＊＊＊＊＊＊SQL 语句排序问题

OceanRain 2009-05-30 01:29:40

A表(注意时间)
id name ispic createtime
1 中国 1 2009-01-03
2 上海 1 2009-02-03
3 西安 0 2008-03-04
4 河南 0 2009-05-23
5 台湾 0 2009-04-02
6 琉球 1 2007-02-02

B表(记录A表记录的刷新时间表,注:AB两表关系是一对多)
aid refresh
3 2009-05-24
6 2009-05-29
6 2009-06-01

要求：实现A表中的数据先按B表的刷新时间排序，再按A表的creatime时间排列

id name ispic createtime
6 琉球 1 2007-02-02 --因为刷新时间最新为2009-06-01
3 西安 0 2008-03-04
4 河南 0 2009-05-23
5 台湾 0 2009-04-02
2 上海 1 2009-02-03
1 中国 1 2009-01-03

...全文

187 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

ks_reny 2009-05-30

打赏
举报



SELECT *

FROM ta  A

ORDER BY 

ISNULL((SELECT MAX(refresh) FROM tb  WHERE aid=A.id),createtime) DESC

SQL77 2009-05-30

打赏
举报



DECLARE @tb1 TABLE (id INT,name VARCHAR(4),ispic INT,createtime DATETIME)

INSERT INTO @tb1

SELECT 1,'中国',1,'2009-01-03' UNION ALL

SELECT 2,'上海',1,'2009-02-03' UNION ALL

SELECT 3,'西安',0,'2008-03-04' UNION ALL

SELECT 4,'河南',0,'2009-05-23' UNION ALL

SELECT 5,'台湾',0,'2009-04-02' UNION ALL

SELECT 6,'琉球',1,'2007-02-02'

 

--> 生成测试数据: @tb2

DECLARE @tb2 TABLE (aid INT,refresh DATETIME)

INSERT INTO @tb2

SELECT 3,'2009-05-24' UNION ALL

SELECT 6,'2009-05-29' UNION ALL

SELECT 6,'2009-06-01'



SELECT * FROM 

(SELECT T.ID,T.NAME,T.ISPIC,ISNULL(T1.REFRESH,T.CREATETIME)CREATETIME 

FROM @TB1 T LEFT JOIN (SELECT AID,MAX(REFRESH)REFRESH FROM @TB2 GROUP BY AID) T1 ON T.ID=T1.AID)AS T2

 ORDER BY CREATETIME DESC

（所影响的行数为 3 行）

ID NAME ISPIC CREATETIME
----------- ---- ----------- ------------------------------------------------------
6 琉球 1 2009-06-01 00:00:00.000
3 西安 0 2009-05-24 00:00:00.000
4 河南 0 2009-05-23 00:00:00.000
5 台湾 0 2009-04-02 00:00:00.000
2 上海 1 2009-02-03 00:00:00.000
1 中国 1 2009-01-03 00:00:00.000

（所影响的行数为 6 行）

JonasFeng 2009-05-30

打赏
举报

这样也是OK的。



--> 创建测试数据: LI1

IF OBJECT_ID('LI1') IS NOT NULL

   DROP TABLE LI1 

IF OBJECT_ID('LI2') IS NOT NULL

   DROP TABLE LI2 



CREATE TABLE LI1 (id INT,name VARCHAR(4),ispic INT,createtime DATETIME)

INSERT INTO LI1

SELECT 1,'中国',1,'2009-01-03' UNION ALL

SELECT 2,'上海',1,'2009-02-03' UNION ALL

SELECT 3,'西安',0,'2008-03-04' UNION ALL

SELECT 4,'河南',0,'2009-05-23' UNION ALL

SELECT 5,'台湾',0,'2009-04-02' UNION ALL

SELECT 6,'琉球',1,'2007-02-02'

 

--> 创建测试数据: LI2 

CREATE TABLE LI2 (aid INT,refresh DATETIME)

INSERT INTO LI2 

SELECT 3,'2009-05-24' UNION ALL

SELECT 6,'2009-05-29' UNION ALL

SELECT 6,'2009-06-01'



--SQL查询如下:



SELECT *

FROM LI1 T

ORDER BY ISNULL((SELECT MAX(refresh) FROM LI2 WHERE aid=T.id),createtime) DESC 



/×

id      name    ispic              createdate

6	琉球	1	2007-02-02 00:00:00.000

3	西安	0	2008-03-04 00:00:00.000

4	河南	0	2009-05-23 00:00:00.000

5	台湾	0	2009-04-02 00:00:00.000

2	上海	1	2009-02-03 00:00:00.000

1	中国	1	2009-01-03 00:00:00.000

×/

rucypli 2009-05-30

打赏
举报

select A.id,A.name,A.ispic,A.createtime

from A left join (

select aid,max(refresh) as refresh 

from B

group by aid

)T on A.id = B.id

order by B.refresh desc,A.createtime

--小F-- 2009-05-30

打赏
举报

SELECT *

FROM A表 AS A

ORDER BY 

   ISNULL((SELECT MAX(refresh) FROM B表 WHERE aid=A.id),createtime) DESC

you_tube 2009-05-30

打赏
举报

用下楼上数据

--> 生成测试数据;@tb1 

DECLARE @tb1 TABLE (id INT,name VARCHAR(4),ispic INT,createtime DATETIME)

INSERT INTO @tb1

SELECT 1,'中国',1,'2009-01-03' UNION ALL

SELECT 2,'上海',1,'2009-02-03' UNION ALL

SELECT 3,'西安',0,'2008-03-04' UNION ALL

SELECT 4,'河南',0,'2009-05-23' UNION ALL

SELECT 5,'台湾',0,'2009-04-02' UNION ALL

SELECT 6,'琉球',1,'2007-02-02'

 

--> 生成测试数据: @tb2

DECLARE @tb2 TABLE (id INT,refresh DATETIME)

INSERT INTO @tb2

SELECT 3,'2009-05-24' UNION ALL

SELECT 6,'2009-05-29' UNION ALL

SELECT 6,'2009-06-01'



-->Test

select TabA.* 

from 

@tb1 TabA left join (select id,max(refresh ) refresh  FROM @tb2 TabB group by id) TabB 

on TabA.id = TabB.id

order by 

case when TabB.refresh is not null then TabB.refresh else TabA.createtime end 



--Result

/*

id          name ispic       createtime

----------- ---- ----------- -----------------------

1           中国   1           2009-01-03 00:00:00.000

2           上海   1           2009-02-03 00:00:00.000

5           台湾   0           2009-04-02 00:00:00.000

4           河南   0           2009-05-23 00:00:00.000

3           西安   0           2008-03-04 00:00:00.000

6           琉球   1           2007-02-02 00:00:00.000



(6 行受影响)

*/

ws_hgo 2009-05-30

打赏
举报

create TABLE #tb(id INT,name VARCHAR(4),ispic INT,createtime DATETIME)

INSERT INTO #tb

SELECT 1,'中国',1,'2009-01-03' UNION ALL

SELECT 2,'上海',1,'2009-02-03' UNION ALL

SELECT 3,'西安',0,'2008-03-04' UNION ALL

SELECT 4,'河南',0,'2009-05-23' UNION ALL

SELECT 5,'台湾',0,'2009-04-02' UNION ALL

SELECT 6,'琉球',1,'2007-02-02'

 

create TABLE #tb2(aid INT,refresh DATETIME)

INSERT INTO #tb2

SELECT 3,'2009-05-24' UNION ALL

SELECT 6,'2009-05-29' UNION ALL

SELECT 6,'2009-06-01'



select b.*,isnull(b2.refresh,b.createtime) refresh from #tb b full join (select max(refresh) refresh,aid from #tb2 group by aid) b2

on b2.aid=b.id order by refresh desc,b.createtime



id          name ispic       createtime              refresh

----------- ---- ----------- ----------------------- -----------------------

6           琉球   1           2007-02-02 00:00:00.000 2009-06-01 00:00:00.000

3           西安   0           2008-03-04 00:00:00.000 2009-05-24 00:00:00.000

4           河南   0           2009-05-23 00:00:00.000 2009-05-23 00:00:00.000

5           台湾   0           2009-04-02 00:00:00.000 2009-04-02 00:00:00.000

2           上海   1           2009-02-03 00:00:00.000 2009-02-03 00:00:00.000

1           中国   1           2009-01-03 00:00:00.000 2009-01-03 00:00:00.000



(6 行受影响)

you_tube 2009-05-30

打赏
举报

select TabA.* 

from 

TabA left join (select id,max(refresh ) refresh  from TabB group by id) TabB 

on TabA.id = TabB.id

order by 

case when TabB.refresh is not null then TabB.refresh else TabA.createtime end

liangCK 2009-05-30

打赏
举报

1楼漏了个ISNULL,,不好意思.

liangCK 2009-05-30

打赏
举报

-------------------------------------------

--  Author : liangCK 小梁 & angellan 兰儿

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-05-30 13:32:53

-------------------------------------------

 

--> 生成测试数据: @tb1

DECLARE @tb1 TABLE (id INT,name VARCHAR(4),ispic INT,createtime DATETIME)

INSERT INTO @tb1

SELECT 1,'中国',1,'2009-01-03' UNION ALL

SELECT 2,'上海',1,'2009-02-03' UNION ALL

SELECT 3,'西安',0,'2008-03-04' UNION ALL

SELECT 4,'河南',0,'2009-05-23' UNION ALL

SELECT 5,'台湾',0,'2009-04-02' UNION ALL

SELECT 6,'琉球',1,'2007-02-02'

 

--> 生成测试数据: @tb2

DECLARE @tb2 TABLE (aid INT,refresh DATETIME)

INSERT INTO @tb2

SELECT 3,'2009-05-24' UNION ALL

SELECT 6,'2009-05-29' UNION ALL

SELECT 6,'2009-06-01'



--SQL查询如下:



SELECT *

FROM @tb1 AS A

ORDER BY 

   ISNULL((SELECT MAX(refresh) FROM @tb2 WHERE aid=A.id),createtime) DESC;



/*

id          name ispic       createtime

----------- ---- ----------- -----------------------

6           琉球   1           2007-02-02 00:00:00.000

3           西安   0           2008-03-04 00:00:00.000

4           河南   0           2009-05-23 00:00:00.000

5           台湾   0           2009-04-02 00:00:00.000

2           上海   1           2009-02-03 00:00:00.000

1           中国   1           2009-01-03 00:00:00.000



(6 row(s) affected)



*/

liangCK 2009-05-30

打赏
举报

SELECT *

FROM tbA AS A

ORDER BY 

   ((SELECT MAX(refresh) FROM tbB WHERE aid=A.id),createtime) DESC;

这是一套体系化的SQL语句教程，按照从浅入深的模式讲解工作中常用的SQL写法、函数、字段处理技术等。使大家快速掌握SQL的基础及部分高阶知识，进而熟练的对数据库表、字段进行各种查询、创建、更新、删除操作。 &...

sql语句时间排序 sql语句按照时间排序以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！SQL按时间排序select * from MyTable Order By ModifyTime Desc...

SQL语句排序与分页

数据库字段排序的SQL语句数据库字段排序的SQL语句2009-09-17将字段依次写在order by 后面即可 , 中间用逗号隔开。select * from 表 order by time , nameselect * from 表 order by time asc , name ascselect * ...

一、SQL语句实现单字段降序、升序 select a.* from a order by a.id;默认情况下，一般是升序；关键字升序是asc，降序为desc；二、SQL语句实现多字段降序select a.* from a order by a.id desc,a.num desc;三、SQL...

疑难问题

22,207

社区成员

121,730

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章