根据PHP中第一列的值检测其他列的变化

Question

transaction_id   vendor_id    currency

     1101           201          USD

     1102           202          EUR

     1103           203          CHF

     1104           202          EUR

     1105           204          HUF

     *1106          202          USD

     *1107          204          USD

我有一个包含三列的表，并且有最后两个交易（1106 和 1107）的交易列表，其中vendor_idof202和204之前的默认货币分别为 EUR 和 HUF。然而，在最后一笔交易中，这两个供应商在他们最后的记录中使用了错误的货币。首选的结果是用错误的货币打印这两个交易。

如何检测错误的货币并在 PHP 中打印这些交易（表来自数据库 MySQL，表名是table_transaction）？

Answer 1

考虑以下...DROP TABLE IF EXISTS my_table;CREATE TABLE my_table(transaction_id SERIAL PRIMARY KEY,vendor_id INT NOT NULL,currency CHAR(3) NOT NULL);INSERT INTO my_table VALUES(1101,201,'USD'),(1102,202,'EUR'),(1103,203,'CHF'),(1104,202,'EUR'),(1105,204,'HUF'),(1106,202,'USD'),(1107,204,'USD');SELECT a.*&nbsp;&nbsp; FROM my_table a&nbsp; LEFT&nbsp;&nbsp; JOIN&nbsp;&nbsp; &nbsp; &nbsp;( SELECT x.*&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM my_table x&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;JOIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( SELECT MIN(transaction_id) transaction_id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM my_table&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BY vendor_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON y.transaction_id = x.transaction_id&nbsp; &nbsp; &nbsp;) b&nbsp;&nbsp; &nbsp; ON b.vendor_id = a.vendor_id&nbsp;&nbsp; &nbsp;AND b.currency = a.currency&nbsp;WHERE b.transaction_id IS NULL;&nbsp;+----------------+-----------+----------+&nbsp;| transaction_id | vendor_id | currency |&nbsp;+----------------+-----------+----------+&nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1106 |&nbsp; &nbsp; &nbsp; &nbsp;202 | USD&nbsp; &nbsp; &nbsp; |&nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1107 |&nbsp; &nbsp; &nbsp; &nbsp;204 | USD&nbsp; &nbsp; &nbsp; |&nbsp;+----------------+-----------+----------+&nbsp;要了解这里发生了什么，只需将查询分解为其组成部分并添加b.transaction_id到 SELECT...离开'y'SELECT MIN(transaction_id) transaction_id&nbsp;&nbsp; FROM my_table&nbsp;&nbsp;GROUP&nbsp;&nbsp; &nbsp; BY vendor_id该位获取每个供应商的第一个 transaction_id。这是有效的，因为 transaction_id 是 PRIMARY KEY。然后我们将此结果连接回源表，以获取与该 transaction_id 对应的其他列（具体currency而言，在这种情况下）：&nbsp; &nbsp;SELECT x.*&nbsp; &nbsp; &nbsp;FROM my_table x&nbsp;&nbsp; &nbsp; &nbsp;JOIN&nbsp; &nbsp; &nbsp; &nbsp; ( SELECT MIN(transaction_id) transaction_id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM my_table&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BY vendor_id&nbsp; &nbsp; &nbsp; &nbsp; ) y&nbsp; &nbsp; &nbsp; &nbsp;ON y.transaction_id = x.transaction_id最后，我们再次将上述所有内容进行 OUTER JOIN 回表，以获取不满足条件的行 ( b.vendor_id = a.vendor_id AND b.currency = a.currency WHERE b.transaction_id IS NULL)。SELECT a.*&nbsp;&nbsp; &nbsp; &nbsp;, b.transaction_id&nbsp; FROM my_table a&nbsp; LEFT&nbsp;&nbsp; JOIN&nbsp;&nbsp; &nbsp; &nbsp;( SELECT x.*&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM my_table x&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;JOIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( SELECT MIN(transaction_id) transaction_id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM my_table&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BY vendor_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON y.transaction_id = x.transaction_id&nbsp; &nbsp; &nbsp;) b&nbsp;&nbsp; &nbsp; ON b.vendor_id = a.vendor_id&nbsp;&nbsp; &nbsp;AND b.currency = a.currency;+----------------+-----------+----------+----------------+| transaction_id | vendor_id | currency | transaction_id |+----------------+-----------+----------+----------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101 |&nbsp; &nbsp; &nbsp; &nbsp;201 | USD&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101 ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1102 |&nbsp; &nbsp; &nbsp; &nbsp;202 | EUR&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1102 ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1103 |&nbsp; &nbsp; &nbsp; &nbsp;203 | CHF&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1103 ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1104 |&nbsp; &nbsp; &nbsp; &nbsp;202 | EUR&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1102 ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1105 |&nbsp; &nbsp; &nbsp; &nbsp;204 | HUF&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1105 ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1106 |&nbsp; &nbsp; &nbsp; &nbsp;202 | USD&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL | <-- We only&nbsp; want|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1107 |&nbsp; &nbsp; &nbsp; &nbsp;204 | USD&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL | <-- these 'NULL' rows+----------------+-----------+----------+----------------+