表内容:2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负要求得到以下格式的结果: 胜 负
2005-05-09 2 2
2005-05-10 1 2
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负要求得到以下格式的结果: 胜 负
2005-05-09 2 2
2005-05-10 1 2
CREATE TABLE TEST(D DATETIME,RESULT VARCHAR(4))
INSERT TEST
SELECT '2005-05-09','胜' UNION ALL
SELECT '2005-05-09','胜' UNION ALL
SELECT '2005-05-09','负' UNION ALL
SELECT '2005-05-09','负' UNION ALL
SELECT '2005-05-10','胜' UNION ALL
SELECT '2005-05-10','负' UNION ALL
SELECT '2005-05-10','负'
GOSELECT D,
SUM(CASE WHEN RESULT='胜' THEN 1 ELSE 0 END)[胜],
SUM(CASE WHEN RESULT='负' THEN 1 ELSE 0 END)[负]
FROM TEST
GROUP BY DD 胜 负
------------------------------------------------------ ----------- -----------
2005-05-09 00:00:00.000 2 2
2005-05-10 00:00:00.000 1 2(所影响的行数为 2 行)
group by date