db2 中关于从同一个表中选择同一列的统计问题。
我想从一个表中选取一个字段,选取这个字段有几个条件,按条件同时选出符合条件的数据进行统计,分别显示出来,我想到了一种方法,用case when ,但是怎么用呢?求解答!
追问:我想知道这个具体的怎么写呢?我不太会。
比方说我的表中有好几个字段, 现在我要选择其中两个,两个中的字段,其中一个我要重复选择,
select
case
when POST_OFFICE=1 then DOMAIN_ID,count(POST_OFFICE)
when POST_OFFICE=2 then DOMAIN_ID,count(POST_OFFICE)
when POST_OFFICE=0 then DOMAIN_ID,count(POST_OFFICE)
end
from JG_MEMBER_DEALER
group by DOMAIN_ID
要统计其中的数量。如上。该怎样写呢?POST_OFFICE这个字段有三个状态,要统计出POST_OFFICE三个状态各有多少条,根据DOMAIN_ID分组。类似把POST_OFFICE命名为POST_OFFICE1数量、POST_OFFICE2数量、POST_OFFICE3数量.这样进行显示。搞定了,谢谢。非常感谢!
select DOMAIN_ID,
sum(case
when POST_OFFICE=1 then 1 else 0
end) as 已注册DFIM数,
sum(case
when POST_OFFICE=2 then 1 else 0
end) as 已注册SC数,
sum(case
when POST_OFFICE=0,STATUS= then 1 else 0
end) as 已注册SM数
from JG_MEMBER_DEALER
group by DOMAIN_IDselect DOMAIN_ID
case
when POST_OFFICE=1 then count(POST_OFFICE)
when POST_OFFICE=2 then count(POST_OFFICE)
when POST_OFFICE=0 then count(POST_OFFICE)
end
from JG_MEMBER_DEALER
group by DOMAIN_ID
GO
这样的话,then 后面是通不过的。
答案:case when 1=1 then 1
when 1=2 then 0
else 2
end
其他:语句
select case 字段1
when 条件1 then '内容'
when 条件2 then '内容'
else '' end
from 表1
例子:
SELECT CASE grade
WHEN grade>90 THEN '优'
WHEN grade between 80 and 90 THEN '良'
WHEN grade between 70 and 80 THEN '中'
WHEN grade between 60 and 70 THEN '及格'
ELSE '不及格' end
from course case when 加上统计函数或条件就可以了
when 条件1 then sum(XXX)
上一个:如何启动db2 9.7 的jdbc
下一个:Oracle 和 DB2 那个好用?