表结构如下:
id NAME result
----------- -------------------- ------
1 jim 胜
2 jim 胜
3 jim 负
4 Tom 胜
5 Tom 负
6 Sam 负
7 Sam 负
想得到的结果是:
NAME 胜 负
-------------------- ----------- -----------
jim 2 1
Sam 0 2
Tom 1 1
SQL可以这样来实现:
SELECT t1.name,
(SELECTCOUNT(1)FROM temp t2 WHERE t2.NAME=t1.NAME AND t2.result='胜')AS'胜',
(SELECTCOUNT(1)FROM temp t3 WHERE t3.NAME=t1.NAME AND t3.result='负')AS'负'
FROM
(SELECT NAME FROM temp GROUPBY NAME ) AS t1
name 胜