我对SQL有点麻烦,我使用MySQL。
我有两个表:
#Table1:刮擦
id address last_price m2 date_last_price
======================================================
id_1234 NY 100000 100 2017-11-27
id_5678 Madrid 90000 120 2017-11-27
#Table2:历史价格
id price date
===============================
id_1234 100000 2017-11-27
id_5678 90000 2017-11-27
id_1234 120000 2017-11-28
id_5678 90000 2017-11-28
id_1234 115000 2017-11-29
id_5678 95000 2017-11-29
我希望在表 1 中插入/更新表 2 中最后一个日期的价格。
结果必须如下
#结果
id address last_price m2 date_last_price
======================================================
id_1234 NY 115000 100 2017-11-29
id_5678 Madrid 95000 120 2017-11-29
你能帮我解决这个问题吗?
#创建这个例子的查询
#Table 1
CREATE TABLE IF NOT EXISTS scrape(
id varchar(50),
address varchar(50),
last_price int,
m2 int,
date_last_price date
);
INSERT INTO scrape (id,address,last_price,m2,date_last_price)
VALUES('id_1234','NY',100000,100,'2017-11-27'),
('id_5678','Madrid',90000,120,'2017-11-27');
#Table 2
CREATE TABLE IF NOT EXISTS historic_price(
id varchar(50),
price int,
`date` date
);
INSERT INTO historic_price(id,price,date)
VALUES('id_1234',100000,'2017-11-27'),
('id_5678',90000,'2017-11-27'),
('id_1234',120000,'2017-11-28'),
('id_5678',90000,'2017-11-28'),
('id_1234',115000,'2017-11-29'),
('id_5678',95000,'2017-11-29');
使用UPDATE
with 的另一种解决方案JOIN
:
UPDATE scrape S
INNER JOIN (
SELECT id, `date`, price
FROM historic_price HP
WHERE `date` = (SELECT MAX(date) FROM historic_price WHERE id = HP.id GROUP BY id)
) A
ON S.id = A.id
SET
S.date_last_price = A.date
,S.last_price = A.price;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句