有表Table
Id MinVal MaxValue
1 100 150
2 200 300
3 250 350有查询区间
50—80 得到Count 为0
80—150 得到Count 为1
100—220 得到Count 为2
250—350 得到Count 为2
400—500 得到Count 为0
即表里的数据为区间
查询条件为区间的SQL语句
Id MinVal MaxValue
1 100 150
2 200 300
3 250 350有查询区间
50—80 得到Count 为0
80—150 得到Count 为1
100—220 得到Count 为2
250—350 得到Count 为2
400—500 得到Count 为0
即表里的数据为区间
查询条件为区间的SQL语句
解决方案 »
- 【求指导】后天去面试个好像搞财务审计的公司
- 求一正则表达式,有点难度:怎么样取出新闻内容里面的图片字符串。
- 我将sql server2005和2000都卸载了重装了sql 2005但是链接不了服务器啦!!还有什么方法可解决???
- 数据库分行问题,在线等
- 要将SQLServer的数据每天备份到另一台电脑的SQLServer,该怎样做?
- 请问:在结果中查询如何编写存储过程~
- 请问任何将某个表中一列的值在存储过程里自动执行
- 求一sql语句,有点难
- 请问:怎样将SQL SERVER数据库用ACCESS数据库那样随程序分发?
- sqlserver2008安装失败,求解啊!!!
- sql 累加问题
- 一个老问题,交叉查询的实现,急…… 先谢谢各位!
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-27 20:03:31
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[MinVal] int,[MaxValue] int)
insert [tb]
select 1,100,150 union all
select 2,200,300 union all
select 3,250,350
--------------开始查询--------------------------
--declare @s int
--set @s=
select
sum(case when MinVal between 50 and 80 then 1 else 0 end) as count1,
sum(case when MinVal between 81 and 150 then 1 else 0 end) as count2,
sum(case when MinVal between 100 and 220 then 1 else 0 end) as count3,
sum(case when MinVal between 250 and 350 then 1 else 0 end) as count4,
sum(case when MinVal between 400 and 500 then 1 else 0 end) as count5
from [tb]
----------------结果----------------------------
/* count1 count2 count3 count4 count5
----------- ----------- ----------- ----------- -----------
0 1 2 1 0(1 行受影响)
*/
函数
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(int int,MinVal int,MaxValue int)
go
insert into tb select
1 ,100, 150 union all select
2 ,200, 300 union all select
3 ,250, 350
go-----------------------------
if OBJECT_ID('dbo.GetCount') is not null
drop function dbo.GetCount
go
create function dbo.GetCount(@s int,@e int)
returns int
as
begin
declare @count as int
select @count=count(MinVal)
from tb
where MinVal>= @s And MinVal<@e
return @count
end
go
-------------------------------------
select count=dbo.GetCount(50,80)
union all
select dbo.GetCount(80,150)
union all
select dbo.GetCount(100,220)
union all
select dbo.GetCount(250,350)
union all
select dbo.GetCount(400,500)
/*
count
-----------
0
1
2
1
0(5 行受影响)*/
insert into a
select
1,100,150 union all select
2,200,300 union all select
3,250,350;select
sum(case when (MinVal>=50 and MinVal<=80) or (MaxVal>=50 and MaxVal<=80) then 1 else 0 end) as [50-80],
sum(case when (MinVal>=80 and MinVal<=150) or (MaxVal>=80 and MaxVal<=150) then 1 else 0 end) as [80-150],
sum(case when (MinVal>=100 and MinVal<=220) or (MaxVal>=100 and MaxVal<=220) then 1 else 0 end) as [100-220],
sum(case when (MinVal>=250 and MinVal<=350) or (MaxVal>=250 and MaxVal<=350) then 1 else 0 end) as [250-350],
sum(case when (MinVal>=400 and MinVal<=500) or (MaxVal>=400 and MaxVal<=500) then 1 else 0 end) as [400-500]
from a;
insert into tb values(1 , 100 , 150 )
insert into tb values(2 , 200 , 300 )
insert into tb values(3 , 250 , 350 )
gocreate proc my_proc @s1 int,@s2 int
as
begin
select count(distinct m.id) from tb m ,
(select val = @s1 + num from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where @s1 + num <= @s2) n
where n.val between m.MinVal and MaxValue
end
goexec my_proc 50 , 80
/*
-----------
0(所影响的行数为 1 行)
*/exec my_proc 80 , 150
/*
-----------
1(所影响的行数为 1 行)
*/exec my_proc 100 , 220
/*
-----------
1(所影响的行数为 1 行)
*/
exec my_proc 250 , 350
/*
-----------
2(所影响的行数为 1 行)
*/exec my_proc 400 , 500
/*
-----------
0(所影响的行数为 1 行)
*/drop table tb
drop proc my_proc
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[MinVal] int,[MaxValue] int)
insert [tb]
select 1,100,150 union all
select 2,200,300 union all
select 3,250,350
--------------开始查询--------------------------
--declare @s int
--set @s=
select
sum(case when MinVal between 50 and 80 or [MaxValue] between 50 and 80 then 1 else 0 end) as count1,
sum(case when MinVal between 81 and 150 or [MaxValue] between 81 and 150 then 1 else 0 end) as count2,
sum(case when MinVal between 100 and 220 or [MaxValue] between 100 and 220 then 1 else 0 end) as count3,
sum(case when MinVal between 250 and 350 or [MaxValue] between 250 and 350 then 1 else 0 end) as count4,
sum(case when MinVal between 400 and 500 or [MaxValue] between 400 and 500 then 1 else 0 end) as count5
from [tb]------------------------------------
count1 count2 count3 count4 count5
----------- ----------- ----------- ----------- -----------
0 1 2 2 0(1 行受影响)
drop table a;create table a(Id int, MinVal int, MaxVal int)
insert into a
select
1,100,150 union all select
2,200,300 union all select
3,250,350;select
sum(case when (MinVal>=50 and MinVal<=80) or (MaxVal>=50 and MaxVal<=80) then 1 else 0 end) as [50-80],
sum(case when (MinVal>=80 and MinVal<=150) or (MaxVal>=80 and MaxVal<=150) then 1 else 0 end) as [80-150],
sum(case when (MinVal>=100 and MinVal<=220) or (MaxVal>=100 and MaxVal<=220) then 1 else 0 end) as [100-220],
sum(case when (MinVal>=250 and MinVal<=350) or (MaxVal>=250 and MaxVal<=350) then 1 else 0 end) as [250-350],
sum(case when (MinVal>=400 and MinVal<=500) or (MaxVal>=400 and MaxVal<=500) then 1 else 0 end) as [400-500]
from a;
---------------------------------------------------------
0 1 2 2 0select '50-80' [区间], sum(case when (MinVal>=50 and MinVal<=80) or (MaxVal>=50 and MaxVal<=80) then 1 else 0 end) [记录数] from a union all
select '80-150', sum(case when (MinVal>=80 and MinVal<=150) or (MaxVal>=80 and MaxVal<=150) then 1 else 0 end) from a union all
select '100-220', sum(case when (MinVal>=100 and MinVal<=220) or (MaxVal>=100 and MaxVal<=220) then 1 else 0 end) from a union all
select '250-350', sum(case when (MinVal>=250 and MinVal<=350) or (MaxVal>=250 and MaxVal<=350) then 1 else 0 end) from a union all
select '400-500', sum(case when (MinVal>=400 and MinVal<=500) or (MaxVal>=400 and MaxVal<=500) then 1 else 0 end) from a;
--------------------------------------------
50-80 0
80-150 1
100-220 2
250-350 2
400-500 0
create table tb(Id int, MinVal int, MaxValue int)
insert into tb values(1 , 100 , 150 )
insert into tb values(2 , 200 , 300 )
insert into tb values(3 , 250 , 350 )
gocreate proc my_proc @s1 int,@s2 int
as
begin
select count(distinct m.id) from tb m ,
(select val = @s1 + t.number - 1 from master..spt_values t where t.type='p' AND t.number >0 and @s1 + t.number <= @s2) n
where n.val between m.MinVal and MaxValue
end
goexec my_proc 50 , 80
/*
-----------
0(所影响的行数为 1 行)
*/exec my_proc 80 , 150
/*
-----------
1(所影响的行数为 1 行)
*/exec my_proc 100 , 220
/*
-----------
2(所影响的行数为 1 行)
*/
exec my_proc 250 , 350
/*
-----------
2(所影响的行数为 1 行)
*/exec my_proc 400 , 500
/*
-----------
0(所影响的行数为 1 行)
*/drop table tb
drop proc my_proc