获取字段首次更改为当前值的日期

Question

用语言来说这是一件相当尴尬的事情，但我想要当该项目第一次切换到关税/客户时的行。 如果商品切换客户，则应返回切换日期，无论关税差异如何。如果某一商品切换项目，则返回日期不得更改，除非新项目的费率与旧项目不同。

不太确定如何使其更清楚，但我愿意接受建议。

我的查询如下所示：

SET @id = 1;
SELECT DISTINCT
    ip.ItemID,
    ip.ProjectID,
    p.TariffID,
    p.CustomerID,
    cs.Date
FROM item_project ip
LEFT JOIN item_project ip1
    ON ip.ItemID = ip1.ItemID
    AND ip.Date < ip1.Date
LEFT JOIN project p
    ON ip.ProjectID = p.ProjectID
LEFT JOIN (
    SELECT
        ip.ItemID,
        ip.Date
    FROM item_project ip
    LEFT JOIN item_project ip1
        ON ip.ProjectID = ip1.ProjectID
        AND ip.Date > ip1.Date
    LEFT JOIN project p
        ON ip.ProjectID = p.ProjectID
    WHERE ip.ItemID = @id
        AND ip1.ItemID IS NULL
        AND p.CustomerID = (
            SELECT p.CustomerID
            FROM project p
            LEFT JOIN item_project ip
                ON p.ProjectID = ip.ProjectID
            LEFT JOIN item_project ip1
                ON ip.ItemID = ip1.ItemID
                AND ip.Date < ip1.Date
            WHERE ip.ItemID = @id
                AND ip1.ItemID IS NULL
            )
        AND p.TariffID = (
            SELECT p.TariffID
            FROM project p
            LEFT JOIN item_project ip
                ON p.ProjectID = ip.ProjectID
            LEFT JOIN item_project ip1
                ON ip.ItemID = ip1.ItemID
                AND ip.Date < ip1.Date
            WHERE ip.ItemID = @id
                AND ip1.ItemID IS NULL
            )
) AS cs
    ON ip.ItemID = cs.ItemID
WHERE ip.ItemID = @id
    AND ip1.ItemID IS NULL

这给了我

"ItemID","ProjectID","TariffID","CustomerID","Date"
"1","2","1","1","2010-11-10 00:00:00"

哪个是错误的日期

SET @id=2 给了我：

"2","2","1","1",NULL

这是正确的，除了

SET @id=3 给出的 日期我：

"3","2","1","1",NULL

除了日期之外，这也是正确的。

这是数据库

CREATE TABLE IF NOT EXISTS `item_project` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `ItemID` varchar(10) NOT NULL,
  `ProjectID` int(10) unsigned NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `item_project` (`ID`, `ItemID`, `ProjectID`, `Date`) VALUES
    (1, '1', 1, '2010-11-05 00:00:00'),
    (2, '1', 2, '2010-11-10 00:00:00'),
    (3, '1', 3, '2010-11-20 00:00:00'),
    (4, '2', 2, '2010-11-21 00:00:00'),
    (5, '3', 4, '2010-11-21 00:00:00'),
    (6, '3', 2, '2010-11-22 00:00:00'),
    (7, '1', 2, '2010-11-23 00:00:00'),

CREATE TABLE IF NOT EXISTS `project` (
  `ProjectID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  `TariffID` varchar(45) NOT NULL,
  `CustomerID` varchar(45) NOT NULL,
  PRIMARY KEY  (`ProjectID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `project` (`ProjectID`, `Name`, `TariffID`, `CustomerID`) VALUES
    (1, 'Test', '2', '1'),
    (2, 'Another test', '1', '1'),
    (3, 'Project1', '1', '1'),
    (4, 'Main project', '2', '2');

CREATE TABLE IF NOT EXISTS `tariff` (
  `TariffID` int(10) unsigned NOT NULL auto_increment,
  `Tariff` varchar(45) NOT NULL,
  PRIMARY KEY  (`TariffID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `tariff` (`TariffID`, `Tariff`) VALUES
    (1, 'Tariff 1'),
    (2, 'Tariff 2');

编辑：项目可以返回到旧的关税或客户。在这种情况下，显示的日期应该是发生这种情况的日期。

Answer 1

有趣的情况。这是我想出的

SELECT i_general.ItemID, ProjectID, TariffID, CustomerID, the_date
FROM
(
    SELECT 
        ip.ItemID,
        p.ProjectID,
        p.TariffID,
        p.CustomerID
    FROM item_project ip
    INNER JOIN project p ON ip.ProjectID = p.ProjectID
    INNER JOIN tariff t ON p.TariffID = t.TariffID
    INNER JOIN (
        SELECT 
            ip.ItemID, MAX(Date) AS max_date
        FROM item_project ip
        GROUP BY ip.ItemID
        ) ip_max ON ip_max.ItemID=ip.ItemID AND ip_max.max_date=ip.Date
) i_general
INNER JOIN (
    SELECT ip1.ItemID, IF(MIN(ip2.Date) IS NULL,MIN(ip1.Date),MIN(ip2.Date)) AS the_date  
    FROM item_project ip1
    INNER JOIN project p1 ON ip1.ProjectID = p1.ProjectID
    LEFT JOIN item_project ip2 ON ip1.ItemID=ip2.ItemID AND ip1.Date < ip2.Date  
    LEFT JOIN project p2 ON ip2.ProjectID = p2.ProjectID AND (p2.TariffID!=p1.TariffID OR p2.CustomerID!=p1.CustomerID)
    GROUP BY ip1.ItemID
    ) i_date_info ON i_date_info.ItemID = i_general.ItemID

当然，您可以根据需要插入一些 WHERE ItemID = @id 。内部查询越多越好。

无论如何，它的结果是

+--------+-----------+----------+------------+---------------------+
| ItemID | ProjectID | TariffID | CustomerID | the_date            |
+--------+-----------+----------+------------+---------------------+
| 1      |         3 | 1        | 1          | 2010-11-10 00:00:00 |
| 2      |         2 | 1        | 1          | 2010-11-21 00:00:00 |
| 3      |         2 | 1        | 1          | 2010-11-22 00:00:00 |
+--------+-----------+----------+------------+---------------------+

，它似乎适用于现有的数据集。让我知道您是否可以提供它不起作用的测试数据。

Answer 2

我之前的答案似乎也适用于新数据集，但我想我理解您所看到的问题。新提出的解决方案：
<代码>

SELECT i_general.ItemID, ProjectID, TariffID, CustomerID, the_date
FROM
(
    SELECT ip.ItemID, p.ProjectID, p.TariffID, p.CustomerID
    FROM item_project ip
    INNER JOIN project p ON ip.ProjectID = p.ProjectID
    INNER JOIN tariff t ON p.TariffID = t.TariffID
    INNER JOIN (
        SELECT ip.ItemID, MAX(Date) AS max_date
        FROM item_project ip
        GROUP BY ip.ItemID
    ) ip_max ON ip_max.ItemID=ip.ItemID AND ip_max.max_date=ip.Date
) i_general
INNER JOIN 
(
    SELECT ItemID_1 AS ItemID, IF(MAX(Next_Change_Date) IS NULL, MIN(Date_1), MAX(Next_Change_Date)) AS the_date
    FROM 
    (
        SELECT ItemID_1, Date_1, MIN(Date_2) AS Next_Change_Date
        FROM
        (
            SELECT ip1.ItemID AS ItemID_1, ip1.Date AS Date_1, p1.TariffID AS TariffID_1, p1.CustomerID AS CustomerID_1
            FROM item_project ip1
            INNER JOIN project p1 ON ip1.ProjectID = p1.ProjectID
        ) ipp1
        LEFT JOIN
        (
            SELECT ip2.ItemID AS ItemID_2, ip2.Date AS Date_2, p2.TariffID AS TariffID_2, p2.CustomerID AS CustomerID_2
            FROM item_project ip2
            INNER JOIN project p2 ON ip2.ProjectID = p2.ProjectID
        ) ipp2 ON ItemID_1=ItemID_2 AND Date_1 < Date_2 AND ((TariffID_1!=TariffID_2 OR CustomerID_1!=CustomerID_2) AND Date_2 IS NOT NULL)
        GROUP BY ItemID_1, Date_1
    ) i_date_pair_info
    GROUP BY ItemID
) i_date_info ON i_date_info.ItemID = i_general.ItemID

我认为它提供了您正在寻找的结果。

它更加健壮，因为内部子查询之一 i_date_pair_info 显式地将每个日期与所有后续更改（如果有）配对。然后分组消除除了最快的更改之外的所有更改。