CREATE TABLE mesurer_dude
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT NOT NULL
);
CREATE TABLE mesurements
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
mesure_date DATETIME NOT NULL,
mesure_value INT NOT NULL,
mesurer_dude_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (mesurer_dude_id) REFERENCES mesurer_dude(id)
);
INSERT INTO mesurer_dude VALUES (default, "John", 42), (default, "Jane", 69);
INSERT INTO mesurements VALUES (default, NOW(), 10, 1), (default, DATE_ADD(NOW(), INTERVAL 1 DAY), 20, 1), (default, DATE_ADD(NOW(), INTERVAL 2 DAY), 25, 1), (default, DATE_ADD(NOW(), INTERVAL 3 DAY), 15, 1), (default, DATE_ADD(NOW(), INTERVAL 4 DAY), 12, 2);
查询#1
-- get the last 30 mesurements of john
SELECT *
FROM mesurer_dude d
INNER JOIN mesurements m
ON d.id=m.mesurer_dude_id
WHERE d.name = 'John'
ORDER BY m.mesure_date DESC
LIMIT 30;
1条答案
按热度按时间zysjyyx41#
为此,我将使用另一个表,并使用外键来链接这两个表。
例如:
架构(mysql v5.7)
查询#1
结果:
db fiddle视图