修改 SQL 语句
现有:表A：
M D 数量
T T 100
表B：
M D 名称 数量
T T A 50
T T B 50
T T C 50
T T A 50
T T B 50
T T C 50
表C：
M D 名称 数量
T T A 50
T T B 50
T T C 50
T T A 50
T T B 50
T T C 50
表D：
M D 名称 数量
T T A 100
T T B 100
T T C 100
T T A 100
T T B 100
T T C 100
求:表E
M D 名称 数量A 数量B 数量C 数量D
T T A 100 100 100 200
T T B 100 100 100 200
T T C 100 100 100 200
请问为何本人用的 SQL 语句无法求出正确结果?该如何修改以下语句?有什么好方法写一个好的 SQL 语句?
SELECT A.MS, A.DT, B.名称, A.数量, (SELECT SUM(B.数量) FROM B) AS 数量B, (SELECT SUM(C.数量) FROM C) AS 数量C, (SELECT SUM(D.数量) FROM D) AS 数量D
FROM A, B, C, D
GROUP BY A.MS, A.DT, B.名称, A.数量;
问题点数：10、回复次数：8Top
1 楼CSDNM（决定不当CSDN经理了）回复于 2002-03-22 21:24:43 得分 0
select
a.M,a.D,tb.名称,a.数量 as 数量A,tb.数量 as 数量B,tc.数量 as 数量C,td.数量 as 数量D
from a,
(select m,d,名称,sum(数量) as 数量 from b group m,d,名称) as tb,
(select m,d,名称,sum(数量) as 数量 from c group m,d,名称) as tc,
(select m,d,名称,sum(数量) as 数量 from d group m,d,名称) as td
where a.m=tb.m
and a.d=tb.d
and a.m=tc.m
and a.d=tc.d
and a.m=td.m
and a.d=td.d
and tb.名称=tc.名称
and tb.名称=td.名称
Top
2 楼TT008（T T）回复于 2002-03-22 21:46:03 得分 0
为何会有 GROUP BY 错误
能否写完整一些Top
3 楼TT008（T T）回复于 2002-03-22 22:00:27 得分 0
修改了错误语法后测试,但无法生成查询
是否你的语句有错?
请测试好告诉我好吗?Top
4 楼CSDNM（决定不当CSDN经理了）回复于 2002-03-22 22:00:34 得分 8
写少了by
select
a.M,a.D,tb.名称,a.数量 as 数量A,tb.数量 as 数量B,tc.数量 as 数量C,td.数量 as 数量D
from a,
(select m,d,名称,sum(数量) as 数量 from b group by m,d,名称) as tb,
(select m,d,名称,sum(数量) as 数量 from c group by m,d,名称) as tc,
(select m,d,名称,sum(数量) as 数量 from d group by m,d,名称) as td
where a.m=tb.m
and a.d=tb.d
and a.m=tc.m
and a.d=tc.d
and a.m=td.m
and a.d=td.d
and tb.名称=tc.名称
and tb.名称=td.名称Top
5 楼TT008（T T）回复于 2002-03-22 22:04:29 得分 0
不光是少了 BY 在数量别名引用中也有错,但改过后还是不行!Top
6 楼supsuccess（火气不小）回复于 2002-03-23 08:34:41 得分 2
select t4.m,t4.d,t1.mc,t4.sl as sla,t1.sl as slb,t2.sl as slc,t3.sl as sld from
(select mc,sum(sl) as sl from b group by mc) t1,
(select mc,sum(sl) as sl from c group by mc) t2,
(select mc,sum(sl) as sl from d group by mc) t3,
a t4
where t1.mc=t2.mc and t1.mc=t3.mcTop
7 楼TT008（T T）回复于 2002-03-23 10:25:23 得分 0
数量别名引用中有错,过不了Top
8 楼TT008（T T）回复于 2002-03-23 10:45:05 得分 0
测试过了
THANK!
Top
