select QI_DATE,BUMEN,TUFAN,GC, (select DAND from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC), (select JIZHONG from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC),YE_CD, (select YE_NAME from tab2 where TAB1.YE_CD=TAB2.YE_CD) from tab1
前两个查询还有一个条件,在查出的多条记录中只要QI_DATE最大的那条记录 ----------------------------------------------------------------- 加个MAX()函数就OK了! select DAND,max(QI_DATE) from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC group by dand
(select DAND from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC),
(select JIZHONG from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC),YE_CD,
(select YE_NAME from tab2 where TAB1.YE_CD=TAB2.YE_CD)
from tab1
执行子查询的时候,会提示错误:有多条数据被返回
-----------------------------------------------------------------
加个MAX()函数就OK了!
select DAND,max(QI_DATE) from tab2 where TAB1.BUMEN=TAB2.BUMEN and TAB1.TUFAN = TAB2.TUFAN and TAB1.GC=TAB2.GC group by dand
按照你的写了,还是说取出了多条数据,我只好把一部分数据贴出来,大家看看.谢谢大家的帮忙!!
TAB1;
QI_DATE BUMEN TUFAN GC DAN JIZHO YE_CD YE_NAME
---------- ----- --------------- -- --- ----- ----- ------------
20010912 52 10526021 93 510 SDV 12845 AAAック
20010912 52 10526021 93 510 SDV 12845 AAAック
20010912 52 10526021 93 510 SDV 12845 AAAルテック
20010912 52 10526021 93 510 SDV 12845 AAAルテック
20010912 52 10526021 93 510 SDV 12845 AAAアルテック
20010912 52 11326280 91 510 SDV 25942 BBBヨウケイゴウ
20010912 52 11326280 91 510 SDV 25942 BBBヨウケイゴウ
20010912 52 11326280 91 510 SDV 25942 BBBヨウケイゴウ
20010912 52 11326280 91 510 SDV 25942 BBBヨウケイゴウ
20010912 52 11326280 91 510 SDV 25942 BBBヨウケイゴウ
20010912 52 11336280 91 510 SDV 12971 CCCテック
TAB2:
BU TUFAN GC YE_CD
-- --------------- -- -----
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11336280 91 12971
TAB1;
QI_DATE BUMEN TUFAN GC DAND JIZHONG YE_CD YE_NAME
-------- ----- --------------- ----- ----- ------- ----- ---
20010912 52 10526021 93 510 SDV 12845 AAA
20010912 52 10526021 93 510 SDV 12845 AAA
20010912 52 10526021 93 510 SDV 12845 AAA
20010912 52 10526021 93 510 SDV 12845 AAA
20010912 52 10526021 93 510 SDV 12845 AAA
20010912 52 11326280 91 510 SDV 25942 BBB
20010912 52 11326280 91 510 SDV 25942 BBB
20010912 52 11326280 91 510 SDV 25942 BBB
20010912 52 11326280 91 510 SDV 25942 BBB
20010912 52 11326280 91 510 SDV 25942 BBB
20010912 52 11336280 91 510 SDV 12971 CCC
TAB2:
BUMEN TUFAN GC YE_CD
-- --------------- -- -----
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 10526021 93 29883
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11326280 91 25942
52 11336280 91 12971
我需要的数据最后记录数要跟TAB2表的一样,不多也不少,也就是说最后结果,TAB2表中的数据都要在.
(select distinct ye_cd,ye_name from tab2) t2,
(select distinct bumen,tufan,gc, dand ,jizhong from tab2 ) t3where t1.ye_cd =t2.ye_cd
and T1.BUMEN=T3.BUMEN and
T1.TUFAN = T3.TUFAN and
T1.GC=T3.GC
试试看, 应该可以吧。