首页 | 新闻 | 交流 | 问吧 | 文档 | 手册 | 下载 | 博客

收藏此问题 发表新评论

这样的case语句有什么问题,请高手帮忙急!!!(已经解决)

select a.acct_id,
(case when acct_month_id='200806' then ys_charge else 0 end)  charge1,
(case when acct_month_id='200806' then yh_charge else 0 end) charge2,
(case when acct_month_id='200813' then ys_charge else 0 end) charge3,
(case when acct_month_id='200813' then yh_charge else 0 end) charge4
from acct_charge a
where a.acct_month_id in('200806','200813')
and acct_id=560000004
group by a.acct_id;
返回结果是:
+-----------+---------+---------+---------+----------+
| acct_id       | charge1  | charge2  | charge3  | charge4  |
+-----------+---------+---------+---------+----------+
| 560000004 | 0       | 0       | 4415.57 | -4752.19 |
+-----------+---------+---------+---------+----------+
1 row in set

select * from  acct_charge a where acct_id='560000004' and  a.acct_month_id in('200806','200813')
返回如下:
+------+-----------+---------------+-----------+-----------+
| id       | acct_id       | acct_month_id | ys_charge | yh_charge |
+------+-----------+---------------+-----------+-----------+
| 3000 | 560000004 | 200813        | 4415.57   | -4752.19  |
| 3004 | 560000004 | 200806        | 1048.7    | -768.79   |
+------+-----------+---------------+-----------+-----------+
2 rows in set

正确的应该是这样:
select a.acct_id,
sum(case when acct_month_id='200806' then ys_charge else 0 end)  charge1,
sum(case when acct_month_id='200806' then yh_charge else 0 end) charge2,
sum(case when acct_month_id='200813' then ys_charge else 0 end) charge3,
sum(case when acct_month_id='200813' then yh_charge else 0 end) charge4
from acct_charge a
where a.acct_month_id in('200806','200813')
and acct_id=560000004
group by a.acct_id;



为什么case中用in语句只能返回最后的一个语句的值

[ 本帖最后由 trjf 于 2008-7-24 13:09 编辑 ]
昵称: trjf  时间: 2008-07-16 09:27:00
因为你写了
group by a.acct_id;
昵称: devotedsky  时间: 2008-07-16 13:37:00
同表的两条记录合并成一条?
select a.acct_id
,a.ys_charge
,a.yh_charge
,b.ys_charge
,b.yh_charge
from acct_charge a,acct_charge b
where a.acct_id = 560000004
and a.acct_id = b.acct_id
and a.acct_month_id = '200806'
and b.acct_month_id = '200813';
前提:对某个id,有200806就必然有200813,否则都没有。

如果200806存在,200813不一定存在,用left join。
from acct_charge a  left join acct_charge b on a.acct_id = b.acct_id
where a.acct_id = 560000004
and a.acct_month_id = '200806'
and (b.acct_month_id = '200813' or b.acct_month_id is null);

如果200806和200813没有前后关系,都可能独自存在,建议LZ做两次查询。
昵称: devotedsky  时间: 2008-07-16 16:21:00