mysql group by, max, count实现

Question

table

items table
------------------
| id |   item    |
------------------
| 1  |   pearl   |
| 2  |   ruby    |
| 3  |   gold    |
| 4  |   diamond |
------------------

transaction table
--------------------------------------------------
| item_id |   sell_price   |    created_at       |
--------------------------------------------------
|    1    |      10        | 2020-08-21 01:50:24 |
|    1    |      20        | 2020-08-22 05:51:10 |
|    3    |      20        | 2020-08-23 06:52:05 |
|    3    |      30        | 2020-08-24 22:55:07 |
|    4    |      50        | 2020-08-25 20:58:18 |
|    2    |      10        | 2020-08-26 12:59:55 |
--------------------------------------------------

预期输出:

--------------------------------------------------------------------
| item_id |  name    |   quantity    | price |    created_at       |
--------------------------------------------------------------------
|    1    |  pearl   |      2        |  20   | 2020-08-22 05:51:10 |
|    2    |  ruby    |      1        |  10   | 2020-08-26 12:59:55 |
|    3    |  gold    |      2        |  30   | 2020-08-24 22:55:07 |
|    4    |  diamond |      1        |  50   | 2020-08-25 20:58:18 |
--------------------------------------------------------------------

查询

SELECT
    items.id AS item_id,
    items.item,
    COUNT(transaction.item_id) AS quantity,
    MAX(transaction.price) AS sell_price
FROM transaction
LEFT JOIN items
ON transaction.item_id = items.id 
GROUP BY transaction.item_id;

我上面的查询在没有 transaction.created_at 的情况下工作得很好在 select条款，但是当我把 select transaction.created_at它抛出一个错误:

SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.transaction.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

从您共享的所需输出来看，您似乎正在尝试查询最大值 created_at :

SELECT
    items.id AS item_id,
    items.item,
    COUNT(transaction.item_id) AS quantity,
    MAX(transaction.price) AS sell_price,
    MAX(created_at) AS created_at -- Here!
FROM transaction
LEFT JOIN items
ON transaction.item_id = items.id 
GROUP BY transaction.item_id;