多个联接语句未返回预期结果
我必须编写一个具有以下要求的查询:
I have to write a query with the following requirements:
查询应返回所有列表 名为的客户的输入值 史蒂夫"以及显示的每个日期(如果 可用)的最新状态详细信息 那个日期.
The query should return a list of all entry values for a customer named "Steve" and for each date shown (if available) the most recent status detail for that date.
客户表
CustomerID | CustomerName
1 | Steve
2 | John
条目表
CustomerID | EntryDate | EntryValue
1 | 5/4/2010 | 200.0
1 | 4/4/2010 | 100.0
1 | 3/4/2010 | 150.0
1 | 2/4/2010 | 170.0
2 | 5/4/2010 | 220.0
状态表
CustomerID | StatusDate | Detail
1 | 5/28/2010 | D
1 | 4/24/2010 | S
1 | 4/5/2010 | P
1 | 2/28/2010 | A
预期输出为:
CustomerName | Date | OrderCost | Detail
Steve | 5/4/2010 | 200.0 | S
Steve | 4/4/2010 | 100.0 | A
Steve | 3/4/2010 | 75.0 | A
Steve | 3/4/2010 | 75.0 | <null>
我认为预期输出可能是错误的，实际上应该是:
I think that the expected output may be wrong and it should actually be:
CustomerName | Date | OrderCost | Detail
Steve | 5/4/2010 | 200.0 | S
Steve | 4/4/2010 | 100.0 | A
Steve | 3/4/2010 | 150.0 | A
Steve | 2/4/2010 | 170.0 | <null>
鉴于此要求，我不明白为什么3/4/2010日期会出现两次，而第二次出现详细信息".我写了以下查询:
Given the requirement, I don't understand why the 3/4/2010 date would occur twice and the second time it would have a Detail. I wrote the following query:
我写了以下查询:
SELECT Customers.CustomerName, Entries.EntryDate, Entries.EntryValue, Status.Detail
FROM Customers
INNER JOIN Entries ON Customers.CustomerID = Entries.CustomerID
LEFT OUTER JOIN Status ON Status.CustomerID = Customers.CustomersID AND Status.StatusDate <= Entries.EntryDate
WHERE (Customers.CustomerName = 'Steve')
我的查询结果是这样:
CustomerName| EntryDate | EntryValue | Detail
Steve | 5/4/2010 | 200.00 | S
Steve | 5/4/2010 | 200.00 | P
Steve | 5/4/2010 | 200.00 | A
Steve | 4/4/2010 | 100.00 | A
Steve | 3/4/2010 | 150.00 | A
Steve | 2/4/2010 | 170.00 | NULL
有人暗示我在这里做错了什么吗?我不知道...
Any hints on what I'm doing wrong here? I can't figure it out...
更新 我已将订单更改为输入，因此不会给我们造成太大的困扰.
Update I've changed the order to an entry so it doesn't confuse us that much.
由于状态表中的许多行都满足第二个JOIN条件(例如，有3个statusDate早于5/4)，因此您获得的结果超出了预期因此此日期在结果集中出现3次.
You are getting more results than you expect because the second JOIN condition is satisfied by many rows in the statuses table (e.g. There are 3 statusDates earlier than 5/4 so this date appears 3 times in the result set).
您需要加入状态表，但只能获得一个匹配项(最新的).这可以通过多种方式完成，AFIAK通常使用子查询.我认为您的情况相当复杂-我使用了一个临时表.希望对您有帮助...(我目前没有数据库可以对此进行测试，希望不会有愚蠢的语法错误).
You need to JOIN statuses table, but get only one match (the latest). This can be done in several ways, AFAIK usually with a sub query. I think your case is rather complicated - I used a temp table. Hope it helps... (I currently don't have a DB to test this on, hopefully there are no silly syntax errors).
DROP TABLE IF EXISTS temp;
CREATE TABLE temp AS -- This temp table is basically the result set you got
(SELECT c.CustomerName, e.EntryDate, e.EntryValue, s.Detail, s.StatusDate
FROM Customers c
INNER JOIN Entires e ON c.CustomerID = e.CustomerID
LEFT OUTER JOIN Status s ON s.CustomerID = c.CustomersID
AND s.StatusDate <= e.EntryDate
WHERE (c.CustomerName = 'Steve')
);
SELECT t.CustomerName, t.EntryDate, t.EntryValue, t.Detail
FROM temp t
WHERE t.StatusDate = (SELECT MAX(t2.StatusDate)
FROM temp t2
WHERE t2.EntryDate = t.EntryDate);
为了避免创建临时表，我相信这会起作用(请尝试并告诉我！)
SELECT t.CustomerName, t.EntryDate, t.EntryValue, t.Detail
FROM (SELECT c.CustomerName, e.EntryDate, e.EntryValue, s.Detail, s.StatusDate
FROM Customers c
INNER JOIN Entries e ON c.CustomerID = e.CustomerID
LEFT OUTER JOIN Status s ON s.CustomerID = c.CustomersID
AND s.StatusDate <= e.EntryDate
WHERE c.CustomerName = 'Steve') AS t
WHERE t.StatusDate = (SELECT MAX(t2.StatusDate)
FROM temp t2
WHERE t2.EntryDate = t.EntryDate);