sql server查询SQL备忘(1)- group by all

来源:互联网 发布:网络运营总监简历 编辑:程序博客网 时间:2024/06/16 00:58

表tableA数据如下:

tableA

id  pass
1  y
2  n
3  n
3  y
3  y

求pass等于y时id所对应的数量
结果集:
id  total
1    1
2    0

3    2


方法一(每次都要去判断,不太适合数据量大的情况): 

SELECT a.id, ISNULL(b.total,0) total      FROM (SELECT DISTINCT id FROM tableA) a LEFT JOIN (                SELECT id, count(*) total                  FROM tableA                 WHERE pass = 'y'              GROUP BY id           ) b        ON b.id = a.id


方法二(和方法一相比,不用每次去判断了):

SELECT a.id, ISNULL(b.total,0) total      FROM (SELECT DISTINCT id FROM tableA) a LEFT JOIN (                SELECT id, count(*) total                  FROM tableA                 WHERE pass = 'y'              GROUP BY id           ) b        ON b.id = a.id



方法三(很酷,但性能怎样,还有待观察):

Select [id],COUNT(1) AS  total  from tableA WHERE [pass]='y' GROUP BY ALL [id]


0 0
原创粉丝点击