怎样把下面2条语句写成一条.谢谢!!
1.
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE
2.
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
group by ZONE
order by ZONE
1.
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE
2.
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
group by ZONE
order by ZONE
解决方案 »
- 分页返回总页数出错
- 请问如何把N个字符串插入到一个临时表中
- 站内短信:如何记录这条信息哪位用户阅读过,因为有的短信是一次发给多位用户,如何记录才能更高效呢?
- 请教 sql 查询的问题 希望用视图来写出
- 如何无人值守安装 sql server 2005 express
- 如何把查询结果马上保存为EXCEL文件?用替代表行不行?===========救命!
- sqlserver 复制中出现不是有效的路径和文件夹
- 用ADO连接命名实例数据库时连接字符串的问题
- 关于分区
- 今天没有正确卸载mysql,而是直接删除了文件夹导致再次下载后配置时显示找不到指定的文件怎么办
- SQL毫秒转成时间
- SQL EXPRESS 2005
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
UNION ALL
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
group by ZONE
order by ZONE
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
--order by ZONE
union all
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
group by ZONE
order by ZONE
直接
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE
--不就是你想要的
不过好象第一句包含第2句啊.
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
UNION ALL
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
group by ZONE
order by ZONE
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE
and ID in (select ID from ID_tab)
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1,
(SELECT SUM(CASE WHEN flag='2' THEN amount ELSE 0 END) FROM z_tab WHERE zone=aa.zone and bz='0' and date .... and id in(....)),
(SELECT SUM(CASE WHEN flag='2' THEN 1 ELSE 0 END) FROM z_tab WHERE zone=aa.zone and bz='0' and date .... and id in(....))
from z_tab aa
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE
SELECT ZONE,SUM(je_1) AS je_1,SUM(bs_1) AS bs_1,SUM(je_2) AS je_2,SUM(bs_2) AS bs_2
FROM
(
select ZONE,
CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END AS je_1,
CASE WHEN FLAG='2' THEN 1 ELSE 0 END AS bs_1,
0 as je_2,0 as bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'Union ALLselect ZONE,
0 as je_1,0 as bs_1,
CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END AS je_2,
CASE WHEN FLAG='2' THEN 1 ELSE 0 END AS bs_2
from
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
) AS TabA
group by ZONE
order by ZONE
FROM
(
select ZONE,
CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END AS je_1,
CASE WHEN FLAG='2' THEN 1 ELSE 0 END AS bs_1,
0 as je_2,0 as bs_2
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26'Union ALLselect ZONE,
0 as je_1,0 as bs_1,
CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END AS je_2,
CASE WHEN FLAG='2' THEN 1 ELSE 0 END AS bs_2
from z_tab
where bz='0' and DATE between '2009-11-01' and '2009-11-26' and ID in (select ID from ID_tab)
) AS TabA
group by ZONE
order by ZONE
SELECT
ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_1,--当falg='2' 就汇总数量
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_1,
sum(CASE WHEN FLAG='2' AND B.ID IS NOT NULL THEN AMOUNT ELSE 0 END) je_2,
--flag='2'且关联的b表值不为空的时候汇总数量
sum(CASE WHEN FLAG='2' AND B.ID IS NOT NULL THEN 1 ELSE 0 END) bs_2
FROM Z_TAB A LEFT JOIN ID_TAB B --两表关联起来,z_tab为主表
ON A.ID=B.ID--关联条件
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
GROUP BY ZONE
ORDER BY ZONE当然表和表之间关联的时候,需要考虑,是否两个表存在多对多的问题.
也就是说,如果楼主的z_tab表的id的每个id值得不唯一,且id_tab的id值也不唯一
那么我刚才这样写就是有问题的,楼主可以在我写的语句上进行修改.
没搞明白楼主想干什么。说清楚一点。第二个SQL可以这样写。
select ZONE,
sum(CASE WHEN FLAG='2' THEN AMOUNT ELSE 0 END) je_2,
sum(CASE WHEN FLAG='2' THEN 1 ELSE 0 END) bs_2
from z_tab inner join ID_tab on z_tab.ID = ID_tab.ID
where bz='0' and DATE between '2009-11-01' and '2009-11-26'
group by ZONE
order by ZONE