在GROUP BY中"做文章"（五种中简答方法！）

被废话，直接上代码

测试代码，数据如下：

CREATE TABLE #T(
 TIMES VARCHAR(15),
 RESULT NVARCHAR(20) 
)

INSERT  INTO #T 
SELECT  '2005-05-09','胜'
UNION ALL
SELECT '2005-05-09', '胜'
UNION ALL
SELECT '2005-05-09', '负'
UNION ALL
SELECT '2005-05-09', '负'
UNION ALL
SELECT '2005-05-10', '胜'
UNION ALL
SELECT '2005-05-10', '负'
UNION ALL
SELECT '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 #T
GROUP BY TIMES

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

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

SELECT AA.TIMES,AA.WIN,BB.LOSE FROM A AA INNER JOIN B BB
ON 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='胜'

 

  帅~