通过联合使用不同的顺序

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?

你能帮我么？

5 个解决方案

#1

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:

来自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.

在使用UNION，EXCEPT或INTERSECT运算符的查询中，只允许在语句末尾使用ORDER BY。此限制仅适用于在*查询中而不是在子查询中指定UNION，EXCEPT和INTERSECT的情况。

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应用于外部查询所需的顺序。我为两个查询添加了一个常量值列。

#2

This is a real hacky way to do this. You probably want these as separate queries in reality, but this should give you the result you want...

这是一个真正的hacky方式来做到这一点。你可能希望这些作为现实中的单独查询，但这应该给你想要的结果......

select *
from (
  select top 10 *, 1 as 'ord', price as 'ordprice' from A
  union 
  select top 3 *, 2 as 'ord', 0 as 'ordprice' from A
) a
order by ord, ordprice, name

#3

UNION doesn't like ORDER by clauses in the UNIONed expressions. Try this:

UNION不喜欢UNIONed表达式中的ORDER by子句。尝试这个：

SELECT * FROM
   (SELECT TOP 10 * FROM A ORDER BY Price) SetA
UNION
SELECT * FROM
   (SELECT TOP 3 * FROM a ORDER BY name) Setb
[ORDER BY something]

This spoofs the UNION operator into ignoring the ORDER BYs, which still operate correctly on the TOP operator. You can apply a final ORDER BY to order the UNIONed set, if you like.

这会欺骗UNION运算符忽略ORDER BY，它仍然可以在TOP运算符上正确运行。如果您愿意，可以应用最终的ORDER BY来订购UNIONed集。

[No longer applies exactly to your question now that it's edited!]

[现在它已被编辑，不再完全适用于您的问题！]

#4

select top 10 *,0 as RS from A
union
select top 3 *,1 as RS from A 
order by
    RS,
    CASE WHEN RS=0 THEN price END, --Don't affect RS 1
    name

#5

-2

cmd.CommandText = "SELECT 0 AS Employee_ID, 'No Employees' as Employee_FullName , 'id1' Orderkey UNION ALL SELECT Employee_ID, Employee_FullName, 'id2' Orderkey FROM tblEmployee ORDER BY Orderkey, Employee_FullName"

cmd.CommandText = “AS SELECT的Employee_ID 0， '无' 雇员为Employee_FullName， 'ID1' Orderkey UNION ALL SELECT雇员，Employee_FullName， 'ID2' Orderkey FROM tblEmployee ORDER BY Orderkey，Employee_FullName”

ds = dbconn.SelectQuery(cmd)

ds = dbconn.SelectQuery（cmd）

ds.Tables(0).Columns.Remove(ds.Tables(0).Columns("Orderkey"))

ds.Tables（0）.Columns.Remove（ds.Tables（0）.Columns（ “Orderkey”））

#1