I have 2 tables, orders and transactions. The orders table has 1 row per order, and the transactions table has multiple rows per order.
我有2个表，订单和交易。订单表每个订单有1行，而事务表每个订单有多行。
I want the check which orders have a different OrderDate compared to the FIRST instance of a transaction row with that same OrderNumber. I have tried the query below. But look at some of the records coming back - it's still returning some rows where the first ever transactions did actually take place on the same date as the OrderDate. It seems to still be querying on the second, third, fourth etc transactions that exist against that OrderNumber. I want it to only query on the first - hence I used ORDER BY Id LIMIT 0,1 - but it's not right.
我想检查哪些订单具有不同的OrderDate与具有相同OrderNumber的事务行的FIRST实例相比较。我已经尝试过以下查询。但是看一下回来的一些记录 - 它仍然会返回一些行，其中第一次交易确实发生在与OrderDate相同的日期。它似乎仍在查询针对该OrderNumber存在的第二，第三，第四等交易。我希望它只对第一个进行查询 - 因此我使用了ORDER BY Id LIMIT 0,1 - 但它不对。
SELECT
o.ordernumber,
Date(OrderDate),
Date((
SELECT
TransDate
FROM
transactions
WHERE
OrderNumber = o.OrderNumber
ORDER BY
Id
LIMIT 0,
1
)) AS TransDate
FROM
transactions AS t
JOIN orders AS o ON o.OrderNumber = t.OrderNumber
WHERE
STATUS = 'booking'
AND date(OrderDate) != Date(TransDate)
ORDER BY
o.OrderDate ASC
123456789 2015-04-13 2015-04-13
123456789 2015-04-13 2015-04-13
123456788 2015-09-28 2015-10-05
123456788 2015-09-28 2015-10-05
The first row shouldn't be included!
不应该包括第一行！
1 个解决方案
#1
0
The problem is that you have the transactions table in the from clause. You don't need it. However, to get the value for comparison, you can't reference the date defined in the select. You can use a MySQL extension:
问题是你在from子句中有transaction表。你不需要它。但是，要获取比较值，您不能引用select中定义的日期。您可以使用MySQL扩展：
SELECT o.ordernumber, Date(o.OrderDate),
(SELECT date(TransDate)
FROM transactions t
WHERE t.OrderNumber = o.OrderNumber
ORDER BY t.Id
LIMIT 0, 1
) AS TransDate
FROM orders o
WHERE o.STATUS = 'booking'
HAVING date(OrderDate) <> TransDate
ORDER BY o.OrderDate ASC;
#1
0
The problem is that you have the transactions table in the from clause. You don't need it. However, to get the value for comparison, you can't reference the date defined in the select. You can use a MySQL extension:
问题是你在from子句中有transaction表。你不需要它。但是，要获取比较值，您不能引用select中定义的日期。您可以使用MySQL扩展：
SELECT o.ordernumber, Date(o.OrderDate),
(SELECT date(TransDate)
FROM transactions t
WHERE t.OrderNumber = o.OrderNumber
ORDER BY t.Id
LIMIT 0, 1
) AS TransDate
FROM orders o
WHERE o.STATUS = 'booking'
HAVING date(OrderDate) <> TransDate
ORDER BY o.OrderDate ASC;