有一个新闻系统,某一个类的新闻,要求有一条新闻永远置顶,就是第一个语句的,督办情况表(这个名字不会有重复的),这条新闻。然后查出来的其他的就是按时间倒排序的5条新闻(其中categoryid是新闻分类的ID)。这2条单独执行,都能得到我想要的结果。可是联合查询后,第一条“督办情况表“表在最上面,没问题。可下面的5条不对了,不是最新的5条。SELECT *
FROM bbs
WHERE title like "*督办情况表*"
UNION ALL SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC;是access的库,请问是怎么回事啊?谢谢!
FROM bbs
WHERE title like "*督办情况表*"
UNION ALL SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC;是access的库,请问是怎么回事啊?谢谢!
SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC; 这个和单独执行这句,取出来的5条记录不一样
WHERE title like "*督办情况表*"
UNION ALL SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC;
WHERE title like "%督办情况表%"
FROM bbs
WHERE title like "*督办情况表*"
UNION ALL select * from (SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC ) tt;
WHERE title like "*督办情况表*" Order by posttime DESC
UNION ALL SELECT TOP 5 *,0 as istop
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC;以上是一个视图的话,Select ...... ORder by istop DESC,posttime DESC
FROM bbs
WHERE title like "*督办情况表*"
UNION ALL select * from (SELECT TOP 5 *
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC ) tt;给你说这样 就行
(SELECT top 1 *,1 as istop FROM bbs WHERE title like '*督办情况表*' Order by posttime DESC) a
UNION ALL
select * from
(SELECT TOP 5 *,0 as istop FROM bbs WHERE categoryid=7 ORDER BY posttime DESC) b
先union all以后,最后会对所有数据重新排序
FROM bbs
WHERE categoryid=7
ORDER BY posttime DESC 保存到tt中 就是一个临时表的名称 可以随便更换
(
[ID] int identity(1,1) primary key,
[categoryid] int,
title nvarchar(50),
posttime datetime
)
insert into tb(categoryid,title,posttime)
select 7,'AAA',GETDATE() union all
select 7,'BBB',dateadd(D,-2,GETDATE()) union all
select 7,'CCC',dateadd(D,-1,GETDATE()) union all
select 7,'DDD',dateadd(D,-1,GETDATE()) union all
select 7,'EEE',dateadd(MI,-1,GETDATE()) union all
select 7,'FFF',GETDATE() union all
select 7,'督办情况表',dateadd(HH,-1,GETDATE())Select top 5 case when CHARINDEX('督办情况表',title,0) > 0 then 1 else 0 end as [redu]
,categoryid
,title
,posttime
from tb
order by redu desc,posttime desc--查询结果
--redu categoryid title posttime
------------- ----------- -------------------------------------------------- -----------------------
--1 7 督办情况表 2011-09-29 22:45:38.617
--0 7 FFF 2011-09-29 23:45:38.617
--0 7 EEE 2011-09-29 23:44:38.617
--0 7 AAA 2011-09-29 23:40:39.847
--0 7 AAA 2011-09-29 23:39:21.700--(5 行受影响)