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

 

    WITH tbDate AS
    (
    SELECT DATE FROM tbUsers GROUP BY Date
    ),
    tbWin AS
    (
    SELECT Date,COUNT(Statu) AS win FROM tbUsers where Statu ='win' GROUP BY Date
    ),
    tbLose AS
    (
    SELECT Date,COUNT(Statu) AS lose FROM tbUsers WHERE Statu ='lose' GROUP BY Date
    )
    SELECT d.DATE,t.win ,L.lose FROM tbWin t ,tbLose l, tbDate d WHERE  d.Date = t.date AND L.date = d.date

 另一种：

    With t as
    (
       SELECT Date,COUNT(Statu) as win FROM tbUsers where Statu = 'win' GROUP BY DATE
       ),
       d as(
    SELECT Date,COUNT(Statu) as lose FROM tbUsers where Statu = 'lose' GROUP BY DATE
    )
    select t.Date,t.win ,d.lose from t ,d where t.Date = d.Date