34,591
社区成员
发帖
与我相关
我的任务
分享很复杂的横竖汇总查询，请高手帮忙
表tb如下：
id name LeiBieName
1 AA 1年
2 AA 1年
3 AA 2年
4 BB 1年
5 BB 1年
6 BB 1年
7 BB 2年
8 BB 2年
9 BB 3年
10 CC 1年
11 CC 2年
从此表汇总出下列信息
name 1年 2年 3年 合计
AA 2 1 0 3
BB 3 2 1 6
CC 1 1 0 2
6 4 1 11
id name LeiBieName
1 AA 1年
2 AA 1年
3 AA 2年
4 BB 1年
5 BB 1年
6 BB 1年
7 BB 2年
8 BB 2年
9 BB 3年
10 CC 1年
11 CC 2年
从此表汇总出下列信息
name 1年 2年 3年 合计
AA 2 1 0 3
BB 3 2 1 6
CC 1 1 0 2
6 4 1 11
...全文
请发表友善的回复…
发表回复
shinger126 2018-08-09
- 打赏
- 举报
加上引号试试[/quote]谢谢，加上后可以了；新手还有很多不足，向大家学习[/quote]
正常情况下，列名开头是数字的话是不允许的。加上引号或者方括号就OK
常清静矣 2018-08-09
- 打赏
- 举报
加上引号试试[/quote]谢谢，加上后可以了；新手还有很多不足，向大家学习
RINK_1 2018-08-09
- 打赏
- 举报
加上引号试试
常清静矣 2018-08-09
- 打赏
- 举报
CREATE TABLE TT
(
id INT,
name VARCHAR(10),
LeiBieName VARCHAR(10)
)
INSERT INTO TT VALUES(1 ,'AA', '1年')
INSERT INTO TT VALUES(2 ,'AA', '1年')
INSERT INTO TT VALUES(3 ,'AA', '2年')
INSERT INTO TT VALUES(4 ,'BB', '1年')
INSERT INTO TT VALUES(5 ,'BB', '1年')
INSERT INTO TT VALUES(6 ,'BB', '1年')
INSERT INTO TT VALUES(7 ,'BB', '2年')
INSERT INTO TT VALUES(8 ,'BB', '2年')
INSERT INTO TT VALUES(9 ,'BB', '3年')
INSERT INTO TT VALUES(10, 'CC', '1年')
INSERT INTO TT VALUES(11, 'CC', '2年')
select name,
sum(case
when LeiBieName='1年' then 1 else 0 end)as one年,
sum(case
when LeiBieName='2年' then 1 else 0 end)as two年,
sum(case
when LeiBieName='3年' then 1 else 0 end)as three年,
count(1) as 合计
from TT
group by name
不是我要写one年，不知道为什么写1年会报错，不过大概意思是没错的
xxfvba 2018-08-09
- 打赏
- 举报
CREATE TABLE #T
(
id INT,
name VARCHAR(10),
LeiBieName VARCHAR(10)
)
INSERT INTO #T VALUES(1 ,'AA', '1年')
INSERT INTO #T VALUES(2 ,'AA', '1年')
INSERT INTO #T VALUES(3 ,'AA', '2年')
INSERT INTO #T VALUES(4 ,'BB', '1年')
INSERT INTO #T VALUES(5 ,'BB', '1年')
INSERT INTO #T VALUES(6 ,'BB', '1年')
INSERT INTO #T VALUES(7 ,'BB', '2年')
INSERT INTO #T VALUES(8 ,'BB', '2年')
INSERT INTO #T VALUES(9 ,'BB', '3年')
INSERT INTO #T VALUES(10, 'CC', '1年')
INSERT INTO #T VALUES(11, 'CC', '2年')
select * from #T
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'sum(case when LeiBieName='''+LeiBieName+''' then 1 else 0 end) as ['+LeiBieName+']' from #T group by LeiBieName
set @s='select case when grouping(name)=''0'' then name else ''Total'' end as [name],'+@s+',count(*) as ''Total'' from #T group by name with rollup'
print @s
exec(@s)
听雨停了 2018-08-09
- 打赏
- 举报
use Tempdb
go
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([id] int,[name] nvarchar(22),[LeiBieName] nvarchar(22))
Insert #tab
select 1,N'AA',N'1年' union all
select 2,N'AA',N'1年' union all
select 3,N'AA',N'2年' union all
select 4,N'BB',N'1年' union all
select 5,N'BB',N'1年' union all
select 6,N'BB',N'1年' union all
select 7,N'BB',N'2年' union all
select 8,N'BB',N'2年' union all
select 9,N'BB',N'3年' union all
select 10,N'CC',N'1年' union all
select 11,N'CC',N'2年'
Go
Select * from #tab
--把表数据进行整理并插入Tab表中
SELECT NAME,
leibiename,
COUNT(id) AS cnt
INTO Tab
FROM (
SELECT *
FROM #tab UNION ALL SELECT id,
NAME,
N'合计' AS [LeiBieName]
FROM #tab
) AS T
GROUP BY
GROUPING SETs((NAME, leibiename), (leibiename))
GO
--再进行行转列
DECLARE @sql NVARCHAR(MAX);
SET @sql=N'select name,'+STUFF(
(SELECT DISTINCT ',[' + [LeiBieName]+']' AS [text()] FROM tab FOR XML PATH('')),
1,
1,
'')+' from Tab PIVOT(sum(cnt) FOR [LeiBieName] IN ('
SET @sql = @sql + STUFF(
(SELECT DISTINCT ',[' + [LeiBieName]+']' FROM tab FOR XML PATH('')),
1,
1,
'')+N')) as P order by isnull(name,''Z'');'
--PRINT @sql
EXEC sp_executesql @stmt=@sql;
DROP TABLE Tab;
name 1年 2年 3年 合计
---------------------- ----------- ----------- ----------- -----------
AA 2 1 NULL 3
BB 3 2 1 6
CC 1 1 NULL 2
NULL 6 4 1 11
RINK_1 2018-08-09
- 打赏
- 举报
借用#1的数据
DECLARE @SQL NVARCHAR(1000)
;WITH CTE
AS
(select CASE WHEN GROUPING(NAME)=1 THEN '所有名称' ELSE NAME END AS NAME,
CASE WHEN GROUPING(LEIBIENAME)=1 THEN '合计' ELSE LEIBIENAME END AS LEIBIENAME,
COUNT(*) AS QTY from #T
GROUP BY LeiBieName,NAME
WITH CUBE)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEIBIENAME=''' +LEIBIENAME+''' THEN QTY ELSE 0 END) AS '''+LEIBIENAME+''''
FROM (SELECT LEIBIENAME FROM CTE GROUP BY LEIBIENAME) AS A
SET @SQL='WITH CTE
AS
(select CASE WHEN GROUPING(NAME)=1 THEN ''所有名称'' ELSE NAME END AS NAME,
CASE WHEN GROUPING(LEIBIENAME)=1 THEN ''合计'' ELSE LEIBIENAME END AS LEIBIENAME,
COUNT(*) AS QTY from #T
GROUP BY LeiBieName,NAME
WITH CUBE)
SELECT NAME,'+@SQL+' FROM CTE GROUP BY NAME'
EXEC(@SQL)
雨夹雪 2018-08-09
- 打赏
- 举报
CREATE TABLE #T
(
id INT,
name VARCHAR(10),
LeiBieName VARCHAR(10)
)
INSERT INTO #T VALUES(1 ,'AA', '1年')
INSERT INTO #T VALUES(2 ,'AA', '1年')
INSERT INTO #T VALUES(3 ,'AA', '2年')
INSERT INTO #T VALUES(4 ,'BB', '1年')
INSERT INTO #T VALUES(5 ,'BB', '1年')
INSERT INTO #T VALUES(6 ,'BB', '1年')
INSERT INTO #T VALUES(7 ,'BB', '2年')
INSERT INTO #T VALUES(8 ,'BB', '2年')
INSERT INTO #T VALUES(9 ,'BB', '3年')
INSERT INTO #T VALUES(10, 'CC', '1年')
INSERT INTO #T VALUES(11, 'CC', '2年')
SELECT * FROM
(
SELECT name,LeiBieName,1 AS cnt FROM #T
UNION ALL
SELECT name,'合计',COUNT(1) FROM #T
GROUP BY name
) P
PIVOT
(
sum(cnt)
FOR LeiBieName IN([1年],[2年],[3年],[合计])
)Q
