SELECT UNION和ORDER BY在mysql ..如何? [英] SELECT UNION and ORDER BY in mysql.. how to?

问题描述

我想从单个表中获取所有行，但是以不同的方式对它们进行排序. 例如我写

i would like to get from a single table, all rows, but order them in different ways. For example i write

(SELECT * FROM table1
ORDER BY fieldA ASC LIMIT 3
)
UNION
(
SELECT * FROM table1
ORDER BY FieldB DESC
)

它的工作原理是，忽略了(FIELDB DESC)的二阶...有人知道为什么吗? 谢谢

It works, excpet that the second order by (FIELDB DESC) is ignored... Somebody know Why ? Thank you

推荐答案

UNION运算符执行隐式排序，作为联合操作(IIRC，在键列上)的一部分.

The UNION operator performs an implied sort as part of the union operation (IIRC, on the key column(s)).

如果要在结果中进行其他排序，则必须将ORDER BY应用于联合选择.

If you want other sorting in the result, you have to apply an ORDER BY to the unioned selection.

在您的情况下，您需要某种方式来区分第一个选择项和第二个选择项，以便可以正确地订购联合.像(未经测试的)这样的东西:

In your case, you need some way to distinguish between the first selection and the second, so that you can order the union properly. Something like (untested):

(SELECT table1.*, 0 AS TMP_ORDER FROM table1 ORDER BY fieldA ASC LIMIT 3)
UNION
(SELECT table1.*, 1 AS TMP_ORDER FROM table1)
ORDER BY TMP_ORDER ASC, 
CASE WHEN TMP_ORDER = 0 THEN fieldA ELSE 0 END ASC, 
CASE WHEN TMP_ORDER = 1 THEN fieldB ELSE 0 END DESC

这种方法的问题在于，由于UNION中的第一个查询，您选择的三行将具有重复项(因为列不完全匹配).

The problem with this approach is that you'll have duplicates for the three rows selected as part of the first query in the UNION (since the columns don't totally match).

您确定不能使用两个SELECT语句吗?

Are you sure you can't use two SELECT statments instead?

这篇关于SELECT UNION和ORDER BY在mysql ..如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！