select top 3 * from Article order by ArticleId ASC union select top 3 * from Article order by ArticleId DESC
可以用table expression:with firstThree as ( select top 3 * from Article order by ArticleId ASC ), lastThree as ( select top 3 * from Article order by ArticleId DESC ) select * from firstThree union select * from lastThree;
SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (ORDER BY ArticleId ASC) AS ROWNUMBER1,ROW_NUMBER() OVER (ORDER BY ArticleId DESC) AS ROWNUMBER2 FROM Article ) A WHERE A.ROWNUMBER1 BETWEEN 1 AND 3 OR A.ROWNUMBER2 BETWEEN 1 AND 3 ORDER BY A.ArticleId
select * form ( select top 3 * from Article order by ArticleId ASC) as tb1 union select * form ( select top 3 * from Article order by ArticleId DESC) as tb2
晕 from 打成 form了
用union select top 3 * from Article order by ArticleId ASC union select top 3 * from Article order by ArticleId DESC
用并操作union 前提是这两个列的长度和类型要一致 select top 3 * from Article order by ArticleId ASC union select top 3 * from Article order by ArticleId DESC
修正一个下 select * form ( select top 3 * from Article order by ArticleId ASC) union select * form ( select top 3 * from Article order by ArticleId DESC)
select top 3 * from Article order by ArticleId ASC union all select top 3 * from Article order by ArticleId DESC
搞成临时表再UNION 否则前3的查询不能加ORDER BY的
1.嵌套查询 SELECT * FROM (select top 3 * from Article order by ArticleId ASC ) t1 UNION SELECT * FROM (select top 3 * from Article order by ArticleId DESC )t2 2.存储过程 分别查询插入临时表,查询返回临时表就行
1、union all 2、把两次查询的结果插入临时表,最后查询一下临时表 3、row_number()函数,一次性获取
union
select top 3 * from Article order by ArticleId DESC
firstThree as
(
select top 3 * from Article order by ArticleId ASC
),
lastThree as
(
select top 3 * from Article order by ArticleId DESC
)
select * from firstThree union select * from lastThree;
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY ArticleId ASC) AS ROWNUMBER1,ROW_NUMBER() OVER (ORDER BY ArticleId DESC) AS ROWNUMBER2 FROM Article
) A
WHERE A.ROWNUMBER1 BETWEEN 1 AND 3 OR A.ROWNUMBER2 BETWEEN 1 AND 3 ORDER BY A.ArticleId
select * form (
select top 3 * from Article order by ArticleId ASC) as tb1
union
select * form (
select top 3 * from Article order by ArticleId DESC) as tb2
select top 3 * from Article order by ArticleId ASC
union
select top 3 * from Article order by ArticleId DESC
select top 3 * from Article order by ArticleId ASC
union
select top 3 * from Article order by ArticleId DESC
消息 156,级别 15,状态 1,第 3 行
关键字 'union' 附近有语法错误。
此写法可以,需要再select * from as 一下才可以union
select * form (
select top 3 * from Article order by ArticleId ASC)
union
select * form (
select top 3 * from Article order by ArticleId DESC)
union all
select top 3 * from Article order by ArticleId DESC
SELECT * FROM (select top 3 * from Article order by ArticleId ASC ) t1
UNION
SELECT * FROM (select top 3 * from Article order by ArticleId DESC )t2
2.存储过程
分别查询插入临时表,查询返回临时表就行
2、把两次查询的结果插入临时表,最后查询一下临时表
3、row_number()函数,一次性获取
是的,我重新修改了,在12#