sql小技巧

一个sql根据条件查询两个聚合结果

select 
    g.Name,
    COUNT(u.Id) as count,
    SUM(CASE
        WHEN u.State = '1' THEN 1
        ELSE 0
    END) as fCount
from
    table u

mysql 查询Group By后的行数

select 
        Count (u.gId)
    from
        table1 u
    JOIN table2 g ON u.gId = g.Id
    WHERE
        u.sId = 'asd'
    Group By u.gId

这个sql不能返回gId的总行数，会显示Group By后每行的个数  1, 2, 2, 34 

需要在外面再套一个select 来查询总行数

select 
    Count(*)
from
    (select 
        u.gId
    from
        table1 u
    JOIN table2 g ON u.gId = g.Id
    WHERE
        u.sId = 'asd'
    Group By u.gId) as t