mysql - 从表中按年、月、周选择数据

Question

我有以下表格:

Table: promotion_asset
promotion_id
asset_id

(促销事件可以有很多素材资源)

Table: promotion_tracking
id(PK)
asset_id
date_scanned

(每次扫描 Assets 时的日志表)

我想在一个查询中尝试 TRY，以便能够确定特定促销 ID 中的 Assets 已被扫描了多少次:今年、本月和本周。

我尝试了很多不同的方法，但找不到一个返回正确结果的查询。

Answer 1

我只有 sql server，所以我能够测试 Year 和 Month 函数，查找 WeekOfYear for mysql

SELECT pa.promotion_id
   , SUM(CASE
        WHEN YEAR(pt.date_scanned) = YEAR(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisYear
   , SUM(CASE
        WHEN YEAR(pt.date_scanned) = YEAR(GETDATE())
           AND MONTH(pt.date_scanned) = MONTH(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisMonth
   , SUM(CASE
        WHEN WEEKOFYEAR(pt.date_scanned) = WEEKOFYEAR(GETDATE()) THEN 1
        ELSE 0
     END CASE) AS ThisWeek
FROM promotion_tracking AS pt
INNER JOIN promotion_asset AS pa ON pt.asset_id = pa.asset_id
GROUP BY pa.promotion_id