如何在两列中查询计数

Question

我正在尝试编写一条SQL查询语句，显示两个团队相互对战的频率。

Id | Team1 | Team2 | Date 
1  |   A   |   B   | 25/5/11
2  |   B   |   A   | 26/5/11
3  |   A   |   C   | 27/5/11
4  |   C   |   B   | 28/5/11
5  |   A   |   B   | 28/5/11

翻译的结果应该是:

A vs B => 3
A vs C => 1
C vs B => 1 

将A-B和B-A视为不同的查询是很容易的。但我无法让它们被一起计算。

有什么建议吗？

- Sorskoot

---

团队名称可以是任何东西。我使用A和B来更快地编写它。 - Sorskoot

每个团队是否有一个代表数字？例如从团队表中获取？ - galchen

你使用的是哪个版本的SQL？ - leoinfo

在这种情况下，我正在使用MySql作为我的实时数据库... - Sorskoot

Answer 1

我已使用子查询在分组之前重新排序团队。

SELECT first_team, second_team, count(*)
FROM (
    SELECT 
        CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END AS first_team,
        CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END AS second_team
    FROM table
) a
GROUP BY first_team, second_team;

- a'r

---

这个方法可以运行，但它根本没有使用索引，所以如果你有很多团队，请使用我的答案。 - Ariel

Answer 2

SELECT Team1, Team2, SUM(num) FROM (
   SELECT Team1, Team2, COUNT(*) num
   FROM table_name
   GROUP BY Team1, Team2
  UNION ALL
   SELECT Team2, Team1, COUNT(*) num
   FROM table_name
   GROUP BY Team2, Team1
) combined
WHERE Team1 < Team2
GROUP BY Team1, Team2

编辑：在必要时更新反转团队。

注意：与其他答案中使用CASE的版本相比，此版本运行速度要快得多，因为它将充分利用索引。

编辑2：移动where语句以更快地使用索引。

- Ariel

---

那样不会得到正确的结果。在该查询中，“A->B”和“B->A”之间有区别。OP正在寻求将A与B相对应并与B与A相对应进行组合。 - user596075

这个表可能会变得非常大...因此可能需要使用索引...谢谢。 - Sorskoot

@Sorskoot 在Team1、Team2上放置两个索引，并在Team2、Team1上放置第二个索引。 - Ariel

我使用索引对其进行了编辑，使其更快。实际上，“where”是可选的 - 如果您将其省略，您将会得到每个分组两次 - 这可能很有帮助，因为您只需要搜索特定团队的“Team1”列，而不是检查两个列以查找该团队。 - Ariel

@Ariel - 我认为你给服务器增加了一些不必要的负担。看看我的帖子，检查标记为“Style 4”的部分。根据执行计划，这应该快大约60%。 - leoinfo

@leoinfo，这只是因为你的数据量太少了才更快。你强制数据库实际上对每一行进行排序、分组和计数。而我的方法只需要在索引中查找，直接获取索引中相同叶子节点的数量，它不需要实际计算它们的数量，也不需要排序或分组——只需从索引中提取即可。 - Ariel

Answer 3

SELECT 
  (CASE WHEN Team1<Team2 THEN Team1 ELSE Team2) Team1,
  (CASE WHEN Team1>Team2 THEN Team1 ELSE Team2) Team2,
  COUNT(*)  cnt
FROM <table> 
GROUP BY 
  (CASE WHEN Team1<Team2 THEN Team1 ELSE Team2) Team1,
  (CASE WHEN Team1>Team2 THEN Team1 ELSE Team2) Team2

- phil

Answer 4

有几种方法可以实现你的目标：

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
*/

这里有一个你可以玩耍的脚本：

/* TEST DATA */
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'    


/* STYLE 1 */    
;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

/* STYLE 2 */
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

/* 或者，使用这个来切换列 */

/* STYLE 3 */    
;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

/* STYLE 4 */
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

- leoinfo