思路:
即取到如下四条记录,可以用开窗函数row_number对产品和仓库进行分组,并根据id排序
2 电视 1 3
3 电视 2 1 5 空调 1 5
6 空调 2 3
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY PRO_NAME, PRO_STOCK ORDER BY ID DESC ) RN FROM STOCK )
WHERE RN = 1;
以以上sql结果为结果集,再一次做聚集并使用case when 列传行就可以了。
SELECT PRO_NAME, MAX(CASE WHEN PRO_STOCK = '1' THEN pro_amount END),
MAX(CASE WHEN PRO_STOCK = '2' THEN pro_amount END)
FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY PRO_NAME, PRO_STOCK ORDER BY ID DESC ) RN FROM STOCK )
WHERE RN = 1
) GROUP BY PRO_NAME;
即取到如下四条记录,可以用开窗函数row_number对产品和仓库进行分组,并根据id排序
2 电视 1 3
3 电视 2 1 5 空调 1 5
6 空调 2 3
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY PRO_NAME, PRO_STOCK ORDER BY ID DESC ) RN FROM STOCK )
WHERE RN = 1;
以以上sql结果为结果集,再一次做聚集并使用case when 列传行就可以了。
SELECT PRO_NAME, MAX(CASE WHEN PRO_STOCK = '1' THEN pro_amount END),
MAX(CASE WHEN PRO_STOCK = '2' THEN pro_amount END)
FROM (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY PRO_NAME, PRO_STOCK ORDER BY ID DESC ) RN FROM STOCK )
WHERE RN = 1
) GROUP BY PRO_NAME;
(
SELECT PRO_NAME "品名",stock_name ,PRO_AMOUNT
FROM STOCK S1,stock2
where s1.pro_stock=stock_id
and NOT EXISTS (SELECT 1 FROM STOCK S2 WHERE S1.ID<S2.ID AND S1.PRO_NAME=S2.PRO_NAME
AND S1.PRO_STOCK=S2.PRO_STOCK)
ORDER BY PRO_NAME
)
PIVOT
( SUM(PRO_AMOUNT)
FOR stock_name IN ('东仓' as "东仓数量",'西仓' as "西仓数量")
)
其中stock是后面的具体信息表,stock2是前面的对照表。
DROP TABLE [库存表]
GO
CREATE TABLE [库存表](stock_id int,stock_name VARCHAR(10))
INSERT INTO [库存表] SELECT 1,'东仓' UNION ALL SELECT 2,'西仓'
IF OBJECT_ID('[库存详细]') IS NOT NULL
DROP TABLE [库存详细]
GO
CREATE TABLE [库存详细](id int,pro_name VARCHAR(10),pro_stock VARCHAR(10),pro_amount VARCHAR(10))
INSERT INTO [库存详细] SELECT 1,'电视',1,5 UNION ALL SELECT
2,'电视',1,3 UNION ALL SELECT
3,'电视',2,1 UNION ALL SELECT
4,'空调',1,1 UNION ALL SELECT
5,'空调',1,5 UNION ALL SELECT
6,'空调',2,3
GO
-----------------静态查询--------------------------------------------------------------------
SELECT a.pro_name AS '品名',MAX(CASE WHEN a.pro_stock=1 THEN pro_amount ELSE '' END) '东仓数量',
MAX(CASE WHEN a.pro_stock=2 THEN pro_amount ELSE '' END) '西仓数量' FROM [库存详细] a,
(SELECT MAX(id) AS id,pro_name,pro_stock FROM
[库存详细] GROUP BY pro_name,pro_stock) b WHERE a.id=b.id GROUP BY a.pro_name-----------------动态查询--------------------------------------------------------------------
DECLARE @sql NVARCHAR(max)
SET @sql='SELECT a.pro_name as ''品名'' '
SELECT @sql=@sql+',MAX(CASE WHEN a.pro_stock='+CONVERT(VARCHAR(10),stock_id)+' THEN pro_amount ELSE '''' END) '''+stock_name+'数量'+''' '
FROM (SELECT stock_id, stock_name FROM [库存表]) a
SELECT @sql=@sql+' FROM [库存详细] a,
(SELECT MAX(id) AS id,pro_name,pro_stock FROM
[库存详细] GROUP BY pro_name,pro_stock) b WHERE a.id=b.id GROUP BY a.pro_name'
EXEC(@sql)
-----------------结果--------------------------------------------------------------------
/*
品名 东仓数量 西仓数量
---------- ---------- ----------
电视 3 1
空调 5 3(2 行受影响)
*/