--> 生成测试数据: @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)*/
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
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.createtimeid 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 行受影响)
用下楼上数据 --> 生成测试数据;@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 行受影响) */
SELECT * FROM A表 AS A ORDER BY ISNULL((SELECT MAX(refresh) FROM B表 WHERE aid=A.id),createtime) DESC
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
这样也是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 ×/
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 行)
SELECT * FROM ta A ORDER BY ISNULL((SELECT MAX(refresh) FROM tb WHERE aid=A.id),createtime) DESC
-- 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)*/
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
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.createtimeid 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 行受影响)
--> 生成测试数据;@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 行受影响)
*/
FROM A表 AS A
ORDER BY
ISNULL((SELECT MAX(refresh) FROM B表 WHERE aid=A.id),createtime) DESC
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
--> 创建测试数据: 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
×/
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 行)
SELECT *
FROM ta A
ORDER BY
ISNULL((SELECT MAX(refresh) FROM tb WHERE aid=A.id),createtime) DESC