下面这个用一条sql语句怎么写

Question

 请大家帮我看下下面这个用一条sql语句怎么写?
初始数据:
   时间        人             结果     
2008-08-20     A              失败                                              
2008-08-20     B              成功                                              
2008-08-20      B              成功                                              
2008-08-20     B              失败                                              
2008-08-20     B              失败                                              
2008-08-21     A              成功                                              
2008-08-21     A              成功                                              
2008-08-21     A              失败                                              
2008-08-21     A              失败                                              
2008-08-21     B              失败                                              
2008-08-21     B              成功                                              
2008-08-21     B              成功   结果:
   时间         人      成功      失败                                          
2008-08-20     A         0         1
2008-08-20     B         2         2
2008-08-21     A         2         2
2008-08-21     B         1         2

Answer 1

DECLARE @TB TABLE(时间 DATETIME,人 VARCHAR(5), 结果 VARCHAR(8))
INSERT INTO @TB SELECT '2008-08-20'    ,'A'              ,'失败'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'失败'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'失败'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'失败'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'失败'                                             
UNION ALL SELECT '2008-08-21'    ,'B'              ,'失败'                                             
UNION ALL SELECT '2008-08-21'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'B'              ,'成功' 
SELECT 时间,人,
成功 =sum(case when 结果='成功' then 1 else 0 end),
失败 =sum(case when 结果='失败' then 1 else 0 end)
FROM @TB 
GROUP BY 时间,人/*
时间                                                     人     成功          失败          
------------------------------------------------------ ----- ----------- ----------- 
2008-08-20 00:00:00.000                                A     0           1
2008-08-21 00:00:00.000                                A     2           2
2008-08-20 00:00:00.000                                B     2           2
2008-08-21 00:00:00.000                                B     2           1（所影响的行数为 4 行）*/

Answer 2

select 时间,人
,成功=SUM(CASE WHEN 结果='成功' THEN 1 ELSE 0 END)
,失败=SUM(CASE WHEN 结果='失败' THEN 1 ELSE 0 END)
from tb
group by 时间,人

Answer 3

select distinct A.时间,A.人
        ,成功 = (select count(1) from tb B where B.时间 = A.时间 and B.人= A.人 and B.结果= '成功')
       ,失败 = (select count(1) from tb B where B.时间 = A.时间 and B.人= A.人 and B.结果= '失败')
from tb

Answer 4

select 时间,人,sum(case when 结果='成功' then 1 else 0 end) 成功 
sum(case when 结果='失败' then 1 else 0 end) 失败  from tb group by 时间,人  order by 时间,人

Answer 5

SELECT 时间,人,
成功 =sum(case 结果 when '成功' then 1 else 0 end),
失败 =sum(case 结果 when '失败' then 1 else 0 end)
FROM 表名
GROUP BY 时间,人

Answer 6

总结学习~~~
DECLARE @TB TABLE(时间 DATETIME,人 VARCHAR(5), 结果 VARCHAR(8))
INSERT INTO @TB SELECT '2008-08-20'    ,'A'              ,'失败'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'失败'                                              
UNION ALL SELECT '2008-08-20'    ,'B'              ,'失败'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'失败'                                              
UNION ALL SELECT '2008-08-21'    ,'A'              ,'失败'                                             
UNION ALL SELECT '2008-08-21'    ,'B'              ,'失败'                                             
UNION ALL SELECT '2008-08-21'    ,'B'              ,'成功'                                              
UNION ALL SELECT '2008-08-21'    ,'B'              ,'成功' 
-->方法一
select 时间,人,成功=sum(case when 结果='成功'then 1 else 0 end)
,失败=sum(case when 结果='失败' then 1 else 0 end)
from @tb
group by 时间,人
-->方法二
select distinct a.时间,a.人,成功=(select count(1) from @tb b where b.时间=a.时间 and b.人=a.人 and 结果='成功')
                  ,失败=(select count(1) from @tb b where b.时间=a.时间 and b.人=a.人 and 结果='失败')
from @tb a

Answer 7

select distinct A.时间,A.人
        ,成功 = (select count(1) from tb B where B.时间 = A.时间 and B.人= A.人 and B.结果= '成功')
       ,失败 = (select count(1) from tb B where B.时间 = A.时间 and B.人= A.人 and B.结果= '失败')
from tb

Answer 8

SELECT 时间,人,
成功 =sum(case 结果 when '成功' then 1 else 0 end),
失败 =sum(case 结果 when '失败' then 1 else 0 end)
FROM 表名
GROUP BY 时间,人呵呵.

Answer 9

--表名  Win
--字段 ORDER_DATE,NAME,PART
SELECT ORDER_DATE,NAME,SUM(NUM1)NUM,SUM(NUM2)NUM2
FROM(SELECT ORDER_DATE,NAME,PART,COUNT(*)NUM1,0 NUM2
FROM WIN
WHERE ISNULL(PART,'')='成功'
GROUP BY ORDER_DATE,NAME,PART
UNION ALL
SELECT ORDER_DATE,NAME,PART,0,COUNT(*)NUM
FROM WIN
WHERE ISNULL(PART,'')='失败'
GROUP BY ORDER_DATE,NAME,PART)A
GROUP BY ORDER_DATE,NAME
ORDER BY ORDER_DATE,NAME