查询问题，求帮助！

[jz54007]

这是数据表


想要的查询结果是

num字段和值等于5的 N条数据（随机） type字段等于0
请问这样的SQL语句应该怎么写？

[zjcxc]

select *
from tb a,(
select
	case when num1 <= 5 then id1 end as id1,
	case when num2 <= 5 then id2 end as id2,
	case when num3 <= 5 then id3 end as id3,
	case when num4 <= 5 then id4 end as id4,
	case when num5 <= 5 then id5 end as id5
from(
    select t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5,
    	t1.num as num1,
    	t1.num + t2.num as num2,
    	t1.num + t2.num + t3.num as num3,
    	t1.num + t2.num + t3.num + t4.num as num4,
    	t1.num + t2.num + t3.num + t4.num + t5.num as num5
    from tb t1, tb t2, tb t3, tb t4, tb t5
    where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id
)x where 5 in (num1, num2, num3, num4, num5 )
) b where a.id in (b.id1, b.id2, b.id3, b.id4, b.id5)

[zjcxc]

select *
from tb a,(
select distinct -- 某些组合达到 num 和 = 5 所需要的 id 一样，所以这里 DISTINCT 一下
	case when num1 <= 5 then id1 end as id1,
	case when num2 <= 5 then id2 end as id2,
	case when num3 <= 5 then id3 end as id3,
	case when num4 <= 5 then id4 end as id4,
	case when num5 <= 5 then id5 end as id5
from(
    select t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5,
    	t1.num as num1,
    	t1.num + t2.num as num2,
    	t1.num + t2.num + t3.num as num3,
    	t1.num + t2.num + t3.num + t4.num as num4,
    	t1.num + t2.num + t3.num + t4.num + t5.num as num5
    from tb t1, tb t2, tb t3, tb t4, tb t5
    where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id
)x where 5 in (num1, num2, num3, num4, num5 )
) b where a.id in (b.id1, b.id2, b.id3, b.id4, b.id5)

[zjcxc]

内层忘记了 type=0 的条件，改成下面这个就行 where t1.id < t2.id and t2.id < t3.id and t3.id < t4.id and t4.id < t5.id and 0 in (t1.type, t2.type, t3.type, t4.type, t5.type)

[ACMAIN_CHM]

在M个数据中取N和数之和为S，这个是刚开的算法问题，大学的算法教材上有。用SQL语句实现代价过大。 select id,num, null,null, null,null, null,null, null,null from table1 where num=5 union all select t1.id,t1.num, t2.id,t2.num, null,null, null,null, null,null from table1 t1 inner join table1 t2 on t1.id<t2.id where t1.num+t2.num =5 union all select t1.id,t1.num, t2.id,t2.num, t3.id,t3.num , null,null, null,null from table1 t1 inner join table1 t2 on t1.id<t2.id inner join table1 t3 on t2.id<t3.id where t1.num+t2.num+t3.num =5 union all select t1.id,t1.num, t2.id,t2.num, t3.id,t3.num ,t4.id,t4.num , null,null from table1 t1 inner join table1 t2 on t1.id<t2.id inner join table1 t3 on t2.id<t3.id inner join table1 t4 on t3.id<t4.id where t1.num+t2.num+t3.num+t4.num =5 union all select t1.id,t1.num, t2.id,t2.num, t3.id,t3.num ,t4.id,t4.num , t5.id,t5.num from table1 t1 inner join table1 t2 on t1.id<t2.id inner join table1 t3 on t2.id<t3.id inner join table1 t4 on t3.id<t4.id inner join table1 t5 on t4.id<t5.id where t1.num+t2.num+t3.num+t4.num+t5.num =5

[jz54007]

引用 1 楼 zjcxc 的回复:

select *
from tb a, (
	select t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5
	from tb t1, tb t2, tb t3, tb t4, tb t5
	where t1.num + t2.num + t3.num + t4.num + t5.num = 5
)b where a.id in (b.id1, b.id2. b.id3, b.id4, b.id5)

运行结果不对

[jz54007]

我要的结果是！


比如
NUM相加值等于5 的有 ID1+ID2+ID8=5 或者 ID2+ID3=5 再或者 ID4+ID1=5
任意选出一组 显示他们的ID就可以了

[jz54007]

纠正一下 上面第一个是 ID1+ID2+ID6=5 补充一下 一个ID 只能在公式中出现一次 不可以是 ID1+ID1+ID1+ID1+ID1=5 这样的结果

[zjcxc]

select *
from tb a, (
	select t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5
	from tb t1, tb t2, tb t3, tb t4, tb t5
	where t1.num + t2.num + t3.num + t4.num + t5.num = 5
)b where a.id in (b.id1, b.id2. b.id3, b.id4, b.id5)

[jz54007]

我测了测 这样做数据量大的时候对数据库负担太大，以后怕是有隐患。我还是修改库结构吧。 非常感谢 zjcxc 的帮助！