DECLARE @TB TABLE([id] INT, [no] INT, [date] DATETIME, [result] VARCHAR(1))
INSERT @TB
SELECT 1, 1, '2009-09-01', 'P' UNION ALL
SELECT 2, 1, '2009-09-02', 'F' UNION ALL
SELECT 3, 1, '2009-09-03', 'P' UNION ALL
SELECT 4, 2, '2009-09-01', 'P' UNION ALL
SELECT 5, 2, '2009-09-02', 'F' UNION ALL
SELECT 6, 2, '2009-09-03', 'F'SELECT 1 as id,[no],count(*) as total_result,sum(case when [result]='P' then 1 else 0 end) as result_p,
sum(case when [result]='F' then 1 else 0 end) as result_f
FROM @TB
GROUP BY [no]
/*
id no total_result result_p result_f
----------- ----------- ------------ ----------- -----------
1 1 3 2 1
1 2 3 1 2
*/
INSERT @TB
SELECT 1, 1, '2009-09-01', 'P' UNION ALL
SELECT 2, 1, '2009-09-02', 'F' UNION ALL
SELECT 3, 1, '2009-09-03', 'P' UNION ALL
SELECT 4, 2, '2009-09-01', 'P' UNION ALL
SELECT 5, 2, '2009-09-02', 'F' UNION ALL
SELECT 6, 2, '2009-09-03', 'F'SELECT 1 as id,[no],count(*) as total_result,sum(case when [result]='P' then 1 else 0 end) as result_p,
sum(case when [result]='F' then 1 else 0 end) as result_f
FROM @TB
GROUP BY [no]
/*
id no total_result result_p result_f
----------- ----------- ------------ ----------- -----------
1 1 3 2 1
1 2 3 1 2
*/
create table [tab]([id] int,[no] int,[date] datetime,[result] varchar(1))
insert [tab]
select 1,1,'2009-09-01','P' union all
select 2,1,'2009-09-02','F' union all
select 3,1,'2009-09-03','P' union all
select 4,2,'2009-09-01','P' union all
select 5,2,'2009-09-02','F' union all
select 6,2,'2009-09-03','F'select * from [tab]declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+[result]+'_result=sum(case when [result]='+quotename([result],'''')+' then 1 else 0 end)'
from tab group by [result]
exec('select no,total_result=count(no)'+@s+' from tab group by no')/*
no total_result F_result P_result
----------- ------------ ----------- -----------
1 3 1 2
2 3 2 1
*/
SUM(case result when 'p' then 1 else 0 end) as result_p,
SUM(case result when 'f' then 1 else 0 end ) as result_f
from tb
group by [no]
DECLARE @TB TABLE([id] INT, [no] INT, [date] DATETIME, [result] VARCHAR(1))
INSERT @TB
SELECT 1, 1, '2009-09-01', 'P' UNION ALL
SELECT 2, 1, '2009-09-02', 'F' UNION ALL
SELECT 3, 1, '2009-09-03', 'P' UNION ALL
SELECT 4, 2, '2009-09-01', 'P' UNION ALL
SELECT 5, 2, '2009-09-02', 'F' UNION ALL
SELECT 6, 2, '2009-09-03', 'F'
select ID=1,no,COUNT(result) as total_result,
SUM(case result when 'p' then 1 else 0 end) as result_p,
SUM(case result when 'f' then 1 else 0 end ) as result_f
from @tb
group by [no]
/*-------------
1 1 3 2 1
1 2 3 1 2
-------------*/
INSERT @TB
SELECT 1, 1, '2009-09-01', 'P' UNION ALL
SELECT 2, 1, '2009-09-02', 'F' UNION ALL
SELECT 3, 1, '2009-09-03', 'P' UNION ALL
SELECT 4, 2, '2009-09-01', 'P' UNION ALL
SELECT 5, 2, '2009-09-02', 'F' UNION ALL
SELECT 6, 2, '2009-09-03', 'F'select 1 id,[no],total_result=[p]+[f],[p] result_p ,[f] result_f from (select [no],result from @TB)m
pivot
(
count(result) for result in ([p],[f])
)p/*
id no total_result result_p result_f
----------- ----------- ------------ ----------- -----------
1 1 3 2 1
1 2 3 1 2(2 行受影响)
*/
,sum(case when result='P' then 1 else 0 end) P
from a
group by no
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-22 09:44:20
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[no] int,[date] datetime,[result] varchar(1))
insert [tb]
select 1,1,'2009-09-01','P' union all
select 2,1,'2009-09-02','F' union all
select 3,1,'2009-09-03','P' union all
select 4,2,'2009-09-01','P' union all
select 5,2,'2009-09-02','F' union all
select 6,2,'2009-09-03','F'
--------------开始查询--------------------------SELECT 1 as id,[no], total_result=count(*),
sum(case when [result]='P' then 1 else 0 end) as result_p,
sum(case when [result]='F' then 1 else 0 end) as result_f
FROM tb
group by [no]
----------------结果----------------------------
/*
id no total_result result_p result_f
----------- ----------- ------------ ----------- -----------
1 1 3 2 1
1 2 3 1 2
*/
declare @tb table(id int,no int,date datetime,result nvarchar(10))
insert into @tb select 1,1,'2009-9-1','p'
union all select 2,1,'2009-9-2','f'
union all select 3,1,'2009-9-3','p'
union all select 4,2,'2009-9-1','p'
union all select 5,2,'2009-9-2','f'
union all select 6,2,'2009-9-3','f'
select no,count(*) total_result,result_p=sum(case when result='p' then 1 end),result_f=sum(case when result='f' then 1 end)
from @tb group by no
(6 行受影响)
no total_result result_p result_f
----------- ------------ ----------- -----------
1 3 2 1
2 3 1 2