算法（根据提供的某数值，找出与其最接近的一组数据）

问题贴地址：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

*/