有张表:TABLE
字段有:NAME(任务名称),STATUS_(VARCHAR2(1),未标记=0 已标记=1)
a1 1
a1 1
a1 0
a2 1
a2 0
a2 1
a2 1
现在想按照任务名称进行统计,能得出如下的结果,SQL该如何写呢?
名称 总记录条数 记录条数(STATUS_为1的记录数)
a1 3 2
a2 4 3
select NAME,count(STATUS_) from TABLE group by NAME这样只能得到两个字段,有没有SQL能一次查出来的?
字段有:NAME(任务名称),STATUS_(VARCHAR2(1),未标记=0 已标记=1)
a1 1
a1 1
a1 0
a2 1
a2 0
a2 1
a2 1
现在想按照任务名称进行统计,能得出如下的结果,SQL该如何写呢?
名称 总记录条数 记录条数(STATUS_为1的记录数)
a1 3 2
a2 4 3
select NAME,count(STATUS_) from TABLE group by NAME这样只能得到两个字段,有没有SQL能一次查出来的?
from test a,(select name,count(name) cnt from test group by name) b
where status_='1' and a.name = b.name
group by name
from table
group by name
select name,count(name),count(case when status='1' then name end)
from table
group by name;
没有实验过,不过这个貌似比较接近了..select name ,count(*) as cnt ,sum(status) as s1
from table
group by name
from test
group by name;
from table
group by name和select name ,count(*) as cnt ,sum(decode(status,1,1,0)) as s1
from table
group by name
都可以!还可以对sum()进行一下空处理,nvl(sum(status))。