使用不同的 order by 和 union [英] Using different order by with union

问题描述

我想写一个类似的查询

 从 A 中选择前 10 个 *按价格订购联盟从 A 中选择前 3 个 *按价格订购

或者诸如此类的

 从 A 中选择前 10 个 *其中名称像%smt%"按价格订购联盟从 A 中选择前 3 个 *其中名称不像%smt%"按价格订购

你能帮我吗?

解决方案

这应该有效:

SELECT *FROM (SELECT TOP 10 A.*, 0 AS Ordinal从 A按 [价格] 订购)作为 A1联合所有选择 *FROM (SELECT TOP 3 A.*, 1 AS Ordinal从 A按 [姓名] 订购)作为 A2按序数排序

来自 MSDN:><块引用>

在使用 UNION、EXCEPT 或 INTERSECT 运算符的查询中，ORDER BY只允许出现在语句的末尾.此限制适用仅当您在顶层指定 UNION、EXCEPT 和 INTERSECT 时查询而不是在子查询中.

已编辑:要强制您需要将 ORDER BY 应用于外部查询的顺序.我在两个查询中都添加了一个常量值列.

I want to write a query like

    select top 10 * from A
    order by price
    union
    select top 3 * from A 
    order by price

or sth like that

    select top 10 * from A
    where name like '%smt%'
    order by price
    union
    select top 3 * from A
    where name not like '%smt%'
    order by price 

Can you please help me?

解决方案

This should work:

SELECT * 
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
      FROM A
      ORDER BY [Price]) AS A1

UNION ALL

SELECT * 
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
      FROM A
      ORDER BY [Name]) AS A2

ORDER BY Ordinal

From MSDN:

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed only at the end of the statement. This restriction applies only to when you specify UNION, EXCEPT and INTERSECT in a top-level query and not in a subquery.

Edited: to force the order you need to apply an ORDER BY to the outer query. I've added a constant value column to both queries.

这篇关于使用不同的 order by 和 union的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！