SQL ORDER BY - 为什么它在这里不起作用? [英] SQL ORDER BY - Why is it not working here?

问题描述

这是我的桌子:

+---------------------+--------+----------+
| date                | sku    | quantity |
+---------------------+--------+----------+
| 2017-08-23 14:58:00 | 123333 |        2 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123337 |        2 |
| 2017-08-23 14:58:00 | 123335 |        1 |
| 2017-08-23 14:58:00 | 123331 |        1 |
| 2017-08-23 14:58:00 | 123332 |        2 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123334 |        1 |
| 2017-08-23 14:58:00 | 123334 |        3 |
+---------------------+--------+----------+

我想执行如下的选择语句:

I would like to perform an select statement as follows:

select sku,sum(quantity) as 'total quant' from transactions
where DATE(date) between '2017-07-23' and '2017-09-23'
group by sku
order by quantity desc;

但我得到了这个:

+--------+-------------+
| sku    | total quant |
+--------+-------------+
| 123332 | 2           |
| 123337 | 2           |
| 123333 | 5           |
| 123334 | 4           |
| 123335 | 1           |
| 123331 | 1           |
+--------+-------------+

这不是我期望的顺序.为什么 order by 在这里不起作用?

SIDE NOTE 我的 quantity 列的数据类型为 int，我尝试按照 这篇文章 但无济于事.

SIDE NOTE My quantitycolumn is of data type int and I have tried casting as suggested in this post but to no avail.

推荐答案

您是按数量订购的.因为它不是聚合的，所以您从每个组中获得一个任意值.您需要按总数订购.一种方法是:

You are ordering by quantity. Because it is not aggregated, you are getting an arbitrary value from each group. You need to order by the total. One method is:

order by sum(quantity) desc

但是，我建议分配一个合理的别名(不需要转义的别名)并使用它:

However, I would recommend assigning a reasonable alias (one that doesn't need to be escaped) and using that:

select sku,sum(quantity) as total_quantity
from transactions
where DATE(date) between '2017-07-23' and '2017-09-23'
group by sku
order by total_quantity desc;

这篇关于SQL ORDER BY - 为什么它在这里不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！