求关于星座的SQL语句？？

[sunrenhao812]

id cDatetemp cXz

1 12/22-01/19 魔羯座
2 01/20-02/18 水瓶座
3 02/19-03/20 雙魚座

............

求一条跟据生日最快速获取星座的语句！

[sunrenhao812]

根据输入的生日最快速获取星座的语句

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,cDatetemp varchar(11),cXz varchar(6))
insert into #tb
select 1,'12/22-01/19','魔羯座' union all
select 2,'01/20-02/18','水瓶座' union all
select 3,'02/19-03/20','雙魚座'

declare @rq datetime

set @rq='20100203'

select * from #tb where @rq between ltrim(year(@rq))+'/'+left(cDatetemp,5) and ltrim(year(@rq))+'/'+right(cDatetemp,5)

id          cDatetemp   cXz
----------- ----------- ------
2           01/20-02/18 水瓶座

(1 行受影响)

[renzhe02]

把cDatetemp字段分解为日期1,日期2
当生日在 日期1与日期2 之间时(比较时只比较月和日.或者将日期1,日期2,生日三个日期的年设置为同一值,这样就直接比较三个日期),就是对应的星座

[renzhe02]

2楼速度真快

[claro]

深切哀悼舟曲泥石流遇难同胞！

[水族杰纶]

这个貌似牵扯到跨年的
要判断下

[SQLCenter]

select top 1

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, cDatetemp varchar(11), cXz varchar(8))
insert into #
select 1, '12/22-01/19', '魔羯座' union all
select 2, '01/20-02/18', '水瓶座' union all
select 3, '02/19-03/20', '雙魚座'

declare @ datetime
set @ = '20100203'
select top 1 cXz from # where right(convert(varchar,@,111),5) >= left(cDatetemp,5) order by cDatetemp
--水瓶座

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, cDatetemp varchar(11), cXz varchar(8))
insert into #
select 1, '12/22-01/19', '魔羯座' union all
select 2, '01/20-02/18', '水瓶座' union all
select 3, '02/19-03/20', '雙魚座'

declare @ datetime
set @ = '20100118'
if right(convert(varchar,@,111),5)<='01/19'
	select '魔羯座'
else 
	select top 1 cXz from # where right(convert(varchar,@,111),5) >= left(cDatetemp,5) order by cDatetemp
--魔羯座