一个高难度的MS SQL问题请教各位高手！

zhangshzh 2002-09-12 10:18:50
现有表test结构如下
No Type Quantity
1 A 21
2 A 20
3 A 13
4 A 15
5 A 14
6 A 17
7 B 11
8 B 21
9 B 26
10 B 22
11 B 37
12 B 29
13 B 28
14 B 16
15 B 36
16 B 25
17 B 13
18 B 12
19 B 20
20 B 31
21 C 21
......
......


要求：
从每个相同类别（Type）的数据中取出几个序号（个数不固定），取出来的序号满足以下几个条件：
1、取出来的序号对应Quantity的和大于或等于此类别所有序号对应Quantity的和的5/1 （即sub_sum(quantity)>=total_sum(quantity)*0.2）
2、没有任何其他的序号组合对应Quantity的和比所取出序号对应Quantity的和（即sub_sum(quantity)）更小且满足条件1
3、取出来的序号个数最少

如上表数据，
A类取出的最终结果应该是序号2
B类取出的最终结果应该是序号11,12
...全文
36 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhangshzh 2002-11-09
  • 打赏
  • 举报
 回复
谢谢J9988，问题已解决，结贴！
caishuni 2002-11-02
  • 打赏
  • 举报
 回复
Toj9988:高手！·一定向你学习！！·
j9988 2002-11-02
  • 打赏
  • 举报
 回复
昨晚喝晕了！SORRY！
create table t(No int, Type varchar(10), Quantity int)
insert t select 1, 'A', 21
union all select 2 ,'A', 20
union all select 3 ,'A', 13
union all select 4 ,'A', 15
union all select 5 ,'A', 14
union all select 6 ,'A', 17
union all select 7 ,'B', 11
union all select 8 ,'B', 21
union all select 9 ,'B', 26
union all select 10 ,'B', 22
union all select 11 ,'B', 37
union all select 12 ,'B', 29
union all select 13 ,'B', 28
union all select 14 ,'B', 16
union all select 15 ,'B', 36
union all select 16 ,'B', 25
union all select 17 ,'B', 13
union all select 18 ,'B', 12
union all select 19 ,'B', 20
union all select 20 ,'B', 31
--union all select 21 ,'B', 1
--union all select 22 ,'B', 1
union all select 23 ,'C', 21



create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant decimal(10,2),@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant

if exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)>right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
)
begin
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
end

insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity>=@quant
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by quantity,len([NO])
end

-- 调用语句：
declare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output

select * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0

No Type Quantity
----------- ---------- -----------
2 A 20

（所影响的行数为 1 行）
No Type Quantity
----------- ---------- -----------
11 B 37
12 B 29
mnjrh 2002-11-02
  • 打赏
  • 举报
 回复
create table t(No int, Type varchar(10), Quantity int)
insert t select 1, 'A', 21
union all select 2 ,'A', 20
union all select 3 ,'A', 13
union all select 4 ,'A', 15
union all select 5 ,'A', 14
union all select 6 ,'A', 17
union all select 7 ,'B', 11
union all select 8 ,'B', 21
union all select 9 ,'B', 26
union all select 10 ,'B', 22
union all select 11 ,'B', 37
union all select 12 ,'B', 29
union all select 13 ,'B', 28
union all select 14 ,'B', 16
union all select 15 ,'B', 36
union all select 16 ,'B', 25
union all select 17 ,'B', 13
union all select 18 ,'B', 12
union all select 19 ,'B', 20
union all select 20 ,'B', 31
--union all select 21 ,'B', 1
--union all select 22 ,'B', 1
union all select 23 ,'C', 21



create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant decimal(10,2),@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant

if exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)>right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
)
begin
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
end

insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity>=@quant
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by quantity,len([NO])
end

declare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output

select * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0

No Type Quantity
----------- ---------- -----------
2 A 20

（所影响的行数为 1 行）
No Type Quantity
----------- ---------- -----------
11 B 37
12 B 29
mnjrh 2002-11-02
  • 打赏
  • 举报
 回复
up
j9988 2002-11-02
  • 打赏
  • 举报
 回复
数据如：
No Type Quantity
----------- ---------- -----------
21 C 21
22 C 1
23 C 2
24 C 1
25 C 2
26 C 1
27 C 3
28 C 1
29 C 3
合计　　合计＊0.2　　　
-----　 --------
35 7.0

答案其实有三组：（22.27.29）和（23.25.27）（23.25.29）程序中只取一组

No Type Quantity
----- ---------- -----------
22 C 1
27 C 3
29 C 3
j9988 2002-11-02
  • 打赏
  • 举报
 回复
一天进不来!上面错把WHERE写成IF了.
-------建表-------
create table t(No int, Type varchar(10), Quantity
int)
insert t select 1, 'A', 21
union all select 2 ,'A', 20
union all select 3 ,'A', 13
union all select 4 ,'A', 15
union all select 5 ,'A', 14
union all select 6 ,'A', 17
union all select 7 ,'B', 11
union all select 8 ,'B', 21
union all select 9 ,'B', 26
union all select 10 ,'B', 22
union all select 11 ,'B', 37
union all select 12 ,'B', 29
union all select 13 ,'B', 28
union all select 14 ,'B', 16
union all select 15 ,'B', 36
union all select 16 ,'B', 25
union all select 17 ,'B', 13
union all select 18 ,'B', 12
union all select 19 ,'B', 20
union all select 20 ,'B', 31
union all select 21 ,'C', 21

-------过程-------
create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant decimal(10,2),@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant
while exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
and a.no+','+right('000'+cast(b.NO as varchar(10)),4)
not in
(select no from @b)

)
begin
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
and a.no+','+right('000'+cast(b.NO as varchar(10)),4) not in
(select no from @b)
end

insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity>=@quant
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by
quantity,len([NO])
end

-------语句调用-------
declare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output
select * from t where
charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0
j9988 2002-11-01
  • 打赏
  • 举报
 回复
上面少粘贴了一断。
create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant int,@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant

if exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)>right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
)
begin
print '1'
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
end

insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by quantity,len([NO])
end

语句这样就可以了！
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id output

select * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0
j9988 2002-11-01
  • 打赏
  • 举报
 回复
create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant int,@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant

if exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)>right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
)
begin
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
end


语句这样就可以了！
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id output

select * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0
j9988 2002-11-01
  • 打赏
  • 举报
 回复


语句这样就可以了！
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id output

select * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0
mnjrh 2002-11-01
  • 打赏
  • 举报
 回复
up
j9988 2002-11-01
  • 打赏
  • 举报
 回复
测试：
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')


set @type='b'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')

No Type Quantity
----------- ---------- -----------
2 A 20

No Type Quantity
----------- ---------- -----------
7 B 11
18 B 12


j9988 2002-11-01
  • 打赏
  • 举报
 回复
我也不知道当时怎么写的这程序！重写：

create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on

declare @quant int,@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)

insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant

if exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)>right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
)
begin
print '1'
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
end

insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by quantity,len([NO])
end

declare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')

j9988 2002-11-01
  • 打赏
  • 举报
 回复
太久了,我忘了,晚上我测试一下回复!
hillhx 2002-11-01
  • 打赏
  • 举报
 回复
j9988(j9988) 真酷
zhangshzh 2002-11-01
  • 打赏
  • 举报
 回复
to j9988(j9988)
存储过程好像还是有点问题，
当@type参数为'b'时输出的结果如下：

set @id1='' select top 1 @id1=''''+cast(a.no as varchar(10))+''''+','+''''+cast(b.no as varchar(10))+'''' from t a,t b where 1=1 and a.no<b.no and a.type=b.type and a.Quantity+b.Quantity=65 and a.type='b'
ji_hui 2002-09-17
  • 打赏
  • 举报
 回复
不明白2。
3是什么意思？
举个例子
hillhx 2002-09-17
  • 打赏
  • 举报
 回复
对了，如果你能确认每个类别的最大数据量也可以用一句SQL解出来，但占用资源太多了。
hillhx 2002-09-17
  • 打赏
  • 举报
 回复
不可能用一句SQL语句写出来，
如果利用存储过程还有点戏
remanwang 2002-09-17
  • 打赏
  • 举报
 回复
没写清楚查询条件
加载更多回复（5）
sql语句优化之SQL Server(优化方案大全)
MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4...
MS-SQL Server 基础类 - SQL语句
一个高难度的MS SQL问题请教各位高手！ 如何修改这个表 这一个查询条件可不可以进行优化使到只查询执行一次就可得出所有的数据？现table1有字段 id... 怎样查找统计相同名称的金额值，请帮我，谢谢。 现在我的企业...
SQL server 语句优化合集
MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4...
sql语句优化之SQL Server(详细整理)
MS SQL Server查询优化方法 查询速度慢的原因很多，常见如下几种 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致...
sqlserver 语句 sql 优化
MS SQL Server查询优化方法查询速度慢的原因很多，常见如下几种1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)2、I/O吞吐量小，形成了瓶颈效应。3、没有创建计算列导致查询不优化。4、内存...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章