Group by 与With结合运用产生特殊效果

原始数据如下图：

Date	Status
2012-01-01	win
2012-01-01	win
2012-01-01	win
2012-01-01	lose
2012-01-01	lose
2012-01-02	win
2012-01-02	win
2012-01-02	lose

 

查询显示如下：

Date	win	lose
2012-01-01	3	2
2012-01-02	2	1

 

View Code

 1     WITH tbDate AS
 2     (
 3     SELECT DATE FROM tbUsers GROUP BY Date
 4     ),
 5     tbWin AS
 6     (
 7     SELECT Date,COUNT(Statu) AS win FROM tbUsers where Statu ='win' GROUP BY Date
 8     ),
 9     tbLose AS
10     (
11     SELECT Date,COUNT(Statu) AS lose FROM tbUsers WHERE Statu ='lose' GROUP BY Date
12     )
13     SELECT d.DATE,t.win ,L.lose FROM tbWin t ,tbLose l, tbDate d WHERE  d.Date = t.date AND L.date = d.date

 另一种：

View Code

1     With t as
2     (
3        SELECT Date,COUNT(Statu) as win FROM tbUsers where Statu = 'win' GROUP BY DATE
4        ),
5        d as(
6     SELECT Date,COUNT(Statu) as lose FROM tbUsers where Statu = 'lose' GROUP BY DATE
7     )
8     select t.Date,t.win ,d.lose from t ,d where t.Date = d.Date