table T1
city score
==============
cityA 0.8
cityB 0.3
cityC 0.5
cityD 0.0table T2
degree low high
==================
1 0.0 0.2
2 0.2 0.4
3 0.4 0.6
4 0.6 0.8
5 0.8 1.0得分区间为前闭后开 [0.0 ,0.2) [0.8 ,1.0]希望得到如下结果:
table T3
city degree
==============
cityA 5
cityB 2
cityC 3
cityD 1
city score
==============
cityA 0.8
cityB 0.3
cityC 0.5
cityD 0.0table T2
degree low high
==================
1 0.0 0.2
2 0.2 0.4
3 0.4 0.6
4 0.6 0.8
5 0.8 1.0得分区间为前闭后开 [0.0 ,0.2) [0.8 ,1.0]希望得到如下结果:
table T3
city degree
==============
cityA 5
cityB 2
cityC 3
cityD 1
from t1
join t2 on t1.score >= t2.low and t1.score < t2.high
city varchar(20),
score decimal(10,2)
)
insert tb1 select 'cityA', 0.8
insert tb1 select 'cityB' , 0.3
insert tb1 select 'cityC' , 0.5
insert tb1 select 'cityD' , 0.0 create table tb2(
degree int,
low decimal(10,2),
high decimal(10,2)
)
insert tb2 select 1, 0.0 , 0.2
insert tb2 select 2 , 0.2 , 0.4
insert tb2 select 3 , 0.4 , 0.6
insert tb2 select 4 , 0.6 , 0.8
insert tb2 select 5 , 0.8, 1.0 select a.city,b.degree
from tb1 a,tb2 b
where a.score>=b.low and a.score<b.high
order by a.citydrop table tb1,tb2/*
city degree
-------------------- -----------
cityA 5
cityB 2
cityC 3
cityD 1(所影响的行数为 4 行)*/
from T1 t1, T2 t2
where t1.score >= t2.low and t1.score < t2.high
table T2
degree low high
==============================
1 0.0 0.2
2 0.2 0.4
3 0.4 0.6
4 0.6 0.8
5 0.8 1.0
5 1.0 1.0 对不?佩服
(
city varchar(10),
score decimal(10,1)
)
insert into tb_t1
select 'cityA' , 0.8 union all
select 'cityB' , 0.3 union all
select 'cityC', 0.5 union all
select 'cityD', 0.0 create table tb_t2
(
degree varchar(10),
low decimal(10,1),
high decimal(10,1)
)
insert into tb_t2
select '1', 0.0 , 0.2 union all
select '2', 0.2 , 0.4 union all
select '3', 0.4 , 0.6 union all
select '4', 0.6 , 0.8 union all
select '5', 0.8 , 1.0 select city, degree
from tb_t1,tb_t2where tb_t1.score >= tb_t2.low
and tb_t1.score < tb_t2.highgroup by tb_t1.city,tb_t2.degree
order by tb_t1.city
/**
city degree
-------------
cityA 5
cityB 2
cityC 3
cityD 1**/
because 1>=1.0 and 1<1.0 should be falsebut
table T2
degree low high
==============================
1 0.0 0.2
2 0.2 0.4
3 0.4 0.6
4 0.6 0.8
5 0.8 1.0
5 1.0 1.0001will work:)
thanks a lot for all of your help.
drop table T1
if object_id('T2') is not null
drop table T2
if object_id('dbo.f_getDegree') is not null
drop function dbo.f_getDegree
go
create table T1(city varchar(10),score float)
create table T2(degree int,low float,high float)
insert T1
select 'cityA',0.8 union all
select 'cityB',0.3 union all
select 'cityC',0.5 union all
select 'cityD',0.0
insert T2
select 1,0.0,0.2 union all
select 2,0.2,0.4 union all
select 3,0.4,0.6 union all
select 4,0.6,0.8 union all
select 5,0.8,1.0
go
create function dbo.f_getDegree(@score float)
returns int
as
begin
declare @degree int
select @degree=degree from T2 where low<=@score and @score<high
if @score=1.0
set @degree=5 return @degree
endgo
select city,dbo.f_getDegree(score) as degree from T1
drop table T1
if object_id('T2') is not null
drop table T2
if object_id('dbo.f_getDegree') is not null
drop function dbo.f_getDegree
go
create table T1(city varchar(10),score float)
create table T2(degree int,low float,high float)
insert T1
select 'cityA',0.8 union all
select 'cityB',0.3 union all
select 'cityC',0.5 union all
select 'cityD',0.0
insert T2
select 1,0.0,0.2 union all
select 2,0.2,0.4 union all
select 3,0.4,0.6 union all
select 4,0.6,0.8 union all
select 5,0.8,1.0
go
create function dbo.f_getDegree(@score float)
returns int
as
begin
declare @degree int
select @degree=degree from T2 where low<=@score and @score<high
if @score=1.0
set @degree=5 return @degree
endgo
select city,dbo.f_getDegree(score) as degree from T1