问题贴地址：http://topic.csdn.net/u/20100423/11/aee2cb0f-2131-4582-8f54-7e50170563f4.html?63521
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-24 20:26:38
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([物料] NVARCHAR(10),[货位] NVARCHAR(10),[数量] INT)
INSERT [tb]
SELECT 'A','C0100201',11 UNION ALL
SELECT 'A','C0100202',5 UNION ALL
SELECT 'A','C0100301',42 UNION ALL
SELECT 'A','C0100302',565 UNION ALL
SELECT 'A','C0100401',5 UNION ALL
SELECT 'A','C0100702',15 UNION ALL
SELECT 'A','C0100801',1234 UNION ALL
SELECT 'A','C0100902',123 UNION ALL
SELECT 'A','C0101201',33 UNION ALL
SELECT 'A','C0107202',138 UNION ALL
SELECT 'A','C0101301',9 UNION ALL
SELECT 'A','C0109302',13
--SELECT * FROM [tb]
-->SQL查询如下:
--第一种需求：总数大于50且最接近50
;with t as
(
select *,path=cast([货位] as varchar(8000)),total=[数量] from tb
union all
select b.物料,b.[货位],b.[数量],
cast(a.path+'.'+rtrim(b.[货位]) as varchar(8000)),a.total+b.[数量]
from t a join tb b on a.[货位]<b.[货位] and a.total<=50
)
select b.*,[path]
from (select top 1 path from t where total>=50 order by total,LEN(path)) a
join tb b
on charindex('.'+b.货位+'.','.'+a.path+'.')>0
/*
物料 货位 数量 path
A C0100301 42 C0100301.C0101301
A C0101301 9 C0100301.C0101301
*/
--第二种需求：总数小于50且最接近50
;with t as
(
select *,path=cast([货位] as varchar(8000)),total=[数量] from tb
union all
select b.物料,b.[货位],b.[数量],
cast(a.path+'.'+rtrim(b.[货位]) as varchar(8000)),a.total+b.[数量]
from t a join tb b on a.[货位]<b.[货位] and a.total<=50
)
select b.*,[path]
from (select top 1 path from t where total<=50 order by total desc,LEN(path)) a
join tb b
on charindex('.'+b.货位+'.','.'+a.path+'.')>0
/*
物料 货位 数量 path
A C0100201 11 C0100201.C0100401.C0101201
A C0100401 5 C0100201.C0100401.C0101201
A C0101201 33 C0100201.C0100401.C0101201
*/
--同时存在多物料代码的情况
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([物料] NVARCHAR(10),[货位] NVARCHAR(10),[数量] INT)
INSERT [tb]
SELECT 'A','C0100201',11 UNION ALL
SELECT 'A','C0100202',5 UNION ALL
SELECT 'A','C0100301',42 UNION ALL
SELECT 'A','C0100302',565 UNION ALL
SELECT 'A','C0100401',5 UNION ALL
SELECT 'A','C0100702',15 UNION ALL
SELECT 'A','C0100801',1234 UNION ALL
SELECT 'A','C0100902',123 UNION ALL
SELECT 'A','C0101201',33 UNION ALL
SELECT 'A','C0107202',138 UNION ALL
SELECT 'A','C0101301',9 UNION ALL
SELECT 'A','C0109302',13 UNION ALL
SELECT 'B','C0100202',5 UNION ALL
SELECT 'B','C0100301',48 UNION ALL
SELECT 'B','C0100302',565 UNION ALL
SELECT 'B','C0100401',5 UNION ALL
SELECT 'B','C0100702',15 UNION ALL
SELECT 'B','C0100801',1234 UNION ALL
SELECT 'B','C0100902',123 UNION ALL
SELECT 'B','C0101201',33 UNION ALL
SELECT 'B','C0107202',138 UNION ALL
SELECT 'B','C0101301',9 UNION ALL
SELECT 'B','C0109302',13
--SELECT * FROM [tb]
-->SQL查询如下：
--指定的数量
--第一种需求：总数大于50且最接近50
;with t as
(
select *,列表=cast([货位] as varchar(8000)),total=[数量]
from tb
union all
select b.物料,b.[货位],b.[数量],
cast(a.列表+','+rtrim(b.[货位]) as varchar(8000)),a.total+b.[数量]
from t a
join tb b
on a.物料=b.物料 and a.[货位]<b.[货位] and a.total<=50
)
select b.*,列表
from (
select * from t tx
where 列表 in(
select top 1 列表 from t
where 物料=tx.物料
and total>=50 order by total,LEN(列表))
) a
join tb b
on a.物料=b.物料 and charindex(','+b.货位+',',','+a.列表+',')>0
order by 物料
/*
物料 货位 数量 列表
A C0100301 42 C0100301,C0101301
A C0101301 9 C0100301,C0101301
B C0100401 5 C0100401,C0101201,C0109302
B C0101201 33 C0100401,C0101201,C0109302
B C0109302 13 C0100401,C0101201,C0109302
*/
--第二种需求：总数小于50且最接近50
;with t as
(
select *,列表=cast([货位] as varchar(8000)),total=[数量]
from tb
union all
select b.物料,b.[货位],b.[数量],
cast(a.列表+','+rtrim(b.[货位]) as varchar(8000)),a.total+b.[数量]
from t a
join tb b
on a.物料=b.物料 and a.[货位]<b.[货位] and a.total<=50
)
select b.*,列表
from (
select * from t tx
where 列表 in(
select top 1 列表 from t
where 物料=tx.物料
and total<=50 order by total desc,LEN(列表))
) a
join tb b
on a.物料=b.物料 and charindex(','+b.货位+',',','+a.列表+',')>0
order by 物料
/*
物料 货位 数量 列表
A C0100201 11 C0100201,C0100401,C0101201
A C0100401 5 C0100201,C0100401,C0101201
A C0101201 33 C0100201,C0100401,C0101201
B C0100301 48 C0100301
*/
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/24/5525281.aspx