我有2个表,一个是客户列表,另一个是这些客户的历史和将来交货日期的列表。
创建示例表的便捷代码:
CREATE TABLE Customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
INSERT INTO Customers (name) VALUES ('David');
INSERT INTO Customers (name) VALUES ('John');
INSERT INTO Customers (name) VALUES ('Anne');
CREATE TABLE Orders (
id INTEGER,
item TEXT,
delivery_date TEXT
);
INSERT INTO Orders VALUES ( 1, 'gopro' , '2016-04-05');
INSERT INTO Orders VALUES ( 1,'car', '2015-12-30');
INSERT INTO Orders VALUES ( 1,'watch', '2015-10-20');
INSERT INTO Orders VALUES ( 2, 'laptop', '2016-04-15');
INSERT INTO Orders VALUES ( 3, 'car', '2016-01-15');
INSERT INTO Orders VALUES ( 3,'cup', '2015-11-30');
我需要创建一个SQL语句来显示所有客户及其最近和下一个交货日期。
(类似-SELECT *,来自具有最新和下一个项目以及来自订单的日期的客户。)
预期结果表
Name, Most Recent Item, Recent Date, Next Item, Next Date
David, car, 2015-12-30, gopro, 2016-04-05
John, NULL, NULL, laptop, 2016-04-15
Anne, car, 2016-01-15, NULL, NULL
但是我可以用一个语句得到的最接近的是:
SELECT
(SELECT o.item from Orders WHERE delivery_date < date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date < date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Delivery_Date,
(SELECT o.item from Orders WHERE delivery_date >= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date >= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Delivery_Date,
c.*
FROM (Orders o INNER JOIN Customers c ON c.id = o.id)
GROUP BY c.name
ORDER BY c.id;
而实际的结果是,因为这:
Last_Item_Ordered Last_Delivery_Date Next_Item_Ordered Next_Delivery_Date id name
watch 2015-10-20 watch 2015-10-20 1 David
laptop 2016-04-15 laptop 2016-04-15 2 John
cup 2015-11-30 cup 2015-11-30 3 Anne
今天的日期是2016年2月21日,因此上述客户将来的结果预计某些值将为NULL。
如果有人可以帮助,将不胜感激。谢谢
您可以使用以下查询:
SELECT (SELECT o.item
from Orders AS o
WHERE o.id = c.id AND delivery_date < date('now')
ORDER BY delivery_date DESC LIMIT 1) as Last_Item_Ordered,
(SELECT o.delivery_date
from Orders AS o
WHERE o.id = c.id AND delivery_date < date('now')
ORDER BY delivery_date DESC LIMIT 1) as Last_Delivery_Date,
(SELECT o.item
from Orders AS o
WHERE o.id = c.id AND delivery_date >= date('now')
ORDER BY delivery_date ASC LIMIT 1) as Next_Item_Ordered,
(SELECT o.delivery_date
from Orders AS o
WHERE o.id = c.id AND delivery_date >= date('now')
ORDER BY delivery_date ASC LIMIT 1) as Next_Delivery_Date,
c.*
FROM Customers c
ORDER BY c.id;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句