22,209
社区成员
发帖
与我相关
我的任务
分享
create table Brand([ID] int,[Name] varchar(5))
insert Brand
select 1,'品牌1' union all
select 2,'品牌2' union all
select 3,'品牌3'
GO
create table BrandOfCategory([ID] int,[BrandID] int,[CategoryID] int)
insert BrandOfCategory
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3
GO
select * from
(select id,name,CategoryList=stuff((select ','+ltrim([CategoryID]) from BrandOfCategory where brandid=a.id for xml path('')),1,1,'') from brand a)a
where CategoryList is not null
--结果:
id name CategoryList
----------- ----- ------------
1 品牌1 1,2,3
--> 测试数据:Brand表
if object_id('Brand表') is not null drop table Brand表
---->建表
create table Brand表([ID] int,[Name] varchar(5))
insert Brand表
select 1,'品牌1' union all
select 2,'品牌2' union all
select 3,'品牌3'
GO
--> 测试数据:BrandOfCategory表
if object_id('BrandOfCategory表') is not null drop table BrandOfCategory表
---->建表
create table BrandOfCategory表([ID] int,[BrandID] int,[CategoryID] int)
insert BrandOfCategory表
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3
GO
--> 查询结果
SELECT * FROM Brand表
--> 删除表格
--DROP TABLE Brand表
--> 查询结果
SELECT * FROM BrandOfCategory表
--> 删除表格
--DROP TABLE BrandOfCategory表
SELECT distinct a.ID,a.Name,
CategoryList =STUFF(REPLACE(REPLACE(
(select CategoryID
FROM Brand表 a join BrandOfCategory表 b
on a.ID = b.BrandID
FOR XML AUTO
), '<b CategoryID="', ','), '"/>', ''), 1, 1, '')
FROM Brand表 a join BrandOfCategory表 b
on a.ID = b.BrandID
--
ID Name CategoryList
1 品牌1 1,2,3