select * from user_table
where (datediff(yy,u_birthday,getdate())>=60) or (datediff(yy,u_birthday,getdate())>=50 and u_sex='女' and u_sort='是') or (datediff(yy,u_birthday,getdate())>=55 and u_sex='女' and u_sort='否')
where (datediff(yy,u_birthday,getdate())>=60) or (datediff(yy,u_birthday,getdate())>=50 and u_sex='女' and u_sort='是') or (datediff(yy,u_birthday,getdate())>=55 and u_sex='女' and u_sort='否')
where datediff(year,u_birthday,@你给的日期参数)
=case when (u_sort='干部' and U_sex='男') then 60
when (u_sort='干部' and u_sex='女') then 50
when (u_sort='工人' and u_sex='男') then 60
when (u_sort='工人' and u_sex='女') then 55 end
FROM user_table
WHERE (u_sort = '干部'
AND ((CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR,u_birthday,21), 1, 4)) - CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR,timenow,21), 1, 4)))
) <= 60 AND u_sex = '男')
AND
(u_sort = '干部' AND (u_birthday - timenew) <= 50 AND u_sex = '女')
AND
(u_sort = '工人' AND (u_birthday - timenew) <= 50 AND u_sex = '男')
AND
(u_sort = '工人' AND (u_birthday - timenew) <= 55 AND u_sex = '女')将u_birthday - timenew 替换为
(CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR,u_birthday,21), 1, 4)) - CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR,timenew ,21), 1, 4)))我写的好麻烦,还要好好学习啊
一楼的方法很好
二楼的我还没有试,我来学习
create table user_table
(u_in int identity,
u_name nvarchar(4),
u_sex nvarchar(2),
u_sort nvarchar(2),
u_birthday smalldatetime)
go
insert user_table(u_name,u_sex,u_sort,u_birthday)
select '张三','男','是','1943/02/06' union all
select '李四','男','否','1949/07/11' union all
select '张红','女','是','1950/11/26' union all
select '李红','女','否','1941/09/06' union all
select '王红','女','否','1959/09/06'
go
--建立存储过程~~~用时候方便。。
create proc ccc
@time datetime
as
select * from user_table where datediff(year,u_birthday,@time) >= 60 and u_sex ='男' union all
select * from user_table where datediff(year,u_birthday,@time) >= 50 and u_sex = '女' and u_sort = '是' union all
select * from user_table where datediff(year,u_birthday,@time) >= 55 and u_sex = '女' and u_sort = '否'
go
--测试存储过程!如果觉得我插入数据不能验证效果你可以truncate table重插入
exec ccc '2005/05/13'
(u_in int identity,
u_name nvarchar(4),
u_sex nvarchar(2),
u_sort nvarchar(2),
u_birthday smalldatetime)
go
insert user_table(u_name,u_sex,u_sort,u_birthday)
select '张三','男','是','1940/02/06' union all
select '李四','男','否','1949/07/11' union all
select '张红','女','是','1950/11/26' union all
select '李红','女','否','1941/09/06' union all
select '王红','女','否','1959/09/06'
select * from user_table
where datediff(year,u_birthday,getdate())
>=case when (u_sort='是' and U_sex='男') then 60
when (u_sort='是' and u_sex='女') then 50
when (u_sort='否' and u_sex='男') then 60
when (u_sort='否' and u_sex='女') then 55 end
drop table user_tableu_in u_name u_sex u_sort u_birthday
----------- ------ ----- ------ ------------------------------------------------------
1 张三 男 是 1940-02-06 00:00:00
3 张红 女 是 1950-11-26 00:00:00
4 李红 女 否 1941-09-06 00:00:00(所影响的行数为 3 行)
借了数据要还的哈哈
哪天我也借你的用用
要是把数据改一下!!create table user_table
(u_in int identity,
u_name nvarchar(4),
u_sex nvarchar(2),
u_sort nvarchar(2),
u_birthday smalldatetime)
go
insert user_table(u_name,u_sex,u_sort,u_birthday)
select '张三','男','是','1943/02/06' union all
select '李四','男','否','1949/07/11' union all
select '张红','女','是','1950/11/26' union all
select '李红','女','否','1950/12/31' union all
select '王红','女','否','1959/09/06'
go
select * from user_table
where datediff(year,u_birthday,getdate())
>=case when (u_sort='是' and U_sex='男') then 60
when (u_sort='是' and u_sex='女') then 50
when (u_sort='否' and u_sex='男') then 60
when (u_sort='否' and u_sex='女') then 55 end--结果:
1 张三 男 是 1943-02-06 00:00:00
3 张红 女 是 1950-11-26 00:00:00
4 李红 女 否 1950-12-31 00:00:00
应该没有李红 才对 啊!!
她要到 2005-12-31 号才有55 啊!!
datediff 来判断年龄有误区啊!!
借了数据同时也接程序改一下哇哈哈
select * from user_table
where case when datediff(dd,dateadd(yy,datediff(yy,u_birthday,getdate()),u_birthday),getdate())>0 then datediff(yy,u_birthday,getdate()) else datediff(yy,u_birthday,getdate())-1 end
>=case when (u_sort='是' and U_sex='男') then 60
when (u_sort='是' and u_sex='女') then 50
when (u_sort='否' and u_sex='男') then 60
when (u_sort='否' and u_sex='女') then 55 end
(case when getdate()-u_birtehday>=50 then '是' else '否' end) when u_sort='工人' then (case when u_sex='男' then (case when getdate()-u_birtehday>=60 then '是' else '否' end) when u_sort='女' then (case when getdate()-u_birtehday>=55 then '是' else '否' end) end,u_birthday from user_table
呵呵!不用客气了!我还应该感谢你给我一个好问题啊!:)to: scmail81(freedom)兄哈哈!你不要放高利贷啊!:)