SQL中的循环或递归还是使用Python

Question

假设我有下表：

D15

A	B	C
10	24	16
9	13	14	14
8	12	13	12
7	9	12	10
6	8	11	8
5	8	10	6

如果我理解正确，你可以很容易地通过取消抓取、枚举行和过滤来做到这一点：

select t.*
from (select cart, val,
row_number() over partition by cart order by val) as seqnum
from ((select 'A' as cart, A as val from t) union all
(select 'B' as cart, B from t) union all
(select 'C' as cart, C from t) union all
(select 'D' as cart, D from t)
) t
) t
where (cart = 'A' and seqnum <= 5) or
(cart = 'A' and seqnum <= 6) or
(cart = 'A' and seqnum <= 4) or
(cart = 'A' and seqnum <= 1)
order by val, cart;

注意：根据您使用的数据库，可能有更简单的方法来取消透视。

这应该有效：

with tab as (
select 'A' as cart, A as value from table
union
select 'B', B from table
union
select 'C', C from table
union
select 'D', D from table
)
select * from (
select tab.*, 
case 
when 
sum(case when cart = 'A' then 1 else 0 end) over(order by value) < 5
or sum(case when cart = 'A' then 1 else 0 end) over(order by value) = 5 and cart = 'A'
then 1 
else 0 
end as selected
from tab
) items
where items.selected = 1

如果您正在寻找Python解决方案，可以使用while循环：

from collections import deque
full_data = [{'a': 10, 'b': 14, 'c': 15, 'd': 16}, {'a': 9, 'b': 13, 'c': 14, 'd': 14}, {'a': 8, 'b': 12, 'c': 13, 'd': 12}, {'a': 7, 'b': 9, 'c': 12, 'd': 10}, {'a': 6, 'b': 8, 'c': 11, 'd': 8}, {'a': 5, 'b': 8, 'c': 10, 'd': 6}]
data = {i:deque(sorted([j[i] for j in full_data])) for i in ['a', 'b', 'c', 'd']}
s, m = [], {'a':5, 'b':6, 'c':4, 'd':1}
while m['a']:
a, _, _ = min([(a, b[0], i) for i, [a, b] in enumerate(data.items()) if m[a]], key=lambda x:(x[1], x[-1]))
s.append((a, data[a].popleft()))
m[a] -= 1

输出：

[('a', 5), ('a', 6), ('d', 6), ('a', 7), ('a', 8), ('b', 8), ('b', 8), ('a', 9)]