[求助] 不同条件查询字段整合为一

beenet

求助，不同的查询条件，多次查询，合并为一，能否将附件的几个查询简化为一个查询语句

beenet

谢谢　andymark　

　

qlm

以下是引用andymark在2006-7-16 10:29:00的发言：


try:

SELECT a.F18 AS 主单号, d.总票数, b.有效票数, b.有效件数, c.子件数量
FROM ((T_PRE AS a LEFT JOIN [SELECT e.F18 AS 主单号, Count(*) AS 有效票数, Sum(e.F4) AS 有效件数
FROM T_PRE e WHERE (((e.F1) Not Like "DT*") AND ((e.F4) Is Not Null)) GROUP BY e.F18]. AS b ON a.F18=b.主单号) LEFT JOIN [SELECT f.F18 AS 主单号, Count(*) AS 子件数量
FROM T_PRE f WHERE (((f.F1) Like "DT*")) GROUP BY f.F18]. AS c ON a.F18=c.主单号) LEFT JOIN [SELECT g.F18 AS 主单号, Count(*) AS 总票数
FROM T_PRE g WHERE (((g.F1) Not Like "DT*")) GROUP BY g.F18]. AS d ON a.F18=d.主单号
GROUP BY a.F18, d.总票数, b.有效票数, b.有效件数, c.子件数量;

good

freedomyhs

yty

freedomyhs

ytyt

bigdinosaur

很好!谢谢分享!

hahaxiaoa

[em09][em09][em09]

受教了。

andymark

try:



SELECT a.F18 AS 主单号, d.总票数, b.有效票数, b.有效件数, c.子件数量
FROM ((T_PRE AS a LEFT JOIN [SELECT e.F18 AS 主单号, Count(*) AS 有效票数, Sum(e.F4) AS 有效件数
FROM T_PRE e WHERE (((e.F1) Not Like "DT*") AND ((e.F4) Is Not Null)) GROUP BY e.F18]. AS b ON a.F18=b.主单号) LEFT JOIN [SELECT f.F18 AS 主单号, Count(*) AS 子件数量
FROM T_PRE f WHERE (((f.F1) Like "DT*")) GROUP BY f.F18]. AS c ON a.F18=c.主单号) LEFT JOIN [SELECT g.F18 AS 主单号, Count(*) AS 总票数
FROM T_PRE g WHERE (((g.F1) Not Like "DT*")) GROUP BY g.F18]. AS d ON a.F18=d.主单号
GROUP BY a.F18, d.总票数, b.有效票数, b.有效件数, c.子件数量;