问题最大选择一年,最大月和在一个查询中最大日期

CREATE TABLE TEMP ( C_INVOICE_ID NUMBER(10), DIA NUMBER, MES NUMBER, ANO NUMBER, SOCIO_NEGOCIO NVARCHAR2(60) NOT NULL, PRODUCTO_NOM NVARCHAR2(60) NOT NULL, M_PRODUCT_ID NUMBER(10), CATEGORIA NVARCHAR2(60) NOT NULL, COSTO NUMBER ) INSERT INTO TEMP VALUES(10111,1,2,2010,'1585','ALURON 100MG X 30 TABLETAS',1530,15,1.15); INSERT INTO TEMP VALUES(1015,15,2,2010,'1520','ALURON 100MG X 30 TABLETAS',1530,15,2.15); INSERT INTO TEMP VALUES(5654,5,2,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.2); INSERT INTO TEMP VALUES(15321,4,6,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.8); INSERT INTO TEMP VALUES(13548,8,6,2010,'1585','AMARYL 2MG X 15 TABLETAS',1531,15,4.3); INSERT INTO TEMP VALUES(19456,31,4,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4); INSERT INTO TEMP VALUES(116544,8,8,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.8); INSERT INTO TEMP VALUES(132,2,3,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.1); INSERT INTO TEMP VALUES(168,15,1,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.9); INSERT INTO TEMP VALUES(4898,7,4,2010,'1220','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8); INSERT INTO TEMP VALUES(15132,25,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.4); INSERT INTO TEMP VALUES(1684,18,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.3); INSERT INTO TEMP VALUES(14988,8,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,2.9); INSERT INTO TEMP VALUES(84941,8,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.1); INSERT INTO TEMP VALUES(1155,7,4,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.7); INSERT INTO TEMP VALUES(184,1,1,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9); INSERT INTO TEMP VALUES(48994,8,4,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.7); INSERT INTO TEMP VALUES(1465465,9,5,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8); INSERT INTO TEMP VALUES(16,18,6,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9); INSERT INTO TEMP VALUES(894886,20,4,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9); CREATE TABLE TEMP ( C_INVOICE_ID NUMBER(10), DIA NUMBER, MES NUMBER, ANO NUMBER, SOCIO_NEGOCIO NVARCHAR2(60) NOT NULL, PRODUCTO_NOM NVARCHAR2(60) NOT NULL, M_PRODUCT_ID NUMBER(10), CATEGORIA NVARCHAR2(60) NOT NULL, COSTO NUMBER ) INSERT INTO TEMP VALUES(10111,1,2,2010,'1585','ALURON 100MG X 30 TABLETAS',1530,15,1.15); INSERT INTO TEMP VALUES(1015,15,2,2010,'1520','ALURON 100MG X 30 TABLETAS',1530,15,2.15); INSERT INTO TEMP VALUES(5654,5,2,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.2); INSERT INTO TEMP VALUES(15321,4,6,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.8); INSERT INTO TEMP VALUES(13548,8,6,2010,'1585','AMARYL 2MG X 15 TABLETAS',1531,15,4.3); INSERT INTO TEMP VALUES(19456,31,4,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4); INSERT INTO TEMP VALUES(116544,8,8,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.8); INSERT INTO TEMP VALUES(132,2,3,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.1); INSERT INTO TEMP VALUES(168,15,1,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.9); INSERT INTO TEMP VALUES(4898,7,4,2010,'1220','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8); INSERT INTO TEMP VALUES(15132,25,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.4); INSERT INTO TEMP VALUES(1684,18,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.3); INSERT INTO TEMP VALUES(14988,8,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,2.9); INSERT INTO TEMP VALUES(84941,8,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.1); INSERT INTO TEMP VALUES(1155,7,4,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.7); INSERT INTO TEMP VALUES(184,1,1,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9); INSERT INTO TEMP VALUES(48994,8,4,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.7); INSERT INTO TEMP VALUES(1465465,9,5,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8); INSERT INTO TEMP VALUES(16,18,6,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9); INSERT INTO TEMP VALUES(894886,20,4,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);

我有这个疑问,到目前为止,

SELECT MAX (TEMP.COSTO) COSTO, TEMP.M_PRODUCT_ID, TEMP.ANO FROM TEMP TEMP INNER JOIN (SELECT MAX(ANO) ANO, M_PRODUCT_ID FROM TEMP I GROUP BY M_PRODUCT_ID ) LA ON LA.ANO = TEMP.ANO AND LA.M_PRODUCT_ID = TEMP.M_PRODUCT_ID GROUP BY TEMP.M_PRODUCT_ID, TEMP.ANO;

但我的意思是不是有最高费用,我的本意是按照这个顺序来查询

首先,我需要选择不同的m_product_id然后

每m_product_id我需要

过滤器MAX(ANO),然后过滤MAX(MES),然后过滤MAX(DIA)

我需要的结果集是这样的。

C_INVOICE_ID DIA MES ANO SOCIO PRODUCTO_NO M_PRODUCT_ID CATERGORIA COSTO 1015 15 2 2010 1520 ALURON 100MG X 30 TABLETAS 1530 15 2.15 5654 5 2 2010 1520 AMARYL 2MG X 15 TABLETAS 1531 15 4.2 15132 25 9 2010 1585 AMOXAL 250MG X 75ML SUSPENSION 1534 15 3.4

请我真的apritiated任何帮助,感谢PD:我使用的Oracle 9i中

--------------解决方案-------------

如果我读您的要求的权利,我想出这个:

select *
from (select t.*
,row_number() over(partition by m_product_id
order by ano desc, mes desc, dia desc) as rn
from temp t
)
where rn = 1;

但是当我运行查询,我得到另一个结果比你指定的内容:

1015 15 2 2010 1520 ALURON 100MG X 30 TABLETAS 1530 15 2,15
116544 8 8 2010 1220 AMARYL 2MG X 15 TABLETAS 1531 15 4,8
15132 25 9 2010 1585 AMOXAL 250MG X 75ML SUSPENSION 1534 15 3,4

对于的product_id = 1531,我选了一​​行INVOICE_ID = 116544,因为该行如果我排序的顺序指定先出现:

select c_invoice_id, ano, mes, dia
from temp t
where m_product_id = 1531
order by ano desc, mes desc, dia desc;

C_INVOICE_ID ANO MES DIA
------------ ---------- ---------- ----------
116544 2010 8 8
13548 2010 6 8
15321 2010 6 4
19456 2010 4 31
132 2010 3 2
5654 2010 2 5
168 2010 1 15

难道我missunderstand你的要求?

尝试这个:

SELECT a.*
FROM (
SELECT a.*, RANK() OVER(PARTITION BY m_product_id ORDER BY DIA,MES,ANO DESC) rnk
FROM temp a
) a
WHERE rnk=1

分类:SQL 时间:2015-03-15 人气:2
本文关键词: SQL,甲骨文,最大每N组
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.241 (s). 10 q(s)