下面是在其中創建和插入值的表：
CREATE TABLE Employees
(
Id INTEGER IDENTITY(1,1),
Name VARCHAR(50),
Gender VARCHAR(50),
Salary INTEGER,
Country VARCHAR(50)
)
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Mark', 'Male', 5000, 'USA')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('John', 'Male', 4500, 'India')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Pam', 'Female', 5500, 'USA')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Sara', 'Female', 4000, 'India')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Todd', 'Male', 3500, 'India')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Mary', 'Female', 5000, 'UK')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Ben', 'Male', 6500, 'UK')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Elizabeth', 'Female', 7000, 'USA')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Tom', 'Male', 5500, 'UK')
INSERT INTO Employees (Name, Gender, Salary, Country)
VALUES ('Ron', 'Male', 5000, 'USA')
SELECT * FROM Employees
現在我運行了以下查詢：
SELECT
COALESCE(Country, '') AS [Country],
COALESCE(Gender, 'Total') AS [Gender],
SUM(Salary) AS [Total Salary]
FROM
Employees
GROUP BY
ROLLUP(Country, Gender)
查看查詢結果時，Gender 列的最后一行包含值“Total”。
我想僅在 Gender 列的最后一行將“Total”替換為“Grand Total”，同時在 Gender 列的其他行中保留“Total”文本。
有沒有可能實作這一目標？
如果是這樣，那么實作它的最簡單方法是什么？
uj5u.com熱心網友回復：
您可以使用GROUPING_ID()：
SELECT
COALESCE(Country,'') AS [Country],
CASE WHEN GROUPING_ID(Country)=1 THEN 'Grand Total' ELSE COALESCE(Gender,'Total') END as [Gender],
SUM(Salary) AS [Total Salary]
FROM Employees
GROUP BY ROLLUP(Country,Gender)
DBFIDDLE
編輯：在問題的評論中指出，應指定結果的順序，以確保它是正確的。
可以像這樣訂購此查詢，以確保總數低于詳細資訊。
SELECT
COALESCE(Country,'') AS [Country],
CASE WHEN GROUPING_ID(Country)=1 THEN 'Grand Total' ELSE COALESCE(Gender,'Total') END as [Gender],
SUM(Salary) AS [Total Salary],
GROUPING_ID(Country),
GROUPING_ID(Gender)
FROM Employees
GROUP BY ROLLUP(Country,Gender)
ORDER BY COALESCE(Country,'ZZZ'),GROUPING_ID(Country),
Gender,GROUPING_ID(Gender)
uj5u.com熱心網友回復：
另一種簡單的方法是使用isnull連接國家名稱，這在 Sql server 中最好只有兩個值，例如：
select
isnull(Country,'') Country,
isnull(Gender, Concat(IsNull(Country, 'Grand'), ' Total')) Gender,
Sum(Salary) [Total Salary]
from Employees
group by rollup(Country,Gender);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451030.html