sql 题目
1.自增列
通用:
select id=(select count(1) from table b where b.sid<a.sid) ,* from table a;
select id=identity(int,1,1),* from ...
第二个已经有主键自增列的就不可以用了
还有就是rownumber
2.
CREATE TABLE dbo.#testTab ( Id int NOT NULL )
添加数据:
insert into #testTab values(3); insert into #testTab values(4); insert into #testTab values(7); insert into #testTab values(10); insert into #testTab values(11); insert into #testTab values(19); insert into #testTab values(20); insert into #testTab values(21); insert into #testTab values(26); insert into #testTab values(27);
select s2.id+1,s3.id-1 from ( select * from ( select Row_Number() over (order by id) as init,t4.Id,t4.UpId,t4.NextId from ( select t1.Id, t2.Id as UpId,t3.id as NextId from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null) as t4 ) as s1 where (s1.init < ( select COUNT(1) from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null ) and s1.UpId is null) or (s1.UpId is null and s1.NextId is null) ) as s2 left join (select Row_Number() over (order by id) as init,t4.Id,t4.UpId,t4.NextId from ( select t1.Id, t2.Id as UpId,t3.id as NextId from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null) as t4) s3 on s2.init = s3.init-1 where s3.Id is not null
其实就是查出id列排序后数据之间的间隔数据的最小与最大值
分析:
//1 t4 select t1.Id,t2.Id as UpId,t3.id as NextId from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null
//2 s1 select Row_Number() over (order by id) as init,t4.Id,t4.UpId,t4.NextId from ( select t1.Id,t2.Id as UpId,t3.id as NextId from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null)as t4
//3 s2 //获取间隔数据中小的 select * from ( select Row_Number() over (order by id) as init,t4.Id,t4.UpId,t4.NextId from ( select t1.Id,t2.Id as UpId,t3.id as NextId from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null) as t4 ) as s1 where (s1.init < ( select COUNT(1) from #testTab as t1 left join #testTab as t2 on t1.Id = (t2.Id-1) left join #testTab as t3 on t1.Id = (t3.Id+1) where t2.Id is null or t3.Id is null ) and s1.UpId is null) or (s1.UpId is null and s1.NextId is null)
这一步是最重要的,它的思路是:
先找出间隔中比较小的那端
s1.init<(.......)and s1.UpId is null 是为了这里的话就是26,27,剔除不是间隔的数据
s1.UpId is null and s1.NextId is null 是为了如果这里26,27只有一位的话,它其实是不能被剔除的,它是间隔的数据
最后就是通过间隔中较小的数据的init+1来查找间隔的较大数据,其实s3就是s1,左连进就可以了。
3.sql%余数
要整除时就要最后一个数字,就是不能为0
类似其他的Mod
select case 2%count(1) when 0 then count(1) else 2%count(1) end from Alliance_B2BContacter where IsShow='T'
这个里面就是一个随机数(这里是2)整除表数量后的序号,不能为0,当为0是就取表最后一条数据。