mysql - 根据日期查找差异

Question

我有一个产品表，其中区域可以定期更改。仅当区域发生更改时，我才需要查找产品。我的数据如下所示

ProductID 123
zones west
Addate 08/12/2018
rate 5.00

ProductID 123
zones east
Addate 08/12/2018
rate 10.00

ProductID 123
Region west
Addate 08/13/2018
rate 10.00

ProductID 123
zones east
Addate 08/13/2018
rate 10.00

表格格式:

ProductID | zones | Addate | rate
123 | west | 08/12/2018 | 5.00
123 | east | 08/12/2018 | 10.00
123 | west | 08/13/2018 | 10.00
123 | east | 08/13/2018 | 10.00

下面的查询不应返回任何数据，因为过去两天没有任何区域发生更改，但它会返回两条记录。你能告诉我如何重写这个吗？

Select A.zones as newArea,B.zones as oldArea ,A.Addate newdate,B.Addate as olddate
from Products A,Products B
where A.ProductId=B.ProductId
and A.Addate=CURDATE()
and B.Addate=DATE_ADD(CURDATE(), INTERVAL -1 DAY)
and A.zones<>B.zones

Answer 1

我们可以编写一个查询，返回今天的行，但昨天没有匹配的行。我们可以使用反连接模式。

SELECT A.zones    AS newarea
     , A.addate   AS newdate
 FROM Products A
 LEFT 
 JOIN Products B
   ON B.productid = A.productid
  AND B.addate    = CURDATE() + INTERVAL -1 DAY
  AND B.zones     = A.zones
WHERE B.productid IS NULL 
  AND A.addate = CURDATE() 

然后，我们还可以向 Products 添加外部联接，以查找除区域之外匹配的行...

SELECT A.zones    AS newarea
     , C.zones    AS oldarea
     , A.addate   AS newdate
 FROM Products A
 LEFT 
 JOIN Products B
   ON B.productid = A.productid
  AND B.addate    = CURDATE() + INTERVAL -1 DAY
  AND B.zones     = A.zones
 LEFT
 JOIN Products C
   ON C.productid = A.productid
  AND C.addate    = CURDATE() + INTERVAL -1 DAY
  AND C.zones    <> A.zones
WHERE B.productid IS NULL
  AND A.addate = CURDATE()

<小时/>

建议:放弃内部联接操作的旧式逗号语法，并使用 JOIN 关键字。