我有一个用户表
id name tag(推荐为 1 不推荐为0 ) tjtime ( 推荐时间)我想得到这样的结果
前面是所有推荐的用户按照 推荐时间 desc 排序
接着是显示没有推荐的用户按照 ID 的desc 排序????????????????????在线等待!!!!!!
id name tag(推荐为 1 不推荐为0 ) tjtime ( 推荐时间)我想得到这样的结果
前面是所有推荐的用户按照 推荐时间 desc 排序
接着是显示没有推荐的用户按照 ID 的desc 排序????????????????????在线等待!!!!!!
解决方案 »
- 向数据库添加记录时,字段信息里有些关键字,最好应如何处理,才不会出错?
- sqlserver存储过程怎么返回结果集?
- 请大家不吝赐教一个sql,谢谢!
- pl/sql
- 我的这个表为何访问会很慢,表结构很简单啊,就两个字段.
- 简单问题:我已经建了表和主键,如何使用企业管理器设定外键?
- sql导入文本文件
- 高手请指教,这条sql语句如何优化? --在线等待
- function里不能調用自己寫的store procedure?也不能建temp table,高手進??
- 请问在VFP中,怎么去验证输入的字段有没有全角的标点符号,如果有,请问如何把它转换为半角
- 本来很快的一个查询忽然变得很慢会是什么原因?
- 急求一个 null值有关的SQL语句
order by tag desc,(case when tag=1 then 0 else ID end) desc,
tjtime desc
order by tag,case when tag=1 then tjtime
else convert(char(10),id) end
desc
order by tag desc,
(case when tag=1 then tjtime else null end) desc,
(case when tag=1 then null else id end) desc
注意:0并不是最小的id
order by tag desc,(case when (tag=1) and (Datediff(day,tjtime,getdate())=0)
then -1 else Abs(ID) end) desc, tjtime desc
from [user]
where tag=1
order by tjtime desc
union all
select *
from [user]
where tag=0
order by id desc
insert into @t
select 1,'n1',0,'1980-11-1' union all
select 2,'n2',1,'1980-12-1' union all
select 3,'n3',0,'1980-3-1' union all
select 4,'n4',1,'1980-4-1' union all
select 5,'n5',1,'1980-5-1' union all
select 6,'n6',0,'1980-6-1' union all
select 7,'n7',0,'1980-7-1' union all
select 8,'n8',1,'1980-8-1' union all
select 9,'n9',1,'1980-9-1'
select * from @t
order by tag desc,(case when tag=1 then tjtime else id end) descid name tag tjtime
----------- -------------------- ----------- ------------------------------------------------------
2 n2 1 1980-12-01 00:00:00.000
9 n9 1 1980-09-01 00:00:00.000
8 n8 1 1980-08-01 00:00:00.000
5 n5 1 1980-05-01 00:00:00.000
4 n4 1 1980-04-01 00:00:00.000
7 n7 0 1980-07-01 00:00:00.000
6 n6 0 1980-06-01 00:00:00.000
3 n3 0 1980-03-01 00:00:00.000
1 n1 0 1980-11-01 00:00:00.000(所影响的行数为 9 行)楼上说的很对:union不能有两个Order by
改正了下,并提供了测试结果。
order by case when tag=1 and datediff(day,tjtime,getdate())<1 then 0 else tag end desc,
(case when tag=1 then tjtime else null end) desc,
(case when tag=1 then null else id end) desc
order by case when tag=1 and datediff(day,tjtime,getdate())<1 then 0 else tag end desc,
(case when tag=1 and datediff(day,tjtime,getdate())>=1 then tjtime else null end) desc,
(case when tag=1 and datediff(day,tjtime,getdate())>=1 then null else id end) desc
id int,
name varchar(20),
tag bit,
tjtime datetime
)insert @t
select 1,'1',0,'2006-8-21'
union all
select 82,'82',0,'2006-8-17'
union all
select 3,'3',0,'2006-8-15'
union all
select 4,'4',0,'2006-8-19'
union all
select 5,'5',0,'2006-8-20'
union all
select 6,'6',0,'2006-8-18'
union all
select 11,'11',1,'2006-8-22'
union all
select 12,'12',1,'2006-8-19'
union all
select 13,'13',1,'2006-8-18'
union all
select 14,'14',1,'2006-8-17'
union all
select 15,'15',1,'2006-8-20'
union all
select 16,'16',1,'2006-8-21'
union all
select 97,'97',1,'2006-8-10'
union all
select 107,'107',0,'2006-8-10'
Select * from @t
order by case when tag=1 and datediff(day,tjtime,getdate())>-1 then 0 else tag end desc,
(case when tag=1 and datediff(day,tjtime,getdate())<=-1 then tjtime else null end) desc,
(case when tag=1 and datediff(day,tjtime,getdate())<=-1 then null else id end) desc--结果
id name tag tjtime
----------- -------------------- ---- ------------------------------------------------------
11 11 1 2006-08-22 00:00:00.000
16 16 1 2006-08-21 00:00:00.000
15 15 1 2006-08-20 00:00:00.000
107 107 0 2006-08-10 00:00:00.000
97 97 1 2006-08-10 00:00:00.000
82 82 0 2006-08-17 00:00:00.000
14 14 1 2006-08-17 00:00:00.000
13 13 1 2006-08-18 00:00:00.000
12 12 1 2006-08-19 00:00:00.000
6 6 0 2006-08-18 00:00:00.000
5 5 0 2006-08-20 00:00:00.000
4 4 0 2006-08-19 00:00:00.000
3 3 0 2006-08-15 00:00:00.000
1 1 0 2006-08-21 00:00:00.000(所影响的行数为 14 行)
declare @t table(
id int,
name varchar(20),
tag bit,
tjtime datetime
)insert @t
select 1,'1',0,'2006-8-21'
union all
select 82,'82',0,'2006-8-17'
union all
select 3,'3',0,'2006-8-15'
union all
select 4,'4',0,'2006-8-19'
union all
select 5,'5',0,'2006-8-20'
union all
select 6,'6',0,'2006-8-18'
union all
select 11,'11',1,'2006-8-22'
union all
select 12,'12',1,'2006-8-19'
union all
select 13,'13',1,'2006-8-18'
union all
select 14,'14',1,'2006-8-17'
union all
select 15,'15',1,'2006-8-20'
union all
select 16,'16',1,'2006-8-21'
union all
select 97,'97',1,'2006-8-10'
union all
select 107,'107',0,'2006-8-10'
Select * from @t
order by case when tag=1 and datediff(day,tjtime,getdate())>0 then 0 else tag end desc,
(case when tag=1 and datediff(day,tjtime,getdate())<=0 then tjtime else null end) desc,
(case when tag=1 and datediff(day,tjtime,getdate())<=0 then null else id end) desc--结果
id name tag tjtime
----------- -------------------- ---- ------------------------------------------------------
11 11 1 2006-08-22 00:00:00.000
16 16 1 2006-08-21 00:00:00.000
15 15 1 2006-08-20 00:00:00.000
12 12 1 2006-08-19 00:00:00.000
107 107 0 2006-08-10 00:00:00.000
97 97 1 2006-08-10 00:00:00.000
82 82 0 2006-08-17 00:00:00.000
14 14 1 2006-08-17 00:00:00.000
13 13 1 2006-08-18 00:00:00.000
6 6 0 2006-08-18 00:00:00.000
5 5 0 2006-08-20 00:00:00.000
4 4 0 2006-08-19 00:00:00.000
3 3 0 2006-08-15 00:00:00.000
1 1 0 2006-08-21 00:00:00.000(所影响的行数为 14 行)