34,587
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[商品id] nvarchar(22),[商品名称] nvarchar(22),[库存数量] int)
Insert #A
select 1,N'01',N'可乐',120 union all
select 2,N'03',N'雪碧',48
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([商品id] nvarchar(22),[单位名称] nvarchar(21),[单位类型] nvarchar(24),[换算率] int)
Insert #B
select N'01',N'瓶',N'基础单位',1 union all
select N'01',N'箱',N'辅助单位',12 union all
select N'03',N'瓶',N'基础单位',1 union all
select N'03',N'箱',N'辅助单位',24 union all
select N'03',N'大箱',N'辅助单位',48
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
SELECT #A.*,#B.单位名称,#B.单位类型,#B.换算率,#B.单位类型+RTRIM(ROW_NUMBER()OVER(PARTITION BY #B.商品id,#B.单位类型 ORDER BY GETDATE())) AS 类型 FROM #B JOIN #A on #B.商品id = #A.商品id
)
select 商品id,商品名称,库存数量'
;WITH cte AS (
SELECT *,单位类型+RTRIM(ROW_NUMBER()OVER(PARTITION BY 商品id,单位类型 ORDER BY GETDATE())) AS 类型 FROM #B
)
SELECT @sql = @sql + ',max(case 类型 when ''' + 类型
+ ''' then 单位名称 else null end)[' + 类型 + '],max(case 类型 when ''' + 类型
+ ''' then 库存数量/换算率 else null end)[' + 类型 + '数量]'
FROM ( SELECT DISTINCT
类型
FROM cte
) a
SET @sql = @sql
+ ' from cte group by 商品id,商品名称,库存数量'
EXEC(@sql)
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[商品id] nvarchar(22),[商品名称] nvarchar(22),[库存数量] int)
Insert #A
select 1,N'01',N'可乐',120 union all
select 2,N'03',N'雪碧',48
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([商品id] nvarchar(22),[单位名称] nvarchar(21),[单位类型] nvarchar(24),[换算率] int)
Insert #B
select N'01',N'瓶',N'基础单位',1 union all
select N'01',N'箱',N'辅助单位',12 union all
select N'03',N'瓶',N'基础单位',1 union all
select N'03',N'箱',N'辅助单位',24
Go
--测试数据结束
SELECT
#A.商品id,
商品名称,
库存数量,
b1.单位名称 AS 基础单位,
库存数量 / b1.换算率 AS 基础单位数量,
b2.单位名称 AS 辅助单位,
库存数量 / b2.换算率 AS 辅助单位数量
FROM
#A
LEFT JOIN
#B b1
ON b1.商品id = #A.商品id
AND b1.单位类型 = '基础单位'
LEFT JOIN
#B b2
ON b2.商品id = #A.商品id
AND b2.单位类型 = '辅助单位';
USE tempdb
GO
IF OBJECT_ID('storeA') IS NOT NULL DROP TABLE storeA;
IF OBJECT_ID('unitB') IS NOT NULL DROP TABLE unitB;
GO
CREATE TABLE storeA(
id INT PRIMARY KEY,
productId VARCHAR(10),
productName NVARCHAR(10),
storeNum INT
)
GO
CREATE TABLE unitB(
productId VARCHAR(10),
unitName NVARCHAR(10),
unitType NVARCHAR(10),
convertRate INT
)
GO
INSERT INTO storeA(id,productId,productName,storeNum) VALUES(1,'01','可乐',120)
INSERT INTO storeA(id,productId,productName,storeNum) VALUES(2,'03','雪碧',48)
GO
INSERT INTO unitB(productId,unitName,unitType,convertRate) VALUES ('01','瓶','基础单位','1');
INSERT INTO unitB(productId,unitName,unitType,convertRate) VALUES ('01','箱','辅助单位','12');
INSERT INTO unitB(productId,unitName,unitType,convertRate) VALUES ('03','瓶','基础单位','1');
INSERT INTO unitB(productId,unitName,unitType,convertRate) VALUES ('03','箱','辅助单位','24');
GO
SELECT
a.productId AS [商品id]
,a.productName AS [商品名称]
,a.storeNum AS [库存数量]
,(SELECT TOP 1 b.unitName FROM unitB AS b WHERE a.productId=b.productId AND b.unitType='基础单位') AS [基础单位]
,a.storeNum AS [基础单位数量]
,(SELECT TOP 1 b.unitName FROM unitB AS b WHERE a.productId=b.productId AND b.unitType='辅助单位') AS [辅助单位]
,a.storeNum/(SELECT TOP 1 b.convertRate FROM unitB AS b WHERE a.productId=b.productId AND b.unitType='辅助单位') AS [辅助单位数量]
FROM storeA AS a