语无伦次!SIGNATURE:----------------------------------------------------------------------------------------
解决方案 »
- SQL如何把查询出来的记录保留整数
- 两个表数据相加 先谢谢
- 发生死锁时,在锁/进程ID中,正在阻塞的进程中为什么对象名与索引不是同一表?多谢
- 修改列~~~
- 如何查看SQL SERVER里的日子文件,*.LDF
- 在线求助,一条语句!!!
- 有关SQL SERVER的安装问题~~我想安装客户/服务端的数据库~~可是有问题~~请大家帮忙
- 如何修改数据表某列的数据的时候不用字段名
- 怎样把在Views 中把table 1与table 2建立成同一表
- 请问SQL Server 2008 Express Edition(免费版)有用户和并发访问限制吗?
- ■■求sql的对列的统计■■ (带年份的)
- ■■求sql的对列的统计■■
go
create table tb(id int)
insert tb
select 1 union all
select 2 union all
select 3 union all
select 2
create table #t(num int identity (1,1),id int)
insert into #t select * from tb
select a.id ,
case a.id when (select id from #t where num in (select max(num)from #t)) then 0
else
(
select count(*) from #t b
where b.id != a.id
and b.num >=a.num+1
and b.num <= (select max(num)from #t)
)
end
as 遗漏次数
from #t a--结果:
id 遗漏次数
1 3
2 0
3 1
2 0
use tempdb
go
create table tb(id int)
insert tb
select 1 union all
select 2 union all
select 3 union all
select 2
create table #t(num int identity (1,1),id int)
insert into #t select * from tb
select a.id ,
case a.id when (select id from #t where num in (select max(num)from #t)) then 0
else
(
select count(*) from #t b
where b.id != a.id
and b.num >=a.num+1
and b.num <= (select max(num)from #t)
)
end
as 遗漏次数
from #t a--结果:
id 遗漏次数
1 3
2 0
3 1
2 0
use tempdb
go
create table tb(id int)
insert tb
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 3 union all
select 2 union all
select 4
declare @t table(num int identity (1,1),id int)
insert into @t select * from tb
select
a.id ,
case a.id when (select id from @t where num in (select max(num)from @t)) then 0
else
(
select count(*) from @t b
where b.id != a.id
and b.num >=a.num+1
and b.num <= (select max(num)from @t)
)
end
as 遗漏次数
from (
select max(num) as num ,id from @t group by id
) as a--
1 6
2 1
3 2
4 0
5 3