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

表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]