您所在位置：主题：SQL语句求助
wkl1973
[专家分：400] 发布于 2008-10-29 10:27:00
各位前辈请指教;
我有四条语句想组合成一个查询语句来实现一个表来显示出来.这是我在查询分析器中写下的语句.已经试过我所会的各种方法.只有一种方法可以解决但是不好.因此请前辈给于指点.
是否还更好的优化形式
select UKeepName,COUNT(UID) as 总数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' GROUP BY UKeepName
select UKeepName,COUNT(UID) as 自用数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' and UKeepName='甲' and UGoodsName='宝山矿' and UFa_Dao='发运' GROUP BY UKeepName
select UKeepName,COUNT(UID) as 共用数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用' GROUP BY UKeepName
select COUNT(UID) as 到达数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达' and USe_from='共用'or USe_from='自用' and UGoodsName='宝山矿'
表的最终形式为; 交接总数并不是等于自用总数+共用总数+到达总数.因为交接总数中包含了非本矿的装车数.
序号 储运员 交接总数 重量 自用总数 重量 共用总数 重量 到达总数 重量
1 甲 10 600 3 180 5 300 2 120
2 乙 20 1200 4 240 3 180 2 120
这是我自已给出的解决方法.
--储运员分类汇总查询。
select UKeepName,COUNT(UID) as 总数,SUM(UHeavy) as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UGoodsName='宝山矿'and UFa_Dao='发运')AS 自用数,(select SUM(UHeavy)from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UGoodsName='宝山矿' and UFa_Dao='发运') as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用') as 共用数,(select SUM(UHeavy) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用') as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达') as 到达数,(select SUM(UHeavy) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达' ) as 重量 from tbTotalwatch where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' GROUP BY UKeepName
我有四条语句想组合成一个查询语句来实现一个表来显示出来.这是我在查询分析器中写下的语句.已经试过我所会的各种方法.只有一种方法可以解决但是不好.因此请前辈给于指点.
是否还更好的优化形式
select UKeepName,COUNT(UID) as 总数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' GROUP BY UKeepName
select UKeepName,COUNT(UID) as 自用数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' and UKeepName='甲' and UGoodsName='宝山矿' and UFa_Dao='发运' GROUP BY UKeepName
select UKeepName,COUNT(UID) as 共用数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-9-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用' GROUP BY UKeepName
select COUNT(UID) as 到达数,SUM(UHeavy) as 重量 from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达' and USe_from='共用'or USe_from='自用' and UGoodsName='宝山矿'
表的最终形式为; 交接总数并不是等于自用总数+共用总数+到达总数.因为交接总数中包含了非本矿的装车数.
序号 储运员 交接总数 重量 自用总数 重量 共用总数 重量 到达总数 重量
1 甲 10 600 3 180 5 300 2 120
2 乙 20 1200 4 240 3 180 2 120
这是我自已给出的解决方法.
--储运员分类汇总查询。
select UKeepName,COUNT(UID) as 总数,SUM(UHeavy) as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UGoodsName='宝山矿'and UFa_Dao='发运')AS 自用数,(select SUM(UHeavy)from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UGoodsName='宝山矿' and UFa_Dao='发运') as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用') as 共用数,(select SUM(UHeavy) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='发运' and USe_from='共用') as 重量,(select COUNT(UID) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达') as 到达数,(select SUM(UHeavy) from tbTotalwatch
where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' and UFa_Dao='到达' ) as 重量 from tbTotalwatch where UDate between '2008-7-1' and '2008-7-30' and UKeepName='甲' GROUP BY UKeepName