gpt4 book ai didi

带有列和行总计的 SQL 数据透视表

转载 作者:行者123 更新时间:2023-12-04 22:41:58 25 4
gpt4 key购买 nike

我正在尝试将行和列总计添加到此数据透视表

      create table test4(city nvarchar(10), race nvarchar(30), sex nvarchar(10), age int)
insert into test4 values ('Austin', 'African-American', 'male', 21)
insert into test4 values ('Austin', 'Asian', 'female', 22)
insert into test4 values ('Austin', 'Caucasian', 'male', 23)
insert into test4 values ('Austin', 'Hispanic', 'female', 24)
insert into test4 values ('Austin', 'African-American', 'Unknown', 25)
insert into test4 values ('Austin', 'Asian', 'male', 26)
insert into test4 values ('Austin', 'Caucasian', 'female', 27)
insert into test4 values ('Austin', 'Hispanic', 'Unknown', 28)
insert into test4 values ('Austin', 'Asian', 'male', 29)
insert into test4 values ('Austin', 'Caucasian', 'female', 31)
insert into test4 values ('Dallas', 'Hispanic', 'Unknown', 32)
insert into test4 values ('Dallas', 'African-American', 'male', 33)
insert into test4 values ('Dallas', 'Asian', 'female', 34)
insert into test4 values ('Dallas', 'Caucasian', 'Unknown', 35)
insert into test4 values ('Dallas', 'Hispanic', 'male', 500)
insert into test4 values ('Dallas', 'African-American', 'female', 36)
insert into test4 values ('Dallas', 'Asian', 'Unknown', 37)
insert into test4 values ('Dallas', 'Caucasian', 'male', 38)
insert into test4 values ('Dallas', 'Hispanic', 'female', 39)
insert into test4 values ('Dallas', 'African-American', 'Unknown', 41)
insert into test4 values ('Houston', 'Asian', 'male', 42)
insert into test4 values ('Houston', 'Caucasian', 'female', 43)
insert into test4 values ('Houston', 'Hispanic', 'Unknown', 44)
insert into test4 values ('Houston', 'African-American', 'male', 45)
insert into test4 values ('Houston', 'Asian', 'female', 46)
insert into test4 values ('Houston', 'Caucasian', 'Unknown', 47)
insert into test4 values ('Houston', 'Hispanic', 'male', 48)
insert into test4 values ('Houston', 'African-American', 'female', 49)
insert into test4 values ('Houston', 'Asian', 'Unknown', 51)
insert into test4 values ('Houston', 'Caucasian', 'male', 52);

WITH T AS (
SELECT
A.city as city, A.sex as sex,
CASE
WHEN A.age BETWEEN 20 AND 30 THEN '20-30_' + race
WHEN A.age BETWEEN 31 AND 40 THEN '31-40_' + race
WHEN A.age BETWEEN 41 AND 50 THEN '41-50_' + race
END AS age_range_race
FROM test4 AS A
)
SELECT *
FROM T
PIVOT( COUNT(age_range_race) FOR age_range_race
IN(
[20-30_African-American],
[20-30_Asian],
[20-30_Caucasian],
[20-30_Hispanic],
[31-40_African-American],
[31-40_Asian],
[31-40_Caucasian],
[31-40_Hispanic],
[41-50_African-American],
[41-50_Asian],
[41-50_Caucasian],
[41-50_Hispanic]
)
) AS P
我正在努力达到这个结果
1

我在谷歌上能找到的唯一例子是为 SQL 2000 编写的存储过程。请帮忙!提前致谢

最佳答案

给你试试。

WITH T AS (
SELECT
A.city as city, A.sex as sex,
CASE
WHEN A.age BETWEEN 20 AND 30 THEN '20-30_' + race
WHEN A.age BETWEEN 31 AND 40 THEN '31-40_' + race
WHEN A.age BETWEEN 41 AND 50 THEN '41-50_' + race
END AS age_range_race
FROM #test4 AS A
)


SELECT *, ([20-30_African-American] + [20-30_Asian] + [20-30_Caucasian]+ [20-30_Hispanic]+ [31-40_African-American]+ [31-40_Asian]+ [31-40_Caucasian]+ [31-40_Hispanic]+[41-50_African-American]+ [41-50_Asian]+ [41-50_Caucasian]+ [41-50_Hispanic]) Total
into #tmp_result
FROM T
PIVOT( COUNT(age_range_race) FOR age_range_race
IN(
[20-30_African-American],
[20-30_Asian],
[20-30_Caucasian],
[20-30_Hispanic],
[31-40_African-American],
[31-40_Asian],
[31-40_Caucasian],
[31-40_Hispanic],
[41-50_African-American],
[41-50_Asian],
[41-50_Caucasian],
[41-50_Hispanic]
)
) AS P

select *
from #tmp_result
union all
select 'Grand Total','',SUM([20-30_African-American]), SUM([20-30_Asian]), SUM([20-30_Caucasian]), SUM([20-30_Hispanic]),SUM([31-40_African-American]), SUM([31-40_Asian]), SUM([31-40_Caucasian]), SUM([31-40_Hispanic]),SUM([41-50_African-American]), SUM([41-50_Asian]), SUM([41-50_Caucasian]),SUM([41-50_Hispanic]), sum(Total)
from #tmp_result

关于带有列和行总计的 SQL 数据透视表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9595157/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com