select a.num,b.num,c.num,d.num,e.num from table1 a,table1 b,table1 c,table1 d,table1 e where a.num+b.num+c.num+d.num+e.num=10 and a.num >=b.num and b.num >=c.num and c.num >=d.num and d.num >=e.num
YANG_ 会不会只去一条NUM=2的记录?
笨办法: declare @sum int select @sum=0 select * into #temp from tab where 1=2 while @sum<>10 begin truncate table #temp insert into #temp select top 5 * from tab order by newid() select @sum=sum(num) from #temp end select * from #temp drop table #temp go
truncate: 删除表中的所有行,而不记录单个行删除操作。
to caiyunxia(monkey) 可能哦! 那应该加设有个idselect x.* from table1 x, ( select a.id as aid,b.id as bid,c.id as cid,d.id as did,e.id as eid from table1 a,table1 b,table1 c,table1 d,table1 e where a.num+b.num+c.num+d.num+e.num=10 and a.id <b.id and b.id <c.id and c.id <d.id and d.id < e.id ) as y where x.id =y.aid or x.id =y.bid or x.id =y.cid or x.id =y.did or x.id =y.eid
测试过程: create table table1(id int,num int)insert table1 values(1,1) insert table1 values(2,5) insert table1 values(3,6) insert table1 values(4,3) insert table1 values(5,8) insert table1 values(6,2) insert table1 values(7,9) insert table1 values(8,10) insert table1 values(9,4) insert table1 values(10,1) insert table1 values(11,3) insert table1 values(12,2) insert table1 values(13,7)select x.* from table1 x, ( select top 1 a.id as aid,b.id as bid,c.id as cid,d.id as did,e.id as eid from table1 a,table1 b,table1 c,table1 d,table1 e where a.num+b.num+c.num+d.num+e.num=10 and a.id <b.id and b.id <c.id and c.id <d.id and d.id < e.id ) as y where x.id =y.aid or x.id =y.bid or x.id =y.cid or x.id =y.did or x.id =y.eid结果: id num 1 1 1 1 4 3 6 2 10 1 11 3
from table1 a,table1 b,table1 c,table1 d,table1 e
where a.num+b.num+c.num+d.num+e.num=10
and a.num >=b.num
and b.num >=c.num
and c.num >=d.num
and d.num >=e.num
会不会只去一条NUM=2的记录?
declare @sum int
select @sum=0
select * into #temp from tab where 1=2
while @sum<>10
begin
truncate table #temp
insert into #temp select top 5 * from tab order by newid()
select @sum=sum(num) from #temp
end
select * from #temp
drop table #temp
go
可能哦!
那应该加设有个idselect x.* from table1 x,
(
select a.id as aid,b.id as bid,c.id as cid,d.id as did,e.id as eid
from table1 a,table1 b,table1 c,table1 d,table1 e
where a.num+b.num+c.num+d.num+e.num=10
and a.id <b.id
and b.id <c.id
and c.id <d.id
and d.id < e.id
) as y
where x.id =y.aid
or x.id =y.bid
or x.id =y.cid
or x.id =y.did
or x.id =y.eid
create table table1(id int,num int)insert table1 values(1,1)
insert table1 values(2,5)
insert table1 values(3,6)
insert table1 values(4,3)
insert table1 values(5,8)
insert table1 values(6,2)
insert table1 values(7,9)
insert table1 values(8,10)
insert table1 values(9,4)
insert table1 values(10,1)
insert table1 values(11,3)
insert table1 values(12,2)
insert table1 values(13,7)select x.* from table1 x,
(
select top 1 a.id as aid,b.id as bid,c.id as cid,d.id as did,e.id as eid
from table1 a,table1 b,table1 c,table1 d,table1 e
where a.num+b.num+c.num+d.num+e.num=10
and a.id <b.id
and b.id <c.id
and c.id <d.id
and d.id < e.id
) as y
where x.id =y.aid
or x.id =y.bid
or x.id =y.cid
or x.id =y.did
or x.id =y.eid结果:
id num
1 1
1 1
4 3
6 2
10 1
11 3
id num
1 1
4 3
6 2
10 1
11 3