代码之家 › 专栏 › 技术社区 › Neil Knight Rob

SQL-需要按两个不同的列对数据进行分组

sql-server-2008

Neil Knight Rob · 技术社区 · 14 年前

     Count    Team   Score  Team2   Score
-----------------------------------------
        10    TeamA    1    TeamB     2
        7     TeamB    2    TeamA     1

现在,因为我得到了相同的结果,但是团队在不同的列中,我得到了2个结果。我正在寻找一种方法来检索这样一个结果:

     Count    Team   Score  Team2   Score
-----------------------------------------
        17    TeamA    1    TeamB     2

这可能吗?

SELECT TOP 5 SUM([CountryCount]) AS [CountryCount]
      ,[Country], [CustomFieldB], [CustomFieldC], [CustomFieldD]
  FROM (
    SELECT COUNT([Country]) AS [CountryCount], [CustomFieldB], [CustomFieldC], [CustomFieldD]
          ,[Country]
      FROM (
        SELECT [CustomFieldA] AS [Country], [CustomFieldB], [CustomFieldC], [CustomFieldD]
          FROM [Target]
         WHERE [TargetListID] = xxx
        ) as tbl
     GROUP
        BY [Country], [CustomFieldB], [CustomFieldC], [CustomFieldD]
    ) as T
 GROUP
    BY [Country], [CustomFieldB], [CustomFieldC], [CustomFieldD]
 ORDER
    BY [CountryCount] DESC;

1 回复 | 直到 14 年前

Schmalls 14 年前

下面的代码使用公共表表达式和 UNION ALL . 数据是使用提供的查询和结果生成的。

DECLARE @Target TABLE (
    [TargetListID] int,
    [CustomFieldA] varchar(5),
    [CustomFieldB] int,
    [CustomFieldC] varchar(5),
    [CustomFieldD] int
    ) 

INSERT INTO @Target
    VALUES
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamA', 1, 'TeamB', 2),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1),
        (1, 'TeamB', 2, 'TeamA', 1)

;WITH CTE AS (
    SELECT
            COUNT([CustomFieldA]) AS [Count],
            [CustomFieldA] AS [Team1],
            [CustomFieldB] AS [Team1Score],
            [CustomFieldC] AS [Team2],
            [CustomFieldD] AS [Team2Score]
        FROM (
            SELECT
                    [CustomFieldA],
                    [CustomFieldB],
                    [CustomFieldC],
                    [CustomFieldD]
                FROM @Target
                WHERE [TargetListID] = 1
            ) AS tbl
        GROUP BY
            [CustomFieldA],
            [CustomFieldB],
            [CustomFieldC],
            [CustomFieldD]
    )
SELECT
        SUM([Count]) AS [Count],
        Team1 AS Team,
        Team1Score AS Score,
        Team2 AS Team2,
        Team2Score AS Score
    FROM (
        SELECT
                CTE.[Count],
                CTE.[Team1],
                CTE.[Team1Score],
                CTE.[Team2],
                CTE.[Team2Score]
            FROM CTE
            WHERE CTE.[Team1Score] <= CTE.[Team2Score]
        UNION ALL
        SELECT
                CTE.[Count],
                CTE.[Team2],
                CTE.[Team2Score],
                CTE.[Team1],
                CTE.[Team1Score]
            FROM CTE
            WHERE CTE.[Team2Score] < CTE.[Team1Score]
        ) AS U
    GROUP BY
        [Team1],
        [Team1Score],
        [Team2],
        [Team2Score]
    ORDER BY [Count] DESC

Count       Team  Score       Team2 Score
----------- ----- ----------- ----- -----------
17          TeamA 1           TeamB 2