我正在考虑如何避免在大型查询中按特定列进行分组,以减少性能问题。
示例:http://sqlfiddle.com/#!18/cb98e/2
CREATE TABLE [OrderTable]
(
[id] INT,
[OrderGroupID] INT,
[Total] INT,
[fkPerson] INT,
[fkitem] INT,
PRIMARY KEY (id)
)
INSERT INTO [OrderTable] (id, OrderGroupID, Total, [fkPerson], [fkItem])
VALUES ('1', '1', '20', '1', '1'),
('2', '1', '45', '2', '2'),
('3', '2', '32', '1', '1'),
('4', '2', '30', '2', '2');
CREATE TABLE [Person]
(
[id] INT,
[Name] VARCHAR(32),
PRIMARY KEY (id)
)
INSERT INTO [Person] (id, Name)
VALUES ('1', 'Fred'),
('2', 'Sam');
CREATE TABLE [Item]
(
[id] INT,
[ItemNo] VARCHAR(32),
[Price] INT,
PRIMARY KEY (id)
)
INSERT INTO [Item] (id, ItemNo, Price)
VALUES ('1', '453', '23'),
('2', '657', '34');
查询:
WITH TABLE1 AS
(
SELECT
P.ID AS [PersonID],
P.Name,
SUM(OT.[Total]) AS [Total],
i.[id] AS [ItemID],
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
ot.fkperson
FROM
OrderTable OT
INNER JOIN
Person P ON P.ID = OT.fkperson
INNER JOIN
Item I ON I.[id] = OT.[fkItem]
GROUP BY
P.ID, P.Name, i.id, ot.fkperson
)
SELECT
*,
Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM
TABLE1
结果:
| PersonID | Name | Total | ItemID | rownum | fkperson | Totalrows |
+----------+------+-------+--------+--------+----------+-----------+
| 1 | Fred | 52 | 1 | 1 | 1 | 2 |
| 2 | Sam | 75 | 2 | 2 | 2 | 2 |
比如说,如果我不想按 varchar 列(人名)进行分组,我可以这样做 - 删除 person 表并稍后再将其连接回来。例如:
WITH TABLE1 AS
(
SELECT
-- P.ID AS [PersonID],
-- P.Name,
SUM(OT.[Total]) AS [Total],
i.[id] AS [ItemID],
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
ot.fkperson
FROM
OrderTable OT
-- INNER JOIN Person P ON P.ID = OT.fkperson
INNER JOIN
Item I ON I.[id] = OT.[fkItem]
GROUP BY
-- P.ID, P.Name,
i.id, ot.fkperson
)
SELECT
p.id as [PersonID],
p.Name,
t1.[total],
t1.[itemid],
t1.[rownum],
t1.fkperson
-- Totalrows = (SELECT MAX(rownum) FROM TABLE1 GROUP BY
-- i.id
-- ,ot.fkperson
-- )
FROM
TABLE1 T1
INNER JOIN
Person P ON P.ID = T1.fkperson
结果:
| PersonID | Name | total | itemid | rownum | fkperson |
+----------+------+-------+--------+--------+----------+
| 1 | Fred | 52 | 1 | 1 | 1 |
| 2 | Sam | 75 | 2 | 2 | 2 |
我的问题是,我也想在我的最后一个查询中包含
MAX(rownum)
列,但是如何做到这一点,而不必再次对所有内容进行分组?最好的方法是什么?我错过了什么非常明显的东西吗? :)
MAX(rownum) OVER (PARTITION BY p.id) AS total_rows
或者MAX(rownum) OVER () AS total_rows
? - MatBailie