34,593
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([编号] int,[名称] nvarchar(22),[价格] int)
Insert #A
select 1,N'可乐',2 union all
select 2,N'鸡翅',3
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([编号] int,[名称] nvarchar(24))
Insert #B
select 1,N'省级代理' union all
select 2,N'县级代理' union all
select 3,N'镇级代理'
GO
IF not object_id(N'Tempdb..#C') is null
drop table #C
Go
Create table #C([代理商编号] int,[商品编号] int,[价格] decimal(18,2))
Insert #C
select 1,1,1.5 union all
select 2,1,1.8 union all
select 2,2,2 union all
select 3,2,1.6
Go
--测试数据结束
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT a.编号 ,
a.名称 ,
a.价格 AS 零售价 '
SELECT @sql = @sql + ',max(case when [代理商编号] = ''' + RTRIM([编号])
+ ''' then c.[价格] else null end ) as[' + RTRIM(名称) + ']'
FROM ( SELECT DISTINCT
[名称] ,
[编号]
FROM #B
) a
SET @sql = @sql
+ ' from #A a join #C c on c.[商品编号] = a.[编号] join #B b on c.[代理商编号] = b.[编号] group by a.编号 ,
a.名称 ,
a.价格'
EXEC(@sql)
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([编号] int,[名称] nvarchar(22),[价格] int)
Insert #A
select 1,N'可乐',2 union all
select 2,N'鸡翅',3
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([编号] int,[名称] nvarchar(24))
Insert #B
select 1,N'省级代理' union all
select 2,N'县级代理' union all
select 3,N'镇级代理'
GO
IF not object_id(N'Tempdb..#C') is null
drop table #C
Go
Create table #C([代理商编号] int,[商品编号] int,[价格] decimal(18,7))
Insert #C
select 1,1,1.5 union all
select 2,1,1.8 union all
select 2,2,2 union all
select 3,2,1.6
Go
--测试数据结束
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT a.编号 ,
a.名称 ,
a.价格 AS 零售价 '
SELECT @sql = @sql + ',max(case when [代理商编号] = ''' + RTRIM([编号])
+ ''' then b.[名称] else null end ) as[' + RTRIM(名称) + ']'
FROM ( SELECT DISTINCT
[名称] ,
[编号]
FROM #B
) a
SET @sql = @sql
+ ' from #A a join #C c on c.[商品编号] = a.[编号] join #B b on c.[代理商编号] = b.[编号] group by a.编号 ,
a.名称 ,
a.价格'
EXEC(@sql)
SELECT a.编号 ,
a.名称 ,
a.价格 AS 零售价 ,
MAX(CASE WHEN b.代理商编号 = 1 THEN b.价格
END) AS [1_省代理商] ,
MAX(CASE WHEN b.代理商编号 = 2 THEN b.价格
END) AS [2_县代理商] ,
MAX(CASE WHEN b.代理商编号 = 3 THEN b.价格
END) AS [3_镇代理商]
FROM 商品零售价 AS a
INNER JOIN 代理商价格 AS b ON b.商品编号 = a.编号
GROUP BY a.编号 ,
a.名称 ,
a.价格;