sql分类汇总拼接字符

[snlixing]

A表：

ID 品名 规格 颜色 数量

1 aaa a-1 红色 50
2 bbb NULL 红色 60
3 aaa a-1 红色 50
4 ccc c-1 NULL 50
5 bbb NULL 红色 60



得到的结果：

品名 规格 颜色 数量 ID

aaa a-1 红色 100 1,3
bbb NULL 红色 120 2,5
ccc c-1 NULL 50 4

[junes06]

--创建一个函数，通过品名获取id，通过，组合
create function getid( @name varchar(20))

returns varchar(max)
as
begin
  declare @id varchar(max)=' '
  select  @id =@id+','+cast(orderid as varchar) from orders
  where   name=@name
  set  @id=right(@id,len(@id)-2)
  return  @id
end
go


--分组查询

select name ,规格,颜色, SUM(数量),dbo.getid(name)
from 表
group by name ,规格,颜色, SUM(数量)

[junes06]

--创建一个函数，通过品名获取id，通过，组合
create function getid( @name varchar(20))
 
returns varchar(max)
as
begin
  declare @id varchar(max)=' '
  select  @id =@id+','+cast(orderid as varchar) from 表  ---换成你的表名
  where   name=@name
  set  @id=right(@id,len(@id)-2)
  return  @id
end
go
 
 
--分组查询
 
select name ,规格,颜色, SUM(数量),dbo.getid(name)
from 表
group by name ,规格,颜色, SUM(数量)

表名称忘改了

[snlixing]

引用 1 楼 junes06 的回复:

--创建一个函数，通过品名获取id，通过，组合
create function getid( @name varchar(20))

returns varchar(max)
as
begin
  declare @id varchar(max)=' '
  select  @id =@id+','+cast(orderid as varchar) from orders
  where   name=@name
  set  @id=right(@id,len(@id)-2)
  return  @id
end
go


--分组查询

select name ,规格,颜色, SUM(数量),dbo.getid(name)
from 表
group by name ,规格,颜色, SUM(数量)

不用函数有方法吗

[snlixing]

我用stuff((select ','+ID from A 提示在将 varchar 值 ',' 转换成数据类型 int 时失败

[junes06]

把id 用 cast(id as varchar) 转换一下

[吉普赛的歌]

DECLARE @t TABLE (
	id INT
	,品名 nvarchar(10)
	,规格 nvarchar(10)
	,颜色 nvarchar(10)
	, 数量 int
)
INSERT INTO @t
SELECT           '1','aaa','a-1','红色','50'
union all select '2','bbb',NULL,'红色','60'
union all select '3','aaa','a-1','红色','50'
union all select '4','ccc','c-1',NULL,'50'
union all select '5','bbb',NULL,'红色','60'

SELECT  品名
,规格
,stuff((SELECT ','+isnull(颜色,'') from @t AS b WHERE a.[品名]=b.[品名] group by 颜色 for xml path('')),1,1,'') as 颜色
,(SELECT SUM(数量) from @t AS b WHERE a.[品名]=b.[品名]) as 数量
,stuff((SELECT ','+isnull(CAST(id AS VARCHAR(50)),'') from @t AS b WHERE a.[品名]=b.[品名] 
        group by CAST(id AS VARCHAR(50)) for xml path('')),1,1,'') as ID
FROM @t AS a
GROUP BY 品名,规格
/*
品名	规格	颜色	数量	ID
aaa	     a-1	红色	100	    1,3
bbb	     NULL	红色	120	    2,5
ccc	     c-1		    50	    4
*/

[中国风]

e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[品名] nvarchar(23),[规格] nvarchar(23),[颜色] nvarchar(22),[数量] int)
Insert #A
select 1,N'aaa',N'a-1',N'红色',50 union all
select 2,N'bbb',null,N'红色',60 union all
select 3,N'aaa',N'a-1',N'红色',50 union all
select 4,N'ccc',N'c-1',null,50 union all
select 5,N'bbb',null,N'红色',60
Go
Select [品名],[规格],[颜色],[数量]=SUM([数量]),STUFF((SELECT ','+RTRIM([ID]) FROM #A WHERE [品名]=a.[品名] FOR XML PATH('')),1,1,'') AS IDs from #A AS a GROUP BY [品名],[规格],[颜色]
/*
品名	规格	颜色	数量	IDs
aaa	a-1	红色	100	1,3
bbb	NULL	红色	120	2,5
ccc	c-1	NULL	50	4
*/