有几种方法可以实现你的目标:
SELECT Teams, Games = COUNT(*) FROM
(
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM MY_TABLE
) AS T
GROUP BY Teams
或者,如果您使用SQL 2005/2008
;WITH T AS (
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM MY_TABLE
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams
以上两种方法将会得到相同的结果。
/*
Teams Games
-------|------
A vs B 3
A vs C 1
B vs C 1
*/
这里有一个你可以玩耍的脚本:
DECLARE @t AS TABLE ( ID INT, Team1 CHAR(1), Team2 CHAR(1), playdate [DATETIME] )
INSERT INTO @t (Team1 , Team2 , playdate)
SELECT 'A' , 'B', '20110525'
UNION ALL SELECT 'B' , 'A', '20110526'
UNION ALL SELECT 'A' , 'C', '20110527'
UNION ALL SELECT 'C' , 'B', '20110528'
UNION ALL SELECT 'A' , 'B', '20110528'
;WITH T AS (
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams
SELECT Teams, Games = COUNT(*) FROM
(
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
) AS T
GROUP BY Teams
/* 或者,使用这个来切换列 */
;WITH T AS (
SELECT
Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END
, Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
)
SELECT Team1 , Team2, Games = COUNT(*) FROM T GROUP BY Team1 , Team2
SELECT Team1 , Team2, Games = COUNT(*) FROM
(
SELECT
Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END
, Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
) AS T
GROUP BY Team1 , Team2