这个结果如何用分组SQL实现啊？
ID RY Que State
1 小王 asdf 已解决
2 小张 q23w 已解决
4 小王 asdf 未解决
5 小沈 3sw 已解决
6 小王 2333 未解决
7 小张 vdsea 未解决
.....
我想要以下分组结果：
RY Sum_01 Sum_02 Sum_03
小王 3 2 1
小张 2 1 1
小沈 1 1 0
Sum_01 代表问题总数
Sum_02 代表（已解决）问题数
Sum_03 代表（未解决）问题数
这个分组SQL能实现吗？
[解决办法]
select RY,
count(*) as 'Sum_01 ',
sum(case when State = '已解决 ' then 1 else 0 end) as 'Sum_02 ',
sum(case when State = '未解决 ' then 1 else 0 end) as 'Sum_03 ',
from 表
group by RY
[解决办法]
select ry,
sum(1) as [sum_01],
sum(case when state = '已解决 ' then 1 else 0 end) as [sum_02],
sum(case when state = '未解决 ' then 1 else 0 end) as [sum_03]
from 表
group by ry
[解决办法]
declare @t table(
ID int,
RY varchar(10),
Que varchar(10),
State varchar(10))
insert @t select 1, '小王 ', 'asdf ', '已解决 '
union all select 2, '小张 ', 'q23w ', '已解决 '
union all select 4, '小王 ', 'asdf ', '未解决 '
union all select 5, '小沈 ', '3sw ', '已解决 '
union all select 6, '小王 ', '2333 ', '未解决 '
union all select 7, '小张 ', 'vdsea ', '未解决 '
select * from @t
select RY, count(RY) as Sum_01,
sum(case State when '已解决 ' then 1 else 0 end) as Sum_02,
sum(case State when '未解决 ' then 1 else 0 end) as Sum_03
from @t a
group by RY
/*
RY Sum_01 Sum_02 Sum_03
---------- ----------- ----------- -----------
小沈 1 1 0
小王 3 1 2
小张 2 1 1
（所影响的行数为 3 行）
*/
------------
樓主自己的結果貌似不太對,呵呵
[解决办法]
直接用 create view 建立就行了
如果一定要用视图设计器, 则切换到 sql 视图, 输入视图语句
[解决办法]
...
視圖為什麼不支持Case
Create View v_t
as
select RY, count(RY) as Sum_01,
sum(case State when '已解决 ' then 1 else 0 end) as Sum_02,
sum(case State when '未解决 ' then 1 else 0 end) as Sum_03
from 表名
group by RY
這樣肯定沒問題
[解决办法]
declare @t table(
ID int,
RY varchar(10),
Que varchar(10),
State varchar(10),
Date varchar(10))
insert @t select 1, '小王 ', 'asdf ', '已解决 ', '2007-7-1 '
union all select 2, '小张 ', 'q23w ', '已解决 ', '2007-7-2 '
union all select 4, '小王 ', 'asdf ', '未解决 ', '2007-7-6 '
union all select 5, '小沈 ', '3sw ', '已解决 ', '2007-6-9 '
union all select 6, '小王 ', '2333 ', '未解决 ', '2007-7-3 '
union all select 7, '小张 ', 'vdsea ', '未解决 ', '2007-7-8 '
select a.RY, isnull(b.Sum_01, 0) as Sum_01,
isnull(b.Sum_02, 0) as Sum_02,
isnull(b.Sum_03, 0) as Sum_03 from
(select distinct RY from @t) a
left join
(select RY, count(RY) as Sum_01,
sum(case State when '已解决 ' then 1 else 0 end) as Sum_02,
sum(case State when '未解决 ' then 1 else 0 end) as Sum_03
from @t a
where Date > = '2007-7-1 '
group by RY) b
on a.RY = b.RY
/*
RY Sum_01 Sum_02 Sum_03
---------- ----------- ----------- -----------
小沈 0 0 0
小王 3 1 2
小张 2 1 1
（所影响的行数为 3 行）
*/
