SELECT TOP 1* FROM WEBINFO ORDER BY Img_AddTime DESC
UNION
SELECT * FROM WEBINFO ORDER BY ID DESC这样写是错误的我的目地是 把 img_time 最大的 那条记录拿出来再 后再合并上其它的记录 但 order by 好像只能 给最终的结果排序
该怎么样解决
UNION
SELECT * FROM WEBINFO ORDER BY ID DESC这样写是错误的我的目地是 把 img_time 最大的 那条记录拿出来再 后再合并上其它的记录 但 order by 好像只能 给最终的结果排序
该怎么样解决
(SELECT TOP 1* FROM WEBINFO where Img_AddTime= (select max(Img_AddTime) from FROM WEBINFO)
UNION SELECT * FROM WEBINFO)
ORDER BY ID DESC
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-05 21:28:26
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
--------------开始查询--------------------------
select * from tb order by case when col=(select max(col) from tb) then 0 else 1 end,col
----------------结果----------------------------
/*col
-----------
6
1
2
3
4
5(6 行受影响)
*/
where img_addtime=(select max(img_addtime) from webinfo)
union all select * from webinfo) t
order by id desc
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
[ID] int,
[no] int
)
go
--插入测试数据
insert into tb select 1,1
union all select 2,3
union all select 3,6
union all select 4,5
union all select 5,9
union all select 6,10
go
--代码实现select * from(select * from [tb]
where [no]=(select max([no]) from [tb])
union all select * from [tb]) t
order by [id] desc/*
ID no
------------
6 10
6 10
5 9
4 5
3 6
2 3
1 1(7 行受影响)
*/
UNION
SELECT * FROM WEBINFO ORDER BY ID DESC
UNION ALL子句只能在最后才能排序,而且只按上面的列排序,请用子查询实现
(SELECT TOP 1* FROM WEBINFO ORDER BY Img_AddTime DESC)t
UNION all
select * from(
SELECT top 999999999999999999 * FROM WEBINFO ORDER BY ID DESC)t/*
id img_addtime
----------- ------------------------------------------------------
1 2010-05-05 21:47:32.527
7 2010-05-05 21:44:44.333
6 2010-05-05 21:44:42.820
5 2010-05-05 21:44:42.190
4 2010-05-05 21:44:41.480
3 2010-05-05 21:44:40.750
2 2010-05-05 21:44:40.027
1 2010-05-05 21:47:32.527(所影响的行数为 8 行)
*/
ORDER BY CASE WHEN Img_AddTime=(SELECT MAX(Img_AddTime ) FROM TB) THEN 0 ELSE 1 END ,ID DESC
(SELECT TOP 1* FROM WEBINFO ORDER BY Img_AddTime DESC)t
UNION all
select * from(
SELECT top 100 percent * FROM WEBINFO ORDER BY ID DESC)t
这个应该也可以
--改为
select *
(SELECT TOP 1* FROM WEBINFO ORDER BY Img_AddTime DESC) as a
UNION
SELECT * FROM WEBINFO ORDER BY ID DESC