SELECT D.NM,ISNULL(T2.ZL,0)ZL,D.GG FROM D LEFT JOIN( SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM (SELECT DM,A_NM NM,ZL FROM A UNION ALL SELECT DM,B_NM,ZL FROM B)T1 JOIN C ON T1.DM=C.DM GROUP BY T1.NM )T2 ON D.NM=T2.NM试下
恩恩 这是表A 这是表B DM A_NM ZL DM B_NM ZL X A 10 X E 5 X B 20 X F 15 Y C 30 X G 8 Y D 12 Y E 12 Z A 15 Y F 16 Z B 16 Z F 25 … … … Z G 30 … … … 这是表D NM GG 这是表C A 50 DM C_NM SL B 50 X 名称1 10 C 50 Y 名称2 20 D 80 Z 名称3 30 E 80 … … … F 100 G 100 H 100 I 80
改成全外连接SELECT ISNULL(D.NM,T2.DM)DM,ISNULL(T2.ZL,0)ZL,ISNULL(D.GG,0)GG FROM D FULL JOIN( SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM (SELECT DM,A_NM NM,ZL FROM A UNION ALL SELECT DM,B_NM,ZL FROM B)T1 JOIN C ON T1.DM=C.DM GROUP BY T1.NM )T2 ON D.NM=T2.NM
大神,这个SQL报错了,报的是“DM无效”
SELECT ISNULL(D.NM,T2.NM)NM,ISNULL(T2.ZL,0)ZL,ISNULL(D.GG,0)GG FROM D FULL JOIN( SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM (SELECT DM,A_NM NM,ZL FROM A UNION ALL SELECT DM,B_NM,ZL FROM B)T1 JOIN C ON T1.DM=C.DM GROUP BY T1.NM )T2 ON D.NM=T2.NM没注意,那就改成NM
SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM
(SELECT DM,A_NM NM,ZL FROM A
UNION ALL SELECT DM,B_NM,ZL FROM B)T1
JOIN C ON T1.DM=C.DM
GROUP BY T1.NM
)T2 ON D.NM=T2.NM试下
大神,这里设置的是结果中的NM是表A/B中的A_NM和B_NM的不同值,而不一定全部是D中的NM
应该怎么改呢?
这是表A 这是表B
DM A_NM ZL DM B_NM ZL
X A 10 X E 5
X B 20 X F 15
Y C 30 X G 8
Y D 12 Y E 12
Z A 15 Y F 16
Z B 16 Z F 25
… … … Z G 30
… … …
这是表D
NM GG 这是表C
A 50 DM C_NM SL
B 50 X 名称1 10
C 50 Y 名称2 20
D 80 Z 名称3 30
E 80 … … …
F 100
G 100
H 100
I 80
SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM
(SELECT DM,A_NM NM,ZL FROM A
UNION ALL SELECT DM,B_NM,ZL FROM B)T1
JOIN C ON T1.DM=C.DM
GROUP BY T1.NM
)T2 ON D.NM=T2.NM
FROM D FULL JOIN(
SELECT T1.NM,SUM(T1.ZL*C.SL)ZL FROM
(SELECT DM,A_NM NM,ZL FROM A
UNION ALL SELECT DM,B_NM,ZL FROM B)T1
JOIN C ON T1.DM=C.DM
GROUP BY T1.NM
)T2 ON D.NM=T2.NM没注意,那就改成NM