我有一个已出售的产品列表,我正在尝试以卖方和价格排序的总和列表,但它不如书中写得。
我正在阅读headfirst sql(关于mysql(,而我正在尝试的内容并不能像书中写的语句一样起作用。我正在与Mariadb练习,但是Mariadb和MySQL几乎相似,所以我认为重要的是没有这种差异,我无法弄清楚为什么会发生这种情况。
This is information about COLUMNS
MariaDB [headfirstdrinks]> DESC movie_table;
+-----------+--------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+----------+----------------+
| movie_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(50) | NO | | NULL | |
| rating | varchar(2) | NO | | NULL | |
| category | varchar(15) | NO | | misc | |
| purchased | date | NO | | NULL | |
| seller | varchar(15) | NO | | In Stock | |
| price | decimal(5,2) | NO | | 0.00 | |
+-----------+--------------+------+-----+----------+----------------+
And This is Table
MariaDB [headfirstdrinks]> SELECT title, seller, price FROM movie_table;
+------------------------------+----------+-------+
| title | seller | price |
+------------------------------+----------+-------+
| Monsters, inc. | Jessie | 2.00 |
| The Godfather | Jessie | 15.00 |
| Gone with the Wind | Jessie | 11.00 |
| American Pie | Jessie | 8.00 |
| Nightmare on Eim Street | Jessie | 5.00 |
| Casablanca | Nicole | 11.00 |
| Big Adventure | Nicole | 2.00 |
| Greg:The Untold Story | Nicole | 8.00 |
| Mad Clowns | In Stock | 11.00 |
| Paraskavendekariaphobia | Brown | 5.00 |
| Rat named Darcy,A | Brown | 2.00 |
| End of the Line | Brown | 8.00 |
| Shark Bait | Brown | 2.00 |
| Angry Pirate | Brown | 15.00 |
| Potentially Habitable Planet | Brown | 5.00 |
+------------------------------+----------+-------+
SELECT seller, SUM(price) FROM movie_table ORDER BY SUM(price) DESC, seller;
+--------+------------+
| seller | SUM(price) |
+--------+------------+
| Jessie | 110.00 |
+--------+------------+
\There's no Nicole and Brown (i don't know why,
\but seems like this is caused because SUM is a function ),
\so i tried "GROUP BY"
SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price DESC;
+----------+------------+
| seller | SUM(price) |
+----------+------------+
| In Stock | 11.00 |
| Nicole | 21.00 |
| Brown | 37.00 |
| Jessie | 41.00 |
+----------+------------+
\this is ascending even though i put DESC so i put ASC
SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price ASC, seller;
+----------+------------+
| seller | SUM(price) |
+----------+------------+
| Jessie | 41.00 |
| Brown | 37.00 |
| In Stock | 11.00 |
| Nicole | 21.00 |
+----------+------------+
\and this is even not Ascending , as i see 'In Stock' upper than 'Nicole'
我做错了哪件事?
您错过了
的组SELECT seller, SUM(price)
FROM movie_table
GROUP BY seller
ORDER BY SUM(price) DESC, seller;
且不使用MySQL版本以前使用MySQL版本,然后5.7我返回了一排,卖方随意价值(默认情况下为其他版本,请提高错误(
对于他人的查询(错误的订单(,您应该使用适当的列名(sum(sum(price((或列名称的别名
SELECT seller, SUM(price) my_col
FROM movie_table
GROUP BY seller ORDER BY my_col ASC, seller;
或
SELECT seller, SUM(price) my_col
FROM movie_table
GROUP BY seller ORDER BY SUM(price ASC, seller;
您可能要使用别名:
SELECT seller, SUM(price) AS price
FROM movie_table
GROUP BY seller
ORDER BY price ASC, seller;
此查询:
SELECT seller, SUM(price) FROM movie_table ORDER BY SUM(price) DESC, seller;
在逻辑上是错误的,因为您没有指定可以将其分组得出的列,因此您可以通过MySQL选择的名称获得整个表格。
在这些查询中:
SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price DESC;
SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price ASC, seller;
您进行了分组,但您 order by
非聚合列。
您可以别名这样的总和,然后按照它们订购:
SELECT seller, SUM(price) as price FROM movie_table GROUP BY seller ORDER BY price DESC;
SELECT seller, SUM(price) as price FROM movie_table GROUP BY seller ORDER BY price ASC, seller;