select distinct SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME, first_value(TESTD) over (partition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,test_attr order by testd desc) testd from V_TEST where ...or select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME, testd from ( select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,testd, rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME order by testd desc) rank_no from v_test where ... ) where rank_no = 1;
支持 jametong(jametong) :select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME, testd from ( select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,testd, rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME order by testd desc) rank_no from v_test where ... ) where rank_no = 1;
真的不好意思要那么多位朋友等那么久了!因为这几天的确忙晕了头!还有其他好多的事情都试很急的,所以....IT人不好做啊.to:jametong(jametong)请问 rank() over (parition by... 的作用是什么? 能用中文解析一下你的这个语句的意思吗?谢谢!! select SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME, SY_DATE,SY_ATTR from ( select SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME, SY_DATE,SY_ATTR rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME,SY_ATTR order by SY_DATE desc) rank_no from V_GDMIS2 where ... ) where rank_no = 1;
我现在先做了一个视图V_GDMIS2把所有的记录都取出来了,然后再从这个视图取到了最大的日期,但是查出来的结果却不能按日期排序!!!另外一个问题就是:假如前面6个字段的都相同的话,我是希望取第七个字段日期最大的记录,不考虑最后一个字段,但不知道为什么group by 后面一定要出现所有的字段,所以我实现不到想要的功能!!!请各位大侠帮忙!!!!!!我现在马上去提问多一次,加分,哪位在这里解决了我在那边也同样给他100分,感激!!!!!!!!! CREATE OR REPLACE VIEW V_GDMIS ( SB_CODE设备编号, SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点, SB_NAME设备名称, SY_DATE试验日期, SY_ATTR试验性质 ) AS select distinct SB_CODE, SB_TYPE, SY_SPEC, UNITNAME, AZD, SB_NAME, max(SY_DATE) as SY_DATE, SY_ATTR from V_GDMIS2 group by SB_CODE, SB_TYPE, SY_SPEC, UNITNAME, AZD,SB_NAME, SY_ATTR order by SY_DATE desc
?? try: select SB_CODE设备编号, SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点, SB_NAME设备名称, max(SY_DATE试验日期) 试验日期 from V_GDMIS group by SB_CODE设备编号, SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点, SB_NAME设备名称;
CREATE OR REPLACE VIEW V_GDMIS ( SB_CODE设备编号, SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点, SB_NAME设备名称, SY_DATE试验日期, SY_ATTR试验性质 ) AS select distinct SB_CODE, SB_TYPE, SY_SPEC, UNITNAME, AZD, SB_NAME, max(SY_DATE) as SY_DATE, SY_ATTR from V_GDMIS2 group by SB_CODE, SB_TYPE, SY_SPEC, UNITNAME, AZD,SB_NAME, SY_ATTR order by max(SY_DATE) desc
感觉好像不应该这么复杂,试试看这样。 SELECT SB_CODE,SB_TYPE,SY_SPEC,UNITNAME,AZD,SB_NAME,MAX(SY_DATE) AS SY_DATE FROM V_GDMIS GROUP BY SB_CODE,SB_TYPE,SY_SPEC,UNITNAME,AZD,SB_NAME ORDER BY SY_DATE SY_DATE DESC
SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,
first_value(TESTD) over (partition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,test_attr order by testd desc) testd
from V_TEST
where ...or
select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME, testd
from (
select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,testd,
rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME order by testd desc) rank_no
from v_test
where ...
) where rank_no = 1;
from (
select SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME,testd,
rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,DWM, INSTALL_POS,SB_NAME order by testd desc) rank_no
from v_test
where ...
) where rank_no = 1;
能用中文解析一下你的这个语句的意思吗?谢谢!!
select SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME, SY_DATE,SY_ATTR
from (
select SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME, SY_DATE,SY_ATTR
rank() over (parition by SB_CODE, SB_TYPE,SY_SPEC,UNITNAME, AZD,SB_NAME,SY_ATTR order by SY_DATE desc) rank_no
from V_GDMIS2
where ...
) where rank_no = 1;
CREATE OR REPLACE VIEW V_GDMIS ( SB_CODE设备编号,
SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点,
SB_NAME设备名称, SY_DATE试验日期, SY_ATTR试验性质 ) AS select distinct SB_CODE,
SB_TYPE, SY_SPEC, UNITNAME, AZD,
SB_NAME, max(SY_DATE) as SY_DATE, SY_ATTR from V_GDMIS2 group by SB_CODE,
SB_TYPE, SY_SPEC, UNITNAME, AZD,SB_NAME, SY_ATTR order by SY_DATE desc
try:
select SB_CODE设备编号,
SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点,
SB_NAME设备名称, max(SY_DATE试验日期) 试验日期 from V_GDMIS
group by SB_CODE设备编号,
SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点,
SB_NAME设备名称;
SB_TYPE设备类型, SY_SPEC专业, UNITNAME单位名, AZD安装点,
SB_NAME设备名称, SY_DATE试验日期, SY_ATTR试验性质 ) AS select distinct SB_CODE,
SB_TYPE, SY_SPEC, UNITNAME, AZD,
SB_NAME, max(SY_DATE) as SY_DATE, SY_ATTR from V_GDMIS2 group by SB_CODE,
SB_TYPE, SY_SPEC, UNITNAME, AZD,SB_NAME, SY_ATTR order by max(SY_DATE) desc
SELECT
SB_CODE,SB_TYPE,SY_SPEC,UNITNAME,AZD,SB_NAME,MAX(SY_DATE) AS SY_DATE
FROM
V_GDMIS
GROUP BY
SB_CODE,SB_TYPE,SY_SPEC,UNITNAME,AZD,SB_NAME
ORDER BY
SY_DATE SY_DATE DESC