sql - SQL Server:根据 SELECT TOP 100 Column 选择 TOP 10 Column
问题描述
我有 3 张桌子 A、B、C
- A 有 ID 和 Name 列
- B 有 ID 和 PIN 列
- C 有名称和金额列
为了获得前 10 个 PIN 的总数,我使用:
SELECT TOP 10 PIN, SUM(Amount) AS PIN_Amount
FROM A, B, C
WHERE
C.Name IN(A.Name) AND (A.ID = B.ID)
GROUP BY PIN
ORDER BY PIN_Amount DESC
我怎样才能只获得前 10 名 PIN 的前 100 名金额?
这是我的桌子:
CREATE TABLE [dbo].[A](
[ID] [int] NOT NULL,
[Name] [varchar](10) NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[B](
[ID] [int] NULL,
[PIN] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[C](
[Name] [varchar](10) NOT NULL,
[Amount] [int] NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[Name] 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
解决方案
你可以用common_table_expression
它来实现它。
;WITH cte_TempTable AS (
SELECT TOP 10 PIN, SUM(Amount) AS PIN_Amount
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON C.Name = A.Name
GROUP BY PIN
ORDER BY PIN_Amount DESC)
SELECT TOP 100 C.Amount
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON C.Name = A.Name
INNER JOIN cte_TempTable ON cte_TempTable.PIN = B.PIN
ORDER BY C.Amount DESC
推荐阅读
- javascript - 每个带有提要数组的 Javascript JSON
- javascript - Javascript-Firefox 中 ajax 请求 $.getJSON 中的 XML 语法错误
- html - 我无法将文本定位在边框内
- git - 去获取私人 gitlab 存储库
- excel - SSIS - “人A”的excel导入失败,但在服务器上由“人B”调用时工作
- css - 溢出:滚动在我的电子应用程序中不起作用
- oauth - Ubuntu base64 编码字符串以“o=”结尾,而不是明显正确的“==”
- firebase - 颤振命令栏卡在pubspec文件上如何删除它
- c# - 为什么有时我在 NServicebus 队列上的消息会获得无限的 DeliveryCount?
- unit-testing - Quasar jest 测试未找到带有名称标签的元素