LeetCode MySQL-1484. Group Sold Products By The Date

    AllenLi
    LeetCode MySQL

    Write an SQL query 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.

    寫一個 SQL Query來尋找每一個日期銷售的不同產品數量與名稱。
並以 sell_date 作為排序結果。

    Table: Activities

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key 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.

    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.

    Solution:
    1. 確認標題 sell_date 。
    2. 確認標題 num_sold，計算不重複的商品數量 。
    3. 確認標題 products，串連該時間群組的商品名稱。
    4. 以 sell_date 作為排序。

    Code.1:

    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;

    Code.2:

    SELECT 
  sell_date,
  COUNT(product) AS num_sold,
  GROUP_CONCAT(product ORDER BY product) AS products
FROM (SELECT DISTINCT * FROM Activities) AS Activities
GROUP BY sell_date
ORDER BY sell_date;

    newProducts

    Remark:
    GROUP_CONCAT:將將多個字串內容進行合併，如空值則返回 NULL。

    GROUP_CONCAT(DISTINCT [要連接的字串] [Order BY ASC/DESC 排序字串] [Separator '分隔符號'])

    Related Posts