sql联合查询排序的有关问题

sql联合查询排序的问题
内容表
id addtime content title add_user
1 2006-07-02 内容1 标题1 admin
2 2006-08-02 内容2 标题2 master
3 2006-09-02 内容3 标题3 aaa
4 2006-10-02 内容4 标题4 admin


收藏表
did id d_addtime downuser
1 3 2006-11-02 admin
2 3 2006-11-02 master
[code=SQL][/code]


下载表

cid id c_addtime shouuser
1 2 2006-11-01 admin
2 1 2006-11-05 master

SQL code

SELECT * FROM (内容表 n LEFT JOIN 下载表 x ON n.id=x.id) LEFT JOIN 收藏表 c ON c.id=n.id WHERE (n.add_user='admin' OR x.downuser='admin' OR c.shouuser='admin')ORDER BY n.addtime DESC,x.d_addtime DESC,c.c_addtime DESC

执行以上SQL现在得到的结果是
id addtime add_user did d_addtime downuser cid c_addtime shouuser
1 2006-07-02 admin - - - - - -
4 2006-10-02 admin - - - - - -
3 2006-09-02 aaa 1 2006-11-02 admin - - -  
2 2006-08-02 master - - - 1 2006-11-01 admin




以上数据就是得到的结果，但排序不理想想得到这样的结果

id addtime add_user did d_addtime downuser cid c_addtime shouuser
3 2006-09-02 aaa 1 2006-11-02 admin - - -  
2 2006-08-02 master - - - 1 2006-11-01 admin
4 2006-10-02 admin - - - - - -
1 2006-07-02 admin - - - - - -

如何修改SQL

[解决办法]
SELECT * FROM (内容表 n LEFT JOIN 下载表 x ON n.id=x.id) LEFT JOIN 收藏表 c ON c.id=n.id 
WHERE (n.add_user='admin' OR x.downuser='admin' OR c.shouuser='admin')
ORDER BY 
case when ( n.addtime is not null and x.d_addtime is not null and c.c_addtime is not null ) 
then 1.0
when (n.addtime is not null and x.d_addtime is not null and c.c_addtime is null ) then 1.1
when (n.addtime is not null and x.d_addtime is null and c.c_addtime is not null ) then 1.2
when (n.addtime is null and x.d_addtime is not null and c.c_addtime is not null ) then 1.2
when (n.addtime is not null and x.d_addtime is null and c.c_addtime is null ) then 1.3
when (n.addtime is null and x.d_addtime is not null and c.c_addtime is null ) then 1.4
when (n.addtime is null and x.d_addtime is null and c.c_addtime is not null ) then 1.5
else 9 end
[解决办法]

SQL code

if object_id('tempdb..#内容表') is not null    drop table #内容表select * into #内容表 from(select '1' as id,'2006-07-02' as addtime,'内容1' as content,'标题1' as title,'admin' as add_user union allselect '2','2006-08-02','内容2','标题2','master' union allselect '3','2006-09-02','内容3','标题3','aaa' union allselect '4','2006-10-02','内容4','标题4','admin')tbif object_id('tempdb..#下载表') is not null    drop table #下载表select * into #下载表 from(select '1' as did,'3' as id,'2006-11-02' as d_addtime,'admin' as downuser union allselect '2','3','2006-11-02','master')tbif object_id('tempdb..#收藏表') is not null    drop table #收藏表select * into #收藏表 from(select '1' as cid,'2' as id,'2006-11-01' as c_addtime,'admin' as shouuser union allselect '2','1','2006-11-05','master')tbSELECT * FROM #内容表 n LEFT JOIN #下载表 x ON n.id=x.idLEFT JOIN #收藏表 c ON c.id=n.id WHERE (n.add_user='admin' OR x.downuser='admin' OR c.shouuser='admin')ORDER BY case when isnull(c.c_addtime,'1900-1-1')>    case when isnull(n.addtime,'1900-1-1') >isnull(x.d_addtime,'1900-1-1')    then isnull(n.addtime,'1900-1-1') else isnull(x.d_addtime,'1900-1-1') endthen    isnull(c.c_addtime,'1900-1-1')else    case when isnull(n.addtime,'1900-1-1') >isnull(x.d_addtime,'1900-1-1')    then isnull(n.addtime,'1900-1-1') else isnull(x.d_addtime,'1900-1-1') endend desc 

 

查看更多 下一篇