问一句sql语句
有两个表:
CREATE TABLE Customers ( -- df: mult=1
custid INTEGER PRIMARY KEY,
cname VARCHAR(14), -- df: word=names.txt sub=power rate=0.016
country CHAR(3) -- df: word=:GBR,USA,ITA,CHN,JPN
);
CREATE TABLE Orders ( -- df: mult=3
ordid INTEGER PRIMARY KEY,
odate DATE, -- df: start=2015-01-01 end=2016-09-30
ocust INTEGER NOT NULL REFERENCES Customers -- df: null=0
);
现在要获得customer的ID,NAME以及这个customer最近一条订单的DATE
我的写法是
select c.custid, c.cname, o.odate
from customers c, orders o
where c.custid = o.ocust
and o.odate in (select m_date from (select ocust, max(odate) as m_date
from orders
group by ocust) as table1)
但是我得出来的数据比答案要多,求解