在mySQL中按表达式排序 [英] Sorting by expression in mySQL

问题描述

我有区域，并且在这些区域内出售门票......我使用以下查询来获取每个区域售出的门票数量，并且我需要进行排序以便可用区域(其中计数 <容量)是在排序顺序的顶部:

I have zones, and tickets sold within those zones....I use the following query to get the number of tickets sold per zone, and I need to sort so that the available zones (where thecount < capacity) are at the top of the sortorder:

SELECT z.name, z.capacity,count(t.ticketid) as thecount  
FROM tickets t JOIN zones z ON z.zoneid=t.zoneid 
GROUP BY z.name ORDER BY z.sortorder

返回:

+--------+----------+---------+
姓名    帽    伯爵
+--------+----------+----------+
区域1 |   100    |  100
2区|   300    |  300
区域3 |   250    |  101
区域4 |   600    |  522
5区|   160    |  160
+--------+----------+----------+

+--------+----------+----------+
name       cap       thecount
+--------+----------+----------+
Zone1 |    100     |   100
Zone2 |     300    |   300
Zone3 |    250     |    101
Zone4 |    600     |    522
Zone5 |    160     |    160
+--------+----------+----------+

我需要按以下顺序排序:
Zone3
Zone4
区域1
区域2
5区

I need the sort to be in the following order:
Zone3
Zone4
Zone1
Zone2
Zone5

我不知道如何使用表达式或其他东西来实现这一点---有什么有用的提示吗?

I'm not sure how to use an expression or something else to achieve this---any helpful hints?

推荐答案

SELECT *, capacity - thecount AS availableSeats
FROM (
    SELECT z.name, z.capacity,count(t.ticketid) as thecount  
    FROM tickets t JOIN zones z ON z.zoneid=t.zoneid 
    GROUP BY z.name
) t
ORDER BY availableSeats, name

我认为您可能还需要按 z.name, z.capacity 分组.

I think you might also need to group by z.name, z.capacity.

这篇关于在mySQL中按表达式排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！