求一sql，太有挑战性了，今天要用到，很急！！

Question

select * from (
select qty,bookcode,sumqty = (select sum(qty) from Tab where qyt < a.qty)
from Tab A
)A where sumqty <= 4000

Answer 1

是按记录从上往下，还是随意的取sum(qty)为4000的？

Answer 2

DECLARE @TB TABLE([qty] INT, [bookcode] VARCHAR(10), [id] INT IDENTITY(1,1))
INSERT @TB 
SELECT 199, '006124200' UNION ALL 
SELECT 180, '006125574' UNION ALL 
SELECT 173, '030726600' UNION ALL 
SELECT 166, '0494437051' UNION ALL 
SELECT 182, '050023842' UNION ALL 
SELECT 176, '057789407' UNION ALL 
SELECT 157, '059040360' UNION ALL 
SELECT 184, '059092156' UNION ALL 
SELECT 161, '069105060';WITH CSDYYR AS
(
SELECT *,PATH=CAST(id AS VARCHAR(8000)),TOTAL=qty FROM @TB 
UNION ALL 
SELECT B.*,PATH=PATH+'-'+RTRIM(B.id),C.TOTAL+B.qty
FROM @TB AS B,CSDYYR AS C WHERE B.id>C.id AND C.TOTAL<500
)SELECT B.* 
FROM @TB AS B,(SELECT TOP 1 PATH FROM CSDYYR WHERE TOTAL BETWEEN 350 AND 450 ORDER BY NEWID()) C
WHERE CHARINDEX('-'+RTRIM(B.id)+'-', '-'+PATH+'-')>0
/*
qty         bookcode   id
----------- ---------- -----------
176         057789407  6
184         059092156  8
*/

Answer 3

--测试环境
declare @t table(bookno varchar(100),qrt int)
declare @i int
while isnull(@i,1)<=500
begin
insert into @t select isnull(@i,1),abs(checksum(newid()))%1000
set @i=isnull(@i,1)+1
end
--测试语句
declare @r table(bookno varchar(100),qrt int)
insert into @r select top 1 * from @t where qrt<4000
while @@rowcount>0
begin
insert into @r select top 1 * from @t 
 where (select sum(qrt) from @r)+qrt<4000 and bookno not in (select bookno from @r) order by newid()
end
--测试结果
select sum(qrt) from @r

Answer 4

如果是从上向下的话with
wang as (select row=row_number() over (order by getdate()),* from tb),
wang1 as (select qty1=(select sum(qty) from wang where row<=t.row),* from wang t),
wang2 as (select *,cha=abs(qty1-4000) from wang1)select * from wang2 t where not exists(select 1 from wang2 where cha<t.cha)

Answer 5

--2005
DECLARE @tb TABLE(qty int,bookcode nvarchar(30))
INSERT INTO @tb
SELECT 
199 ,       '006124200' UNION ALL SELECT
580 ,       '006125574' UNION ALL SELECT
773 ,       '030726600' UNION ALL SELECT
266 ,       '0494437051' UNION ALL SELECT
682 ,       '050023842' UNION ALL SELECT
376 ,       '057789407' UNION ALL SELECT
155 ,       '059040360' UNION ALL SELECT
684 ,       '059092126' UNION ALL SELECT
284 ,       '059092136' UNION ALL SELECT
384 ,       '059092146' UNION ALL SELECT
584 ,       '059092156' UNION ALL SELECT
584 ,       '059092166' UNION ALL SELECT
1184 ,       '059092156' UNION ALL SELECT
1284 ,       '029092156' UNION ALL SELECT
1184 ,       '039092156' UNION ALL SELECT
1184 ,       '049092156' UNION ALL SELECT
161 ,       '069105060'
 ;
WITH CTE
AS
(
SELECT qty,cast(bookcode as varchar(max)) as codes,qty as TotalQty,1 as lvl
FROM @tb
UNION ALL
SELECT a.qty,cast(b.codes+','+a.bookcode as varchar(max)),b.TotalQty+a.Qty,b.lvl+1
FROM @tb a,CTE b
WHERE CHARINDEX(a.bookcode,b.codes)<=0
AND b.TotalQty+a.Qty<=4050 --
)
SELECT TOP 1 codes,TotalQty FROM CTE WHERE TotalQty Between 3950 AND 4050 ORDER BY TotalQty DESC,NEWID()

Answer 6

有没有简单的写法，我机子上只有sql server 2000，实现不了！

Answer 7

》》有没有简单的写法，我机子上只有sql server 2000，实现不了！
我的应该可以

Answer 8

select       bookcode ,sum(qty) from tablename
group by bookcode 
having sum(qty)>3500 and sum(qty)<4500
是不是这样

Answer 9

set nocount on
declare @ab table(qty int,bookcode varchar(50))
insert @ab select 199,        '006124200'
insert @ab select 180,        '006125574' 
insert @ab select 173,        '030726600' 
insert @ab select 166,        '049443701' 
insert @ab select 182,        '050023842' 
insert @ab select 176,        '057789407' 
insert @ab select 157,        '059040360' 
insert @ab select 184,        '059092156' 
insert @ab select 161,        '069105060' declare @sum int
set @sum = 1000declare @a table(qty int,bookcode varchar(50),bookcodec varchar(100),cnt int)
declare @b table(qty int,bookcode varchar(50),bookcodec varchar(100),cnt int)
declare @c table(qty int,bookcode varchar(50),bookcodec varchar(100),cnt int)
insert @a select qty,bookcode, rtrim(bookcode),1 from @ab where qty <=@sumif not exists(select 1 from @a)
begin
  select bookcode, qty, cnt = 1 from @ab where qty = (select min(qty) from @ab)
  return
endwhile not exists(select 1 from @a where qty = @sum ) and exists(select 1 from @a)
begin
  delete @b 
  insert @b select * from @a
  delete @a
  insert @a select a.qty+b.qty,b.bookcode,a.bookcodec+','+rtrim(b.bookcode),a.cnt+1
    from @b a, @ab b where a.qty+b.qty <=@sum and a.bookcode<b.bookcode
  insert @c select * from @a
end
select bookcodec,a.qty,a.cnt from @c a join
  (select qty,min(cnt) cnt from @c where abs(qty-@sum) = (select min(abs(qty-@sum)) from @c) group by qty) b
  on a.qty = b.qty and a.cnt = b.cntset nocount off
/*
bookcodec                                                                                            qty         cnt         
---------------------------------------------------------------------------------------------------- ----------- ----------- 
006124200,006125574,050023842,057789407,059092156                                                    921         5
*/

Answer 10

select       bookcode ,sum(qty) from tablename
group by bookcode 
having sum(qty)>3500 and sum(qty)<4500

Answer 11

提供一个思路：随机+有序=随机。下面是随机取出总和约为500的一个组合：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([qty] int,[bookcode] varchar(10))
insert [tb]
select 199,'006124200' union all
select 180,'006125574' union all
select 173,'030726600' union all
select 166,'0494437051' union all
select 182,'050023842' union all
select 176,'057789407' union all
select 157,'059040360' union all
select 184,'059092156' union all
select 161,'069105060'
go
--select * from [tb]--随机排序
select id=identity(int,1,1),qty,bookcode 
into #1
from tb
order by newid() desc
--顺序求和
select id,qty,bookcode,d=(select abs(sum(qty)-500) from #1 where id<=t.id)
into #2
from #1 t
--选取与500最接近的一个组合
select qty,bookcode
from #2
where id<=(select top 1 id from #2 order by d)
--结果一：
/*
qty         bookcode
----------- ----------
180         006125574
184         059092156
182         050023842
*/
--结果二：
/*
qty         bookcode
----------- ----------
173         030726600
166         0494437051
180         006125574
*/
drop table #1
drop table #2
考虑到效率问题，使用了临时表做中转，没有用一个大长句子来完成。

Answer 12

order by newid() desc 的desc忘记去掉了。

Answer 13

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([qty] int,[bookcode] varchar(10))
insert [tb]
select 199,'006124200' union all
select 180,'006125574' union all
select 173,'030726600' union all
select 166,'0494437051' union all
select 182,'050023842' union all
select 176,'057789407' union all
select 157,'059040360' union all
select 184,'059092156' union all
select 161,'069105060'
go
--select * from [tb]--随机排序
select id=identity(int,1,1),qty,bookcode 
into #1
from tb
order by newid() desc
--顺序求和
select id,qty,bookcode,d=(select abs(sum(qty)-500) from #1 where id<=t.id)
into #2
from #1 t
--选取与500最接近的一个组合
select qty,bookcode
from #2
where id<=(select top 1 id from #2 order by d)
--结果一：
/*
qty         bookcode
----------- ----------
180         006125574
184         059092156
182         050023842
*/
--结果二：
/*
qty         bookcode
----------- ----------
173         030726600
166         0494437051
180         006125574
*/
drop table #1
drop table #2