想象一下在线调查的两个问题:
结果存储在mysql db列q1中为 1 或 0 for No
结果存储在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,返回的列顺序将恢复到原始顺序。
为什么我问这个问题?
解决方案
感谢@ 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:
Result stored in mysql db column "q1" as 1 for Yes or 0 for No
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 myTableAssume 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?
解决方案
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语句中返回列的顺序进行排序?
发布评论