select * from test a join (select number,max(sign) as sign from test group by number) as b on a.number=b.number and a.sign=b.sign-1SQL SERVER语法,MYSQL应该类似
select * from t1 where (number, sign) in (select number, if(count(*)>1,max(sign)-1,max(sign)) m from t1 group by number ) order by sign结果为: 2,1,test2,2 (这里id应为2吧?) 4,2,test4,1 5,3,test5,1
上面贴的应该一样可以用,如果你的数据很严格,当然可以简化一下,如果你的sign在同一个number的情况下一定是1到n且连续的话,只要使用 select * from t1 where (number, sign) in ( select number, if(count(*)>1,count(*)-1,1) m from t1 group by number ) order by sign
用union吧,( select *, 0 flag from t1 where (number, sign) in ( select number,1 m from t1 group by number having count(*)=1 )) union ( select *, 1 flag from t1 where (number, sign) in ( select number,count(*)-1 m from t1 group by number having count(*)>1 ) ) order by sign
join
(select number,max(sign) as sign from test group by number) as b
on a.number=b.number and a.sign=b.sign-1SQL SERVER语法,MYSQL应该类似
例如我要取出来的记录为:
1,1,test2,2
4,2,test4,1
5,3,test5,1---------------------------------有点矛盾,4,5两条不是sign为最大数减1
你的sql办能取出number重复的一条记录,4,5的取不出来
where (number, sign) in
(select number, if(count(*)>1,max(sign)-1,max(sign)) m
from t1
group by number )
order by sign结果为:
2,1,test2,2 (这里id应为2吧?)
4,2,test4,1
5,3,test5,1
首先条件是相同number为多个时,sign才会累加1,如果是单条记录的话,sign值就是1
也就是说number为唯一记录时,sign值都是1,不用再找最大的sign,只有当number为多个时,才会找最大的sign来减一,这样两个条件来查出number为多个时的记录最后的sql要查询出所有的符合条件的记录:number为唯一时的所有记录,还有number有多个时且sign为最大值减1的记录
你的sql可以取出来,但是还有一个问题就是取出来的记录没法知道哪条记录是经过sign减一后所取出来的记录,最好能把减1后的sign记录as成一个新的值(比如减1的赋值为1,没减的赋为0),这样就可以方便标示出来了
select * from t1
where (number, sign) in
(
select number, if(count(*)>1,count(*)-1,1) m
from t1
group by number
)
order by sign
就像我给出记录中的number为1的记录就是sign减1后得出来,能再加一个字段来标识吗
select *, 0 flag from t1
where (number, sign) in
(
select number,1 m
from t1
group by number having count(*)=1
))
union
(
select *, 1 flag from t1
where (number, sign) in
(
select number,count(*)-1 m
from t1
group by number having count(*)>1
)
)
order by sign