mysql - 多条件查询的 SQL 语句，包括 3 个最近的日期

Question

我需要帮助来查找与最近日期、下一个最近日期和之后日期对应的行，其中某些条件 ABC 为“Y”并将其按列名称 XYZ ASC 分组，但 XYZ 可以出现多个次。因此，假设 XYZ 为 50，那么对于三年中的行，XYZ 将为 50。我执行了以下代码，但仅返回数千行中的两行，这是不可能的。我尝试只执行日期条件，但它返回的日期也小于或等于 MAX(DATE)-3。不知道我哪里错了。

select * from money.cash where DATE =(
  select
  MAX(DATE)
  from
  money.cash
  where
  DATE > (select MAX(DATE)-3 from money.cash)
)
GROUP BY XYZ ASC
having ABC = "Y";

表格结构如下(仅为示意图，非实物)

Comp_ID   DATE   XYZ   ABC  $$$$ ....
1     2012-1-1    10    Y   SOME-AMOUNT
2     2011-1-1    10    Y
3     2006-1-1    10    Y
4     2011-1-1    20    Y
5     2002-1-1    20    Y
6     2000-1-1    20    Y
7     1998-1-1    20    Y

所需的 o/p 将是 XYZ=10 的前三行(按升序排列)和最近的 3 个日期(XYZ=20)。

最后且重要 - 此表的值随着新数据的进入而不断变化。因此，o/p(将在新表中)必须反射(reflect)第一个/原始/上表中的动态。

Answer 1

MySQL 没有对 greatest-n-per-group 查询友好的功能。

一个选择是……
- 找到每组 MAX(Date)(XYZ)
- 然后使用该结果查找该日期之前所有记录的MAX(Date)
- 然后对该日期之前的所有记录再次执行此操作

它确实效率低下，但 MySQL 还没有有效执行此操作所需的功能。对不起...

CREATE TABLE yourTable
     (
      comp_id                          INT,
      myDate                           DATE,
      xyz                              INT,
      abc                              VARCHAR(1)
)
;

INSERT INTO yourTable SELECT 1, '2012-01-01', 10, 'Y';
INSERT INTO yourTable SELECT 2, '2011-01-01', 10, 'Y';
INSERT INTO yourTable SELECT 3, '2006-01-01', 10, 'Y';
INSERT INTO yourTable SELECT 4, '2011-01-01', 20, 'Y';
INSERT INTO yourTable SELECT 5, '2002-01-01', 20, 'Y';
INSERT INTO yourTable SELECT 6, '2000-01-01', 20, 'Y';
INSERT INTO yourTable SELECT 7, '1998-01-01', 20, 'Y';


SELECT
  yourTable.*
FROM
(
  SELECT
    lookup.XYZ,
    COALESCE(MAX(yourTable.myDate), lookup.MaxDate)  AS MaxDate
  FROM
  (
    SELECT
      lookup.XYZ,
      COALESCE(MAX(yourTable.myDate), lookup.MaxDate)  AS MaxDate
    FROM
    (
      SELECT
        yourTable.XYZ,
        MAX(yourTable.myDate)  AS MaxDate
      FROM
        yourTable
      WHERE
        yourTable.ABC = 'Y'
      GROUP BY
        yourTable.XYZ
    )
      AS lookup
    LEFT JOIN
      yourTable
        ON  yourTable.XYZ    = lookup.XYZ
        AND yourTable.myDate < lookup.MaxDate
        AND yourTable.ABC    = 'Y'
    GROUP BY
      lookup.XYZ,
      lookup.MaxDate
  )
    AS lookup
  LEFT JOIN
    yourTable
      ON  yourTable.XYZ    = lookup.XYZ
      AND yourTable.myDate < lookup.MaxDate
      AND yourTable.ABC    = 'Y'
  GROUP BY
    lookup.XYZ,
    lookup.MaxDate
)
  AS lookup
INNER JOIN
  yourTable
    ON  yourTable.XYZ     = lookup.XYZ
    AND yourTable.myDate >= lookup.MaxDate
WHERE
  yourTable.ABC = 'Y'
ORDER BY
  yourTable.comp_id
;


DROP TABLE yourTable;

还有其他选项，但它们都有些老套。在 SO 中搜索 greatest-n-per-group mysql。

我使用您的示例数据得出的结果:

Comp_ID | DATE     | XYZ | ABC
------------------------------
   1    | 2012-1-1 | 10  |  Y
   2    | 2011-1-1 | 10  |  Y
   3    | 2006-1-1 | 10  |  Y
   4    | 2011-1-1 | 20  |  Y
   5    | 2002-1-1 | 20  |  Y
   6    | 2000-1-1 | 20  |  Y