是否可以对在mysql SELECT语句中返回列的顺序进行排序？ [英] Is it possible to sort the order in which columns are returned in a mysql SELECT statement?

问题描述

想象一下在线调查的两个问题：

1. 你喜欢苹果吗？

结果存储在mysql db列q1中为 1 或 0 for No

2. ？

结果存储在mysql db列q2中为 1

>

  SELECT SUM（q1）AS q1Sum，SUM（q2）AS q2Sum FROM myTable 
  pre> 
 
 
假设q1Sum = 10和q2Sum = 12。当前的SELECT语句将返回：
  + ------- + ------- + 
 | q1Sum | q2Sum | 
 + ------- + ------- + 
 | 10 | 12 | 
 + ------- + ------- + 
  
 但可以返回不同的列顺序，以便最大的SUM始终是在SELECT语句中不更改列表顺序的情况下返回的第一列？
  SELECT SUM（q1）AS q1Sum，SUM（q2）AS q2Sum FROM myTable<其他>> 
 
 + ------- + ------- + 
 | q2Sum | q1Sum | 
 + ------- + ------- + 
 | 12 | 10 | 
 + ------- + ------- + 
  
如果q1Sum大于q2Sum，返回的列顺序将恢复到原始顺序。
 
 
 
 为什么我问这个问题？ 
 
 
 
 
我继承了一个包含多个列的大型表格，类似于上面的对多个问题的Yes = 1和No = 0响应。我假定正确的方法是创建保存SUM值的变量，并将它们插入到临时表行，然后选择/排序返回的行。但是，这并不真正回答是否可能排序返回的列的问题，我甚至不确定我的假设是正确的方法来做这个... 
 
 
虽然我知道如何排序结果一次返回无论顺序（使用php在我的情况下），我很好奇，如果有一种方法自动排序返回的列在mysql中，以便最高的值总是第一
 
 
 好奇，如果有一个优雅的答案。虽然很容易，使用ORDER BY在mysql中返回排序ROWS，我还没有看到如何排序COLS的一个返回的行的多个SUM值的值如上所述。想象上面的10个问题，而不是2.我假设这是不可能的，但希望有人可以证明我错了...以一个很好的方式。
 
 
 
 
解决方案
 感谢@ user2864740和@sgeddes的快速回复。 
 
 
  SURVEY SAYS：不能使用mysql而不使用动态sql在SELECT语句中对返回的列进行排序。
 
 
我想出的解决方案使用临时表，类似于下面的例子。虽然也许不太理想，它回答了邮件，我需要什么。好奇，如果有一个更好的方法来做同样的事情，或者如果我搞砸了别的东西下面。 
  DROP TEMPORARY TABLE如果EXISTS orderedSums; 
 SET @ q1Sum =（SELECT SUM（q1）FROM myTable）; 
 SET @ q2Sum =（SELECT SUM（q2）FROM myTable）; 
 
 CREATE TEMPORARY TABLE orderedSums（
 qNum VARCHAR（5），
 qSum INT 
）; 
 
 INSERT INTO orderedSums 
 VALUES（'q1Sum'，@ q1Sum），（'q2Sum'，@ q2Sum）; 
 
 SELECT qNum，qSum 
 FROM orderedSums 
 ORDER BY qSum DESC; 
 
返回从myTable以降序排列的多行SUM。
 
Imagine two questions from an online survey:

Do you like apples? 



  
Result stored in mysql db column "q1" as 1 for Yes or 0 for No



Do you like oranges? 



  
Result stored in mysql db column "q2" as 1 for Yes or 0 for No
Imagine the following SELECT statement:
SELECT SUM(q1) AS q1Sum, SUM(q2) AS q2Sum FROM myTable
Assume q1Sum=10 and q2Sum=12.  The current SELECT statement would return:
+-------+-------+
| q1Sum | q2Sum |
+-------+-------+
|   10  |   12  |
+-------+-------+
But is it possible to return a different column order so that the greatest SUM is always the first column returned without changing the list order in the SELECT statement?
SELECT SUM(q1) AS q1Sum, SUM(q2) AS q2Sum FROM myTable << something else >>

+-------+-------+
| q2Sum | q1Sum |
+-------+-------+
|   12  |   10  |
+-------+-------+
If q1Sum becomes greater than q2Sum the column order returned would revert to the original order.

Why am I asking this question?

I have inherited a large table of multiple columns from a survey similar to above with Yes=1 and No=0 responses to multiple questions.  I assume the right way to do this is to create variables that hold the SUM values and insert them into temp table rows and then select/sort returned rows.  However, this doesn't really answer the question of 'is it possible to sort returned columns' and I'm not even sure my assumption is correct on the 'right way to do this'...
While I know how to sort the results once returned regardless of order (using php in my case), I was curious if there was a way to automatically sort the returned columns in mysql so that the highest value is always the first column and the SUM-ed results in the remaining columns decrease numerically for the remaining columns returned.
Curious if there is an elegant answer.  While it is easy to sort ROWS returned in mysql using ORDER BY, I have not seen an example of how to sort COLS of a single returned row of multiple SUM-ed values as articulated above.  Imagine 10 questions above instead of 2.  I am assuming this is not possible but hope someone can prove me wrong...in a nice way.

解决方案
Thanks @user2864740 and @sgeddes for your prompt replies.

  
SURVEY SAYS:  It is NOT possible to sort returned columns in a SELECT statement using mysql without using dynamic sql.
The solution I came up with uses a temporary table and is similar to the example below.  While perhaps less than ideal, it answered the mail for what I needed. Curious if there is a better way to do the same thing or if I messed anything else up below. 
DROP TEMPORARY TABLE IF EXISTS orderedSums;
SET @q1Sum = (SELECT SUM(q1) FROM myTable);
SET @q2Sum = (SELECT SUM(q2) FROM myTable);

CREATE TEMPORARY TABLE orderedSums (
    qNum VARCHAR(5),
    qSum INT
);

INSERT INTO orderedSums
    VALUES ('q1Sum', @q1Sum),('q2Sum', @q2Sum);

SELECT qNum, qSum
    FROM orderedSums
    ORDER BY qSum DESC;
Returns multiple rows of SUMs sorted in descending order from myTable.

                        
这篇关于是否可以对在mysql SELECT语句中返回列的顺序进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！