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是就取表最后一条数据。