27,580
社区成员
发帖
与我相关
我的任务
分享分类查询汇总的问题
请教下面两种表结构，做分类汇总查询的语句应该怎样写，谢谢。
第一种情况
一、表a
id name class
1 甲 语文
2 甲 数学
3 甲 英语
4 甲 科学
5 乙 语文
6 乙 英语
7 丙 数学
8 丙 语文
9 丙 科学
二、查询结果说明：
1、按name分类汇总
2、num：相同name 数据条数的汇总
5、class：class字段的集合
三、结果：
id name num class
1 甲 4 语文，数学，英语，科学
2 乙 2 语文，英语
3 丙 3 语文，科学
第二种情况
表a：
id name
1 甲
2 乙
3 丙
表b：
id a_id class
1 1 语文
2 1 数学
3 1 英语
4 1 科学
5 2 语文
6 2 英语
7 3 数学
8 3 语文
9 3 科学
查询结果说明：
1、表a的id和表b的a_id做关联查询；
2、id ：表a id
3、name ：表a name
4、num ：表b 对应表a 数据条数的汇总
5、class：表b 对应表a 的科目集合
结果：
id name num class
1 甲 4 语文，数学，英语，科学
2 乙 2 语文，英语
3 丙 3 语文，科学
...全文
请发表友善的回复…
发表回复
吉普赛的歌 2018-11-13
- 打赏
- 举报
帮他改一下：
IF OBJECT_ID('dbo.forxmlpath') IS NOT NULL
DROP FUNCTION dbo.forxmlpath
GO
CREATE FUNCTION dbo.forxmlpath
(
@name NVARCHAR(20)
)
RETURNS NVARCHAR(200)
AS
BEGIN
declare @s varchar(8000);
set @s = '';
select @s = @s+ ','+class --这里改一下
from a WHERE [name] = @name
set @s = stuff(@s,1,1,'')
--SELECT @s
RETURN @s;
END
GO
--测试数据
if not object_id(N'a') is null
drop table a
Go
Create table a([id] int,[name] nvarchar(21),[class] nvarchar(22))
Insert a
select 1,N'甲',N'语文' union all
select 2,N'甲',N'数学' union all
select 3,N'甲',N'英语' union all
select 4,N'甲',N'科学' union all
select 5,N'乙',N'语文' union all
select 6,N'乙',N'英语' union all
select 7,N'丙',N'数学' union all
select 8,N'丙',N'语文' union all
select 9,N'丙',N'科学'
Go
--测试数据结束
SELECT ROW_NUMBER()OVER(ORDER BY mid) id,name,num,t.class FROM (
SELECT [name] ,
MIN(id) AS mid,
COUNT(1) AS num,
MAX(dbo.forxmlpath(name) ) AS class
FROM a
GROUP BY a.name )t
二月十六 2018-11-13
- 打赏
- 举报
谢谢版主回复， 定义函数报错： 消息 170：第 12 行: '=' 附近有语法错误。[/quote]
好吧，不清楚你那里怎么回事了，我这测试着都没问题了，你自己调试改改吧……
另外能升级升一下吧，我都没用过2000，最低版本是从2005开始的……
吉普赛的歌 2018-11-13
- 打赏
- 举报
建议你升级 SQL Server ， 如果服务器是 Win 2003 ， 最高可以安装 SQL Server 2008 。
Win 2008 的话， 建议安装 SQL Server 2014 。
一个过去了 18 年的老版本， 要写点代码、维护一下是多么的心酸……
Derekwong 2018-11-13
- 打赏
- 举报
谢谢版主回复， 定义函数报错： 消息 170：第 12 行: '=' 附近有语法错误。
二月十六 2018-11-13
- 打赏
- 举报
2、
CREATE FUNCTION dbo.forxmlpath_b
(
@id INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
declare @s varchar(8000);
set @s = '';
select @s += ','+class
from b WHERE a_id = @id
set @s = stuff(@s,1,1,'')
--SELECT @s
RETURN @s;
END
GO
--测试数据
if not object_id(N'a') is null
drop table a
Go
Create table a([id] int,[name] nvarchar(21))
Insert a
select 1,N'甲' union all
select 2,N'乙' union all
select 3,N'丙'
GO
if not object_id(N'b') is null
drop table b
Go
Create table b([id] int,[a_id] int,[class] nvarchar(22))
Insert b
select 1,1,N'语文' union all
select 2,1,N'数学' union all
select 3,1,N'英语' union all
select 4,1,N'科学' union all
select 5,2,N'语文' union all
select 6,2,N'英语' union all
select 7,3,N'数学' union all
select 8,3,N'语文' union all
select 9,3,N'科学'
Go
--测试数据结束
SELECT id,name ,
(SELECT COUNT(1) FROM b WHERE a_id = a.id) AS num,
MAX(dbo.forxmlpath_b(id)) AS [class]
FROM a
GROUP BY a.id,a.name 二月十六 2018-11-13
- 打赏
- 举报
创建一个方法：
CREATE FUNCTION dbo.forxmlpath
(
@name NVARCHAR(20)
)
RETURNS NVARCHAR(200)
AS
BEGIN
declare @s varchar(8000);
set @s = '';
select @s += ','+class
from a WHERE name = @name
set @s = stuff(@s,1,1,'')
--SELECT @s
RETURN @s;
END
GO
--测试数据
if not object_id(N'a') is null
drop table a
Go
Create table a([id] int,[name] nvarchar(21),[class] nvarchar(22))
Insert a
select 1,N'甲',N'语文' union all
select 2,N'甲',N'数学' union all
select 3,N'甲',N'英语' union all
select 4,N'甲',N'科学' union all
select 5,N'乙',N'语文' union all
select 6,N'乙',N'英语' union all
select 7,N'丙',N'数学' union all
select 8,N'丙',N'语文' union all
select 9,N'丙',N'科学'
Go
--测试数据结束
SELECT ROW_NUMBER()OVER(ORDER BY mid) id,name,num,t.class FROM (
SELECT [name] ,
MIN(id) AS mid,
COUNT(1) AS num,
MAX(dbo.forxmlpath(name) ) AS class
FROM a
GROUP BY a.name )tDerekwong 2018-11-13
- 打赏
- 举报
不好意思，忘了说是用在SQL2000上的，测试时有点错误。
Derekwong 2018-11-13
- 打赏
- 举报
谢谢回复，忘了说是用在sql2000上的，测试出错。
吉普赛的歌 2018-11-13
- 打赏
- 举报
第二种情况：
USE tempdb
GO
IF OBJECT_ID('dbo.[tableA]') IS NOT NULL
DROP TABLE dbo.[tableA]
GO
CREATE TABLE dbo.[tableA](
[id] NVARCHAR(MAX)
,[name] NVARCHAR(MAX)
)
GO
IF OBJECT_ID('dbo.[tableB]') IS NOT NULL
DROP TABLE dbo.[tableB]
GO
CREATE TABLE dbo.[tableB](
[id] NVARCHAR(MAX)
,[a_id] NVARCHAR(MAX)
,[class] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[tableA] VALUES(N'1',N'甲')
INSERT INTO dbo.[tableA] VALUES(N'2',N'乙')
INSERT INTO dbo.[tableA] VALUES(N'3',N'丙')
--
INSERT INTO dbo.[tableB] VALUES(N'1',N'1',N'语文')
INSERT INTO dbo.[tableB] VALUES(N'2',N'1',N'数学')
INSERT INTO dbo.[tableB] VALUES(N'3',N'1',N'英语')
INSERT INTO dbo.[tableB] VALUES(N'4',N'1',N'科学')
INSERT INTO dbo.[tableB] VALUES(N'5',N'2',N'语文')
INSERT INTO dbo.[tableB] VALUES(N'6',N'2',N'英语')
INSERT INTO dbo.[tableB] VALUES(N'7',N'3',N'数学')
INSERT INTO dbo.[tableB] VALUES(N'8',N'3',N'语文')
INSERT INTO dbo.[tableB] VALUES(N'9',N'3',N'科学')
SELECT a.id
,a.name
,COUNT(a.id) AS num
,STUFF((SELECT ','+ bb.class FROM tableB AS bb WHERE a.id=bb.a_id FOR XML PATH('')),1,1,'') AS [class]
FROM dbo.tableA AS a INNER JOIN dbo.tableB AS b ON a.id=b.a_id
GROUP BY a.id,a.[name]
吉普赛的歌 2018-11-13
- 打赏
- 举报
--情况一
USE tempdb
GO
IF OBJECT_ID('dbo.[tableA]') IS NOT NULL
DROP TABLE dbo.[tableA]
GO
CREATE TABLE dbo.[tableA](
[id] NVARCHAR(MAX)
,[name] NVARCHAR(MAX)
,[class] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[tableA] VALUES(N'1',N'甲',N'语文')
INSERT INTO dbo.[tableA] VALUES(N'2',N'甲',N'数学')
INSERT INTO dbo.[tableA] VALUES(N'3',N'甲',N'英语')
INSERT INTO dbo.[tableA] VALUES(N'4',N'甲',N'科学')
INSERT INTO dbo.[tableA] VALUES(N'5',N'乙',N'语文')
INSERT INTO dbo.[tableA] VALUES(N'6',N'乙',N'英语')
INSERT INTO dbo.[tableA] VALUES(N'7',N'丙',N'数学')
INSERT INTO dbo.[tableA] VALUES(N'8',N'丙',N'语文')
INSERT INTO dbo.[tableA] VALUES(N'9',N'丙',N'科学')
GO
SELECT
[name]
,COUNT([name]) AS cnt
,STUFF((SELECT ','+b.class FROM tableA AS b
WHERE a.[name]=b.[name] FOR XML PATH('') ),1,1,''
) AS [class]
FROM tableA AS a
GROUP BY [name]
/*
name cnt class
丙 3 数学,语文,科学
甲 4 语文,数学,英语,科学
乙 2 语文,英语
*/
二月十六 2018-11-13
- 打赏
- 举报
1、
2、
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(21),[class] nvarchar(22))
Insert #a
select 1,N'甲',N'语文' union all
select 2,N'甲',N'数学' union all
select 3,N'甲',N'英语' union all
select 4,N'甲',N'科学' union all
select 5,N'乙',N'语文' union all
select 6,N'乙',N'英语' union all
select 7,N'丙',N'数学' union all
select 8,N'丙',N'语文' union all
select 9,N'丙',N'科学'
Go
--测试数据结束
SELECT ROW_NUMBER()OVER(ORDER BY mid) id,name,num,t.class FROM (
SELECT [name] ,
MIN(id) AS mid,
COUNT(1) AS num,
STUFF(( SELECT ',' + class
FROM #a
WHERE name = a.name
FOR
XML PATH('')
), 1, 1, '') AS [class]
FROM #a a
GROUP BY a.name )t
2、
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(21))
Insert #a
select 1,N'甲' union all
select 2,N'乙' union all
select 3,N'丙'
GO
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([id] int,[a_id] int,[class] nvarchar(22))
Insert #b
select 1,1,N'语文' union all
select 2,1,N'数学' union all
select 3,1,N'英语' union all
select 4,1,N'科学' union all
select 5,2,N'语文' union all
select 6,2,N'英语' union all
select 7,3,N'数学' union all
select 8,3,N'语文' union all
select 9,3,N'科学'
Go
--测试数据结束
SELECT id,name ,
(SELECT COUNT(1) FROM #b WHERE a_id = a.id) AS num,
STUFF(( SELECT ',' + class
FROM #b
WHERE a_id = a.id
FOR
XML PATH('')
), 1, 1, '') AS [class]
FROM #a a
GROUP BY a.id,a.name
