8/26 每日一題(MYSQL輸出以日期為分組賣出多少產品,並將當天賣出的產品列在同一列)

Table Activities:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.

 

Write a solution to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically.

Return the result table ordered by sell_date.

The result format is in the following example.

 

Example 1:

Input: 
Activities table:
+------------+------------+
| sell_date  | product     |
+------------+------------+
| 2020-05-30 | Headphone  |
| 2020-06-01 | Pencil     |
| 2020-06-02 | Mask       |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible      |
| 2020-06-02 | Mask       |
| 2020-05-30 | T-Shirt    |
+------------+------------+
Output: 
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
Explanation: 
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.

 # Write your MySQL query statement below

SELECT sell_date,

COUNT(DISTINCT product) AS num_sold ,

GROUP_CONCAT(DISTINCT product ORDER BY product) AS products  #DISTINCT 是因為0602Mask出現兩次,避免重複出現,然後

#如果希望在同一欄位列出不同名稱就是需要使用GROUP_CONCAT()

#因為需要以日期排序再以商品名稱排序

#但這個商品名稱不只有一個商品,所以必須在這個分組內先排序一次,無法在外面排序

FROM Activities

GROUP BY sell_date

ORDER BY sell_date  

-------------------

# Write your MySQL query statement below
SELECT sell_date, count(distinct product) AS num_sold,
GROUP_CONCAT(distinct product order by product ASC separator ',') AS products
FROM Activities 
GROUP BY sell_date
ORDER BY sell_date ASC