如果在多表关联中查询某表的最大值?该怎么解决
如果在多表关联中查询某表的最大值?
数据如下:
SELECT MA003 AS A,SUBSTRING(TF003,1,6)AS B,SUM(TG027) as C,TG004 AS D
FROM MOCTF as MOCTF
Left JOIN MOCTG as MOCTG On MOCTG.TG001=MOCTF.TF001 and MOCTG.TG002=MOCTF.TF002
Left JOIN INVMB as INVMB On INVMB.MB001=MOCTG.TG004
LEFT JOIN INVMA AS INVMA ON INVMB.MB008=INVMA.MA002
WHERE MA001='4'and SUBSTRING(TF003,1,4)='2014'
GROUP BY D,B,A
A B C D
a 201401 1 1
a 201402 2 1
a 201403 3 1
b 201401 1 2
b 201402 3 2
b 201403 2 2
需要加入条件,取每个D最大的C值。
结果如下:
A B C D
a 201403 3 1
b 201402 3 2
我写的语句是这样的
select A,B,C,D FROM
(SELECT MA003 AS A,SUBSTRING(TF003,1,6)AS B,TG004 as D,SUM(TG027) as C
FROM MOCTF as MOCTF
Left JOIN MOCTG as MOCTG On MOCTG.TG001=MOCTF.TF001 and MOCTG.TG002=MOCTF.TF002
Left JOIN INVMB as INVMB On INVMB.MB001=MOCTG.TG004
LEFT JOIN INVMA AS INVMA ON INVMB.MB008=INVMA.MA002
WHERE MA001='4'and SUBSTRING(TF003,1,4)='2014'
GROUP BY TG004,SUBSTRING(TF003,1,6),MA003
) T
GROUP BY T.D,T.C,T.A,T.B
HAVING C=(select MAX(C) FROM MOCTG)
但是执行时提示返回值不止一个,无法运行。
------解决方案--------------------
数据如下:
SELECT MA003 AS A,SUBSTRING(TF003,1,6)AS B,SUM(TG027) as C,TG004 AS D
FROM MOCTF as MOCTF
Left JOIN MOCTG as MOCTG On MOCTG.TG001=MOCTF.TF001 and MOCTG.TG002=MOCTF.TF002
Left JOIN INVMB as INVMB On INVMB.MB001=MOCTG.TG004
LEFT JOIN INVMA AS INVMA ON INVMB.MB008=INVMA.MA002
WHERE MA001='4'and SUBSTRING(TF003,1,4)='2014'
GROUP BY D,B,A
A B C D
a 201401 1 1
a 201402 2 1
a 201403 3 1
b 201401 1 2
b 201402 3 2
b 201403 2 2
需要加入条件,取每个D最大的C值。
结果如下:
A B C D
a 201403 3 1
b 201402 3 2
我写的语句是这样的
select A,B,C,D FROM
(SELECT MA003 AS A,SUBSTRING(TF003,1,6)AS B,TG004 as D,SUM(TG027) as C
FROM MOCTF as MOCTF
Left JOIN MOCTG as MOCTG On MOCTG.TG001=MOCTF.TF001 and MOCTG.TG002=MOCTF.TF002
Left JOIN INVMB as INVMB On INVMB.MB001=MOCTG.TG004
LEFT JOIN INVMA AS INVMA ON INVMB.MB008=INVMA.MA002
WHERE MA001='4'and SUBSTRING(TF003,1,4)='2014'
GROUP BY TG004,SUBSTRING(TF003,1,6),MA003
) T
GROUP BY T.D,T.C,T.A,T.B
HAVING C=(select MAX(C) FROM MOCTG)
但是执行时提示返回值不止一个,无法运行。
------解决方案--------------------
;with f as
(
SELECT MA003 AS A,SUBSTRING(TF003,1,6)AS B,SUM(TG027) as C,TG004 AS D
FROM MOCTF as MOCTF
Left JOIN MOCTG as MOCTG On MOCTG.TG001=MOCTF.TF001 and MOCTG.TG002=MOCTF.TF002
Left JOIN INVMB as INVMB On INVMB.MB001=MOCTG.TG004
LEFT JOIN INVMA AS INVMA ON INVMB.MB008=INVMA.MA002
WHERE MA001='4'and SUBSTRING(TF003,1,4)='2014'
GROUP BY D,B,A
)
SELECT * FROM F AS T WHERE NOT EXISTS(SELECT 1 FROM F WHERE D=T.D AND C>T.C)