解决方案 »
- 谁帮我看看,这句sql语句什么意思,急用
- 请问:可以通过程序对托管的SQL中的存储过程进行修改吗?
- SQL Server2000,数据修改后,修改后的数据全部消失
- 求助!关于数据库压缩问题上。
- 五角啦,给各位兄弟姐妹们散点分,呵呵~
- SQL代理问题
- 一个sql 查询
- 我把SQL数据库移植成access数据库后,触发器该如何移植?
- 兄弟们,帮帮忙,急求答案,不胜感激!!! QQ:346185051
- SELECT * FROM a,b,c,d 结果如何排序
- 求一句sql 关于交叉统计
- 进行还原SQL SERVER 数据库时,总是提示“数据库正在使用所以未能获得对数据库的排它访问权, restore database 操作异常终止”
select top 999999999 * from test where tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt<=30 order by tjt desc) t1
union all
select * from(
select top 999999999 * from test where (tj=0 or (tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt>30)) order by id desc) t2
declare @t1 table(id int,tj int,tjt datetime)
insert into @t1
select * from test where tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt<=30 order by tjt desc
declare @t2 table(id int,tj int,tjt datetime)
insert into @t2
select * from test where (tj=0 or (tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt>30)) order by id desc
select * from @t1
union all
select * from @t2id tj tjt
9 1 2010-05-25 00:00:00.000
5 1 2010-05-22 00:00:00.000
7 1 2010-05-21 00:00:00.000
8 0 NULL
6 0 NULL
4 0 NULL
3 1 2010-03-21 00:00:00.000
2 0 NULL
1 0 NULL
select * from test
order by case when tj=1 and datetime(dd,tjt,getdate())<30 then tjt else null end desc,id desc
from
(
select *,aa=1 from test where tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt<=30
union all
select *aa=2 from test where (tj=0 or (tj=1 and CONVERT(varchar(100), GETDATE(), 23)-tjt>30))
) t
order by aa,tjt desc,id desc
DROP TABLE test
GO
CREATE TABLE test
(
ID INT IDENTITY(1,1) NOT NULL,
TJ INTEGER,
TJT DATETIME
)
INSERT test SELECT 0,NULL
INSERT test SELECT 0,NULL
INSERT test SELECT 1,'2010-3-21'
INSERT test SELECT 0,NULL
INSERT test SELECT 1,'2010-5-22'
INSERT test SELECT 0,NULL
INSERT test SELECT 1,'2010-5-21'
INSERT test SELECT 0,NULL
INSERT test SELECT 1,'2010-5-25'SELECT *
FROM test
ORDER BY CASE
WHEN tj=1 AND DATEDIFF(dd, tjt, GETDATE())<30 THEN tjt
END DESC,id DESC
/*
ID TJ TJT
----------- ----------- -----------------------
9 1 2010-05-25 00:00:00.000
5 1 2010-05-22 00:00:00.000
7 1 2010-05-21 00:00:00.000
8 0 NULL
6 0 NULL
4 0 NULL
3 1 2010-03-21 00:00:00.000
2 0 NULL
1 0 NULL(9 行受影响)
*/