SQL排序分组

[肥胖的柠檬]

按id与他们的money数相加后不超过他们的maxmoney数,生成新一个排序.
(我也想不出怎样才可以最优地先选择那几条数据在一起).
数据如下:
-----------------------------------
[id],[money],[maxmoney]
1 20 80
1 70 200
1 60 150
2 80 100
2 20 90
2 30 40
2 70 100
3 70 200
3 90 200
4 100 200
4 20 300
4 60 250
-----------------------------结果如下(也可以是别的组合,结果几个money数相加后不超过他们的maxmoney数即可,下面是从上到下的结果)
----------------------------------------------------------
[id],[money],[maxmoney] , [cid] | 或[cid]
1 20 80 1 | 1
1 70 200 2 | 2
1 60 150 1 | 1
2 80 100 1 | 3
2 20 90 2 | 4
2 30 40 3 | 5
2 70 100 2 | 4
3 70 200 1 | 6
3 90 200 1 | 6
4 100 200 1 | 7
4 20 300 1 | 7
4 60 250 1 | 7

[肥胖的柠檬]

这是多张张订单生成一张发票的,要求订单组合后的总金额不能超过他们本身的最大生成发票金额(maxmoney)数
------------
就用这个说, 1 20 80 生成cid 1

那以那一条 记录做为基准 得到的数据都是不一样的是马?
------------
这是我以第一条为准,你也可以按[id]的以最小,小大money 数为准.(不是重点,按你所想的就可以了,只要按[id]几个money数相加后不超过他们的maxmoney数即可)让他们分开就可以了

[tx1icenhe]

20+70>max(80,200) ?
90=20+70>max(80,200) =200 ?

[tx1icenhe]

2 70 100 2 | 4怎么回事？

[肥胖的柠檬]

就用这个说, 1 20 80 生成cid 1
20+70>min(80,200) 第2条记录跳过, 生成cid 2
20+60<=min(80,150) 第3条记录可以和1合并 生成cid 1
不好意思....打错

[tx1icenhe]

楼主表述问题,其实要求最后结果：

同一id，同一cid的,sum([money])<=min([maxmoney]) （第一组cid）
或者
同一cid，必须同一id,而且同一cid的sum([money])<=min([maxmoney]) （第二组cid）


是个分配问题

[肥胖的柠檬]

....死了,没人理了~

[xiaoku]

drop table t
create table T
(
id int ,
money int ,
maxmoney int
)

insert into T
select 1 , 20 , 80 union all
select 1 , 70 , 200 union all
select 1 , 60 , 150 union all
select 2 , 80 , 100 union all
select 2 , 20 , 90 union all
select 2 , 30 , 40 union all
select 2 , 70 , 100 union all
select 3 , 70 , 200 union all
select 3 , 90 , 200 union all
select 4 , 100 , 200 union all
select 4 , 20 , 300 union all
select 4 , 60 , 250

---这里假定 money 列有唯一最小值
---搞了好久,也犯了好多错误哦
select * ,
(
select case
when (b.money + c.money ) <= (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 1
when (b.money + c.money ) > (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end )
and (case when b.money >=c.money then b.money else c.money end )<=(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 2
when (case when b.money >=c.money then b.money else c.money end )>(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 3 end
--,b.money ,c.money ,b.maxmoney ,c.maxmoney
from t b
join (select * from t tt where not exists (select 1 from t where id =tt.id and money > tt.money ) ) c on b.id =c.id

where b.id =a.id and b.money =a.money and b.maxmoney =a.maxmoney
) as cid
from t a


id money maxmoney cid
----------- ----------- ----------- -----------
1 20 80 2
1 70 200 1
1 60 150 1
2 80 100 2
2 20 90 2
2 30 40 3
2 70 100 2
3 70 200 1
3 90 200 1
4 100 200 1
4 20 300 1
4 60 250 1

（所影响的行数为 12 行）

[xiaoku]

如果 把你的数据 插入一个临时表的话,哪就好办多了...

再思考...

[xiaoku]

--这个适用 有重复的
select *, identity(int,1,1) IID into New_t
from t


select id,money,maxmoney ,
(select case
when (b.money + c.money ) <= (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 1
when (b.money + c.money ) > (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end )
and (case when b.money >=c.money then b.money else c.money end )<=(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 2
when (case when b.money >=c.money then b.money else c.money end )>(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 3 end
from new_t b
join
(
select * from new_t a
where not exists (select 1 from new_t where id = a.id and iid <a.iid )
) c on b.id =c.id
where b.id = t.id and b.iid =t.iid
) as cid
from new_t t

id money maxmoney cid
----------- ----------- ----------- -----------
1 20 80 1
1 70 200 2
1 60 150 1
2 80 100 2
2 20 90 2
2 30 40 3
2 70 100 2
3 70 200 1
3 90 200 1
4 100 200 1
4 20 300 1
4 60 250 1

（所影响的行数为 12 行）

[肥胖的柠檬]

---------------------------
2 80 100 2
2 20 90 2
2 30 40 3
2 70 100 2
---------------------------
这里不对哦~
2 80 100 2 --
2 20 90 2 --
2 30 40 3
2 70 100 2 --
这3条不能放一起哦,超过了他们的最大金额了,不过还是谢谢了~放入一个临时表也没关系,我一开始也是放在一个临时表这样做的

[xiaoku]

--改一下

select id,money,maxmoney ,
(select case
when b.iid =c.iid then 1 --加个条件
when (b.money + c.money ) <= (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 1
when (b.money + c.money ) > (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end )
and (case when b.money >=c.money then b.money else c.money end )<=(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 2
when (case when b.money >=c.money then b.money else c.money end )>(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 3 end
from new_t b
join
(
select * from new_t a
where not exists (select 1 from new_t where id = a.id and iid <a.iid )
) c on b.id =c.id
where b.id = t.id and b.iid =t.iid
) as cid
from new_t t

id money maxmoney cid
----------- ----------- ----------- -----------
1 20 80 1
1 70 200 2
1 60 150 1
2 80 100 1
2 20 90 2
2 30 40 3
2 70 100 2
3 70 200 1
3 90 200 1
4 100 200 1
4 20 300 1
4 60 250 1

（所影响的行数为 12 行）

[xiaoku]

再改一下:

select id,money,maxmoney ,
(select case
when (b.money + c.money ) <= (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) or b.iid =c.iid then 1 --把条件加来这里
when (b.money + c.money ) > (case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end )
and (case when b.money >=c.money then b.money else c.money end )<=(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 2
when (case when b.money >=c.money then b.money else c.money end )>(case when b.maxmoney >=c.maxmoney then c.maxmoney else b.maxmoney end ) then 3 end
from new_t b
join
(
select * from new_t a
where not exists (select 1 from new_t where id = a.id and iid <a.iid )
) c on b.id =c.id
where b.id = t.id and b.iid =t.iid
) as cid
from new_t t

[肥胖的柠檬]

哈哈,比我的快好多..3Q3Q~

[肥胖的柠檬]

想给分的,不过给了就结贴了,还过一会就给分...3Qxiaoku(野蛮人(^v^))

[xiaoku]

呵呵...

贴你的出来看看阿?

[hhhdyj]

CID只有1，2，3？
如果在加入这么一条数据 2 10 20，对应的cid是不是应该是4？

[hhhdyj]

还有如果在加一条2 10 40，问题就更大了

[中国风]

declare @ta table
(
id int ,
money int ,
maxmoney int
)

insert into @ta
select 1 , 20 , 80 union all
select 1 , 70 , 200 union all
select 1 , 60 , 150 union all
select 2 , 80 , 100 union all
select 2 , 20 , 90 union all
select 2 , 30 , 40 union all
select 2 , 70 , 100 union all
select 3 , 70 , 200 union all
select 3 , 90 , 200 union all
select 4 , 100 , 200 union all
select 4 , 20 , 300 union all
select 4 , 60 , 250

select * from @ta
order by id,case when id+money!>maxmoney then id+money
else id end

（所影响的行数为 12 行）

id money maxmoney
----------- ----------- -----------
1 20 80
1 60 150
1 70 200
2 20 90
2 30 40
2 70 100
2 80 100
3 70 200
3 90 200
4 20 300
4 60 250
4 100 200

（所影响的行数为 12 行）

[中国风]

以上排序方式为:
id,case when id+money!>maxmoney then id+money
else id end
首先按id排序，次序id+money不大于maxmoney时根据大小排序，大于时按id排序

[肥胖的柠檬]

Top
hhhdyj(萤火虫) ( ) 信誉：100 Blog 2006-11-26 7:33:13 得分: 0

CID只有1，2，3？
如果在加入这么一条数据 2 10 20，对应的cid是不是应该是4？

是的.
如果可能还有5呢,我在想用上面的变成动态的SQL.
大家还有没有好的方法啊