首页 文章

带有PIVOT（）的RANK（） - 限制输出中的排名数

提问于
浏览 次
0

我在SELECT RANK（）上使用PIVOT函数（Partition by ...）返回一个表“矩阵”，显示我的哪个战争游戏朋友每个军队获胜最多 . 我想限制矩阵只显示每个军队排名前3位的成员（例如，在下图中的“HE”栏中，我想排除突出显示的等级为“4”的记录）

Army Wins by Member

我认为我需要包含一个WHERE或TOP子句但不能确定它的位置 . 我试过看这个网站和谷歌但无法找到答案 . 很抱歉，如果这是学习者的问题，但我仍然是SQL Server的新手 .

以下是两个表[军队]和[战斗]（电子表格格式）： - https://docs.google.com/spreadsheet/ccc?key=0Ana40VqkvVtRdDAwc1BRWnhsWEdaaTQzcFprQmlyeVE

这是我的代码： -

SELECT *
FROM
    (
    SELECT
        RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
         COUNT(WDL) as Wins
    FROM
        [dbo].[Battles]

    INNER JOIN Armies on Army1 = Armies.ArmyNum

    Where
        WDL=2 and Home=1 -- represents a "Win"
    Group By
        Member, ArmyMnemonic, Army, WDL
    ) as rnk

PIVOT (sum(rnk.ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt

ORDER BY Wins Desc;

谢谢你提供的所有帮助 .

CREATE TABLE脚本： -

USE [WFBattlesDB]
GO

/****** Object:  Table [dbo].[Armies]    Script Date: 11/09/2012 13:24:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Armies](
[ArmyNum] [int] NOT NULL,
[ArmyMnemonic] [nvarchar](3) NOT NULL,
[Army] [char](30) NOT NULL,
[Official] [bit] NULL,
[Active] [bit] NULL,
 CONSTRAINT [PK_Armies] PRIMARY KEY CLUSTERED

（[ArmyNum] ASC）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]）ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [WFBattlesDB]
GO

/****** Object:  Table [dbo].[Battles]    Script Date: 11/09/2012 13:25:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Battles](
[keyBattle] [int] IDENTITY(1,1) NOT NULL,
[subDate] [datetime] NOT NULL,
[Member] [nvarchar](20) NOT NULL,
[Home] [bit] NOT NULL,
[Army1] [int] NOT NULL,
[Army2] [int] NOT NULL,
[WDL] [int] NOT NULL,
[PtsVal] [int] NULL,
[MVU] [nvarchar](30) NULL,
[Fun] [int] NULL,
[Luck] [int] NULL,
[Notes] [nvarchar](1500) NULL,
[Link] [nvarchar](255) NULL

）[主要]

GO
sql-server pivot rank

3 回答

  • 0

    您不能在 WHERE 子句中使用 ranks ，因为它在 PIVOT 函数中使用 . 因此，您可以创建第二个 ranks 列，以进行过滤 . 我在内部选择中创建了第二列：

    RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as rankFilter

    然后，您可以在 WHERE 子句中使用它：

    SELECT Army, Member, Wins, [Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE]
FROM
(
    SELECT
        RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
         COUNT(WDL) as Wins,
       RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as rankFilter
    FROM
        [dbo].[Battles]

    INNER JOIN Armies on Army1 = Armies.ArmyNum

    Where
        WDL=2 and Home=1 -- represents a "Win"
    Group By
        Member, ArmyMnemonic, Army, WDL
    ) as rnk

PIVOT (sum(rnk.ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt
where rankFilter <=3

ORDER BY Wins Desc;

    或者您可以在 PIVOT 之前的子查询中应用 WHERE ：

    SELECT Army, Member, Wins, [Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE]
FROM
(
    select *
    from
    (
        SELECT
            RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
             COUNT(WDL) as Wins
        FROM
            [dbo].[Battles]

        INNER JOIN Armies on Army1 = Armies.ArmyNum

        Where
            WDL=2 and Home=1 -- represents a "Win"
        Group By
            Member, ArmyMnemonic, Army, WDL
    )  rnk
    where rnk.ranks <= 3
) src
PIVOT (sum(ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt

ORDER BY Wins Desc;
    回复于
  • 1

    我添加了额外的subquerying级别，因此可以应用简单的 WHERE 子句

    SELECT *
FROM
    (SELECT * FROM ( --Subquery start
    SELECT
        RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
         COUNT(WDL) as Wins
    FROM
        Battles

    INNER JOIN Armies on Army1 = Armies.ArmyNum

    Where
        WDL=2 and Home=1 -- represents a "Win"
    Group By
        Member, ArmyMnemonic, Army, WDL

    ) t where t.ranks <= 3 --New WHERE clause

    ) as rnk
PIVOT (sum(rnk.ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt
ORDER BY Wins Desc;
    回复于
  • 1

    将所有内容包装在另一个select语句中：

    SELECT TOP 3 * FROM（

    SELECT *
FROM
    (
    SELECT
        RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
         COUNT(WDL) as Wins
    FROM
        [dbo].[Battles]

    INNER JOIN Armies on Army1 = Armies.ArmyNum

    Where
        WDL=2 and Home=1 -- represents a "Win"
    Group By
        Member, ArmyMnemonic, Army, WDL
    ) as rnk

PIVOT (sum(rnk.ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt

    ）数据ORDER BY Wins Desc

    回复于

相关问题