第一条记录,a=0,b=33,表示从0到33的数字,就是0,1,2,3...30,31,32,33我想找最优的SQL语句。
解决方案 »
- 檢查約束問題
- for xml auto问题,想把查询结果保存在变量@res 中
- OpenDataSource或者OPENROWSET 读取TXT文件,如何分开字段
- 删除最新的一条记录!!!!!
- sql server附加
- 这个问题怎么解决???在线等
- SQL7 如何实现类似Format(date,"mm-dd-yyyy")的函数
- 很容易得分的问题,帮我检查一个语句的语法,在线等待!(SQL SERVER 7.0查询分析器中的一个语句)
- 极难问题。关于数据表转换。求aql 。。高手in
- 一个dataserver的update 的问题
- 在Windows 2003下,sql server 2005总出现调试窗口,为什么?如何调试?谢谢!
- (最后30分!)如何存放电影院座位
DECLARE @Temp TABLE(a int, b int)
insert into @Temp (a,b) values (0,33)
insert into @Temp (a,b) values (16,46)
insert into @Temp (a,b) values (0,17)
insert into @Temp (a,b) values (0,21)
insert into @Temp (a,b) values (0,30)
insert into @Temp (a,b) values (30,67)
insert into @Temp (a,b) values (29,89)
insert into @Temp (a,b) values (63,89) select top 1 * from
(
select number,cout =count(1)
from @Temp a,master.dbo.spt_values b
where a.a<=b.number and a.b>=b.number and b.type='P'
group by number
) a
order by cout desc/*
number cout
----------- -----------
16 5(1 行受影响)*/
到这里看看有没有你的解决方案,这里搜集了大乌龟,诌老大等大牛们的很多sql语句
DECLARE @Temp TABLE(a int, b int)
insert into @Temp (a,b) values (0,33)
insert into @Temp (a,b) values (16,46)
insert into @Temp (a,b) values (0,17)
insert into @Temp (a,b) values (0,21)
insert into @Temp (a,b) values (0,30)
insert into @Temp (a,b) values (30,67)
insert into @Temp (a,b) values (29,89)
insert into @Temp (a,b) values (63,89) select * from
(
select number,cout =count(1)
from @Temp a,master.dbo.spt_values b
where a.a<=b.number and a.b>=b.number and b.type='P'
group by number
) a where cout=(select max(cout) from
(
select number,cout =count(1)
from @Temp a,master.dbo.spt_values b
where a.a<=b.number and a.b>=b.number and b.type='P'
group by number
) a
)
/*
number cout
----------- -----------
30 5
16 5
17 5(3 行受影响)
*/
--a表示开始数字,b表示结束数字,如第一条记录表示从0到33的数字
insert into @Temp (a,b) values (0,33)
insert into @Temp (a,b) values (16,46)
insert into @Temp (a,b) values (0,17)
insert into @Temp (a,b) values (0,21)
insert into @Temp (a,b) values (0,30)
insert into @Temp (a,b) values (30,67)
insert into @Temp (a,b) values (29,89)
insert into @Temp (a,b) values (63,89) select b.number,cnt=count(1)
from @Temp a
join master..spt_values b
on b.type='p' and b.number between a.a and a.b
group by b.number
having count(1)=(
select top 1 count(1) cnt
from @Temp a
join master..spt_values b
on b.type='p' and b.number between a.a and a.b
group by b.number
order by cnt desc)
/*
number cnt
----------- -----------
16 5
17 5
30 5(3 行受影响)
*/
select top 1000 number=identity(int,1,1) into # from sysobjects a,syscolumns b
--a表示开始数字,b表示结束数字,如第一条记录表示从到的数字,获取插入的这些数据段中,那个数字被重叠时最多的
insert into @Temp (a,b) values (0,33)
insert into @Temp (a,b) values (16,46)
insert into @Temp (a,b) values (0,17)
insert into @Temp (a,b) values (0,21)
insert into @Temp (a,b) values (0,30)
insert into @Temp (a,b) values (30,67)
insert into @Temp (a,b) values (29,89)
insert into @Temp (a,b) values (63,89) declare @min int,@max int,@jieguo int,@jieguo2 int,@jieguo3 int,@cheshi int
select @min=min(a),@max=max(b) from @Temp
select @min,@maxset @cheshi=@min set @jieguo2=0
while(@cheshi>=@min and @cheshi<=@max)
begin
select @jieguo=count(*) from @Temp where (a<=@cheshi and b>=@cheshi)
if(@jieguo>=@jieguo2)
begin
set @jieguo2=@jieguo set @jieguo3=@cheshi
end
set @cheshi=@cheshi+1
if(@cheshi>@max)
begin
break
end
end
select @jieguo2 as 重叠次数,@jieguo3 as 数字
DECLARE @Temp TABLE(a int, b int)
--a表示开始数字,b表示结束数字,如第一条记录表示从0到33的数字
insert into @Temp (a,b) values (0,33)
insert into @Temp (a,b) values (16,46)
insert into @Temp (a,b) values (0,17)
insert into @Temp (a,b) values (0,21)
insert into @Temp (a,b) values (0,30)
insert into @Temp (a,b) values (30,67)
insert into @Temp (a,b) values (29,89)
insert into @Temp (a,b) values (63,89) select top 1 with ties b.number,cnt=count(1)
from @Temp a
join master..spt_values b
on b.type='p' and b.number between a.a and a.b
group by b.number
order by cnt desc
/*
number cnt
----------- -----------
30 5
16 5
17 5(3 行受影响)
*/