mysql - 如何获取每个产品/进入日期最近 4 周的数据

Question

我有一个问题，我有 4 天作为输入，我必须获取他们过去 4 周的每一天的值

这最后 4 周并不意味着我有解决方案的最近 4 周。

    SELECT prodno, 
           ardate8n, 
           selloff1 
    FROM   sales s 
           JOIN ( SELECT CAST(DATE_SUB('2011-02-27', INTERVAL 1 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-27' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-27', INTERVAL 2 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-27' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-27', INTERVAL 3 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-27' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-27', INTERVAL 4 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-27' AS adt

UNION ALL

 SELECT CAST(DATE_SUB('2011-02-26', INTERVAL 1 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-26' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-26', INTERVAL 2 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-26' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-26', INTERVAL 3 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-26' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-26', INTERVAL 4 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-26' AS adt

UNION ALL

 SELECT CAST(DATE_SUB('2011-02-25', INTERVAL 1 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-25' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-25', INTERVAL 2 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-25' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-25', INTERVAL 3 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-25' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-25', INTERVAL 4 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-25' AS adt

UNION ALL

 SELECT CAST(DATE_SUB('2011-02-24', INTERVAL 1 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-24' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-24', INTERVAL 2 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-24' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-24', INTERVAL 3 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-24' AS adt
 UNION ALL
 SELECT CAST(DATE_SUB('2011-02-24', INTERVAL 4 WEEK) AS DECIMAL(8,0)) AS wdt, '2011-02-24' AS adt) days 
             ON s.ardate8n = days.wdt 
    WHERE  custno = 38726 
           AND deptno = 0 
           AND Find_in_set(prodno, '0020,0064,0070,0073,0096') > 0 
    ORDER by prodno,adt,ardate8n;

如您所见，我对最近 4 周的数据进行了硬编码。这只会读取每个产品/每个条目日期的最近 4 周，如果缺少一个或多个星期的记录，我不会得到 4 行。

所以我需要通过某种限制动态地获取它。 sales.ardate8n 给出给定日期是否有记录。

这将返回以下数据

0006, '2011-03-03', 20110127, 0
0006, '2011-03-03', 20110203, 0
0006, '2011-03-03', 20110210, 0
0006, '2011-03-04', 20110128, 0
0006, '2011-03-04', 20110204, 0
0006, '2011-03-05', 20110129, 0
0006, '2011-03-05', 20110205, 0
0006, '2011-03-05', 20110212, 0
0006, '2011-03-05', 20110219, 0

如您所见，对于输入日期 2011-03-03，同一工作日的产品只有 3 行。对于输入日期 2011-03-04，同一工作日的产品只有 2 行。

Answer 1

虽然以逗号分隔的列表是将一组值传递给查询的一种便捷方式，但它不适用于这种情况，在这种情况下，要求请求的每个值都应出现在数据集中。因此，不应将值用作列表，而应将值作为数据列，行集。这样就可以将所有这些都包含在结果集中。

以上内容适用于查询中的 prodno 值列表，但同样适用于日期。

下面是一个示例，说明如果将输入数据制成数据集，将如何满足要求:

SELECT
  p.prodno,
  d.date,
  CAST(DATESUB(d.date, INTERVAL (w.weeksAgo) WEEK) AS DECIMAL(8, 0)) AS ardate8n,
  s.selloff1

FROM       (SELECT @date1 AS date
  UNION ALL SELECT @date2
  UNION ALL SELECT @date3
  UNION ALL SELECT @date4) d

CROSS JOIN (SELECT 1 AS weeksAgo
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4) w

CROSS JOIN (SELECT '0020' AS prodno
  UNION ALL SELECT '0064'
  UNION ALL SELECT '0070'
  UNION ALL SELECT '0073'
  UNION ALL SELECT '0096') p

LEFT JOIN sales s
  ON s.ardate8n = CAST(DATESUB(d.date, INTERVAL (w.weeksAgo) WEEK) AS DECIMAL(8, 0))
 AND s.prodno   = p.prodno
 AND s.custno   = 38726 
 AND s.deptno   = 0