被废话，直接上代码

测试代码，数据如下：

CREATE TABLE #T( TIMES VARCHAR(15), RESULT NVARCHAR(20) )INSERT  INTO #T SELECT  '2005-05-09','胜'UNION ALLSELECT '2005-05-09', '胜'UNION ALLSELECT '2005-05-09', '负'UNION ALLSELECT '2005-05-09', '负'UNION ALLSELECT '2005-05-10', '胜'UNION ALLSELECT '2005-05-10', '负'UNION ALLSELECT '2005-05-10', '负'

 

要求结果：

       时间          胜  负

2005-05-09      2  2

2005-05-10      1  2

 方法一：CASE 的妙用

--结果--CASE 里面做文章滴呀；--我艹尼玛；--CASE 用户group by 之后的逻辑判断滴呀；--好逻辑，尼玛case的妙用滴呀；SELECT TIMES, SUM(CASE WHEN RESULT='胜' THEN 1 ELSE 0 END) AS 胜, SUM(CASE WHEN RESULT='负' THEN 1 ELSE 0 END) AS 负FROM #TGROUP BY TIMES

 方法二：共用表达式+COUNT()

GOWITH A AS(SELECT TIMES,COUNT(*) AS WIN FROM #TWHERE RESULT='胜'GROUP BY TIMES), B AS(SELECT TIMES,COUNT(*) AS LOSE FROM #TWHERE RESULT='负'GROUP BY TIMES)SELECT AA.TIMES,AA.WIN,BB.LOSE FROM A AA INNER JOIN B BBON AA.TIMES=BB.TIMES

 方法三：既然能够使用我们的共用表达式，那么自然能使用我们的临时表滴啦

 

 方法四：

  

SELECT A.TIMES,A.WIN,B.LOSE FROM   (   SELECT TIMES,COUNT(*) WIN FROM #T   WHERE RESULT='胜'   GROUP BY TIMES  ) AS A,    (   SELECT TIMES,COUNT(*) LOSE FROM #T   WHERE RESULT='负'   GROUP BY TIMES    ) AS B    WHERE A.TIMES=B.TIMES

 方法五：

   

SELECT N.TIMES,N.WIN,M.LOSE FROM (  (    SELECT TIMES,COUNT(*) AS WIN FROM #T    WHERE RESULT='胜'    GROUP BY TIMES)N     INNER JOIN     (SELECT TIMES ,COUNT(*) AS LOSE FROM #T    WHERE RESULT='负'    GROUP BY TIMES    )M ON N.TIMES=M.TIMES    )    --中间的赛选条件也可以这样写滴呀  --我艹尼玛  SELECT TIMES,COUNT(*) as win  FROM #T  GROUP BY TIMES,RESULT  HAVING RESULT='胜'

 

  帅~