drop table Bugs;
CREATE TABLE `Bugs` (
`bug_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`reported_by` int,
`date_reported` date,
PRIMARY KEY (`bug_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
;
insert into Bugs values (1234,1,'2009-12-19'), (2248,2,'2010-06-01'), (3456,2,'2010-02-16'), (4077,3,'2010-02-10'), (5150,5,'2010-02-16'), (5678,6,'2010-01-01'), (8063,4,'2009-11-09');
drop table Accounts;
create table Accounts (
account_id int,
account_name varchar(100),
PRIMARY KEY (`account_id`)
);
insert into Accounts values (1 ,'Fran'), (2 ,'Ollie'), (3 ,'Kukla'), (4 ,'hironomiu'), (5 ,'hoge'), (6 ,'fuga');
drop table BugsProducts;
create table `BugsProducts`(
`bug_id`bigint(20) unsigned NOT NULL,
product_id int,
product_name varchar(100)
);
insert into BugsProducts values
(1234,1,'Open RoundFile'), (2248,1,'Open RoundFile'), (3456,2,'Visual TurboBuilder'), (4077,2,'Visual TurboBuilder'), (5150,2,'Visual TurboBuilder'), (5678,3,'Reconsider'), (8063,3,'Reconsider');
- データの確認
select * from Bugs JOIN BugsProducts USING(bug_id);
- 14.2.1も含む
SELECT product_id, MAX(date_reported) AS latest FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;
SELECT product_id, MAX(date_reported) AS latest,bug_id FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;
- product_nameで同じことを試す
SELECT product_name, MAX(date_reported) AS latest FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_name;
SELECT product_name, MAX(date_reported) AS latest ,bug_id FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_name;
SELECT product_id, MAX(date_reported) AS latest, MIN(date_reported) AS earliest, bug_id FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;
SELECT product_name, MAX(date_reported) AS latest, MIN(date_reported) AS earliest, bug_id FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_name;
MySQLの以下の設定
set sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT b.reported_by, a.account_name FROM Bugs b JOIN Accounts a ON (b.reported_by = a.account_id) GROUP BY b.reported_by;
SELECT b.reported_by, a.account_name FROM Bugs b JOIN Accounts a ON (b.reported_by = a.account_id) GROUP BY b.reported_by,a.account_name;
SELECT b.reported_by, a.account_name FROM Bugs b JOIN Accounts a ON (b.reported_by = a.account_id) ;
SELECT product_id, MAX(date_reported) AS latest FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id FROM Bugs b1 JOIN BugsProducts bp1 USING (bug_id) WHERE NOT EXISTS
(SELECT * FROM Bugs b2 JOIN BugsProducts bp2 USING (bug_id) WHERE bp1.product_id = bp2.product_id AND b1.date_reported < b2.date_reported);
insert into Bugs values (8888,2,'2010-02-17');
insert into BugsProducts values (8888,2,'Visual TurboBuilder');
SELECT m.product_id, m.latest, b1.bug_id FROM Bugs b1 JOIN BugsProducts bp1 USING (bug_id)
JOIN (SELECT bp2.product_id, MAX(b2.date_reported) AS latest FROM Bugs b2 JOIN BugsProducts bp2 USING (bug_id) GROUP BY bp2.product_id) m
ON (bp1.product_id = m.product_id AND b1.date_reported = m.latest);
SELECT m.product_id, m.latest, MAX(b1.bug_id) AS latest_bug_id FROM Bugs b1 JOIN
(SELECT product_id, MAX(date_reported) AS latest FROM Bugs b2 JOIN BugsProducts USING (bug_id) GROUP BY product_id) m
ON (b1.date_reported = m.latest) GROUP BY m.product_id, m.latest;
-- 余談 相関サブクエリ
SELECT bp1.product_id, b1.date_reported, b1.bug_id FROM Bugs b1 JOIN BugsProducts bp1 USING (bug_id)
where b1.date_reported = (SELECT MAX(b2.date_reported) AS latest FROM Bugs b2 JOIN BugsProducts bp2 USING (bug_id) where bp1.product_id = bp2.product_id GROUP BY bp2.product_id)
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id
FROM Bugs b1 JOIN BugsProducts bp1 ON (b1.bug_id = bp1.bug_id)
LEFT OUTER JOIN (Bugs AS b2 JOIN BugsProducts AS bp2 ON (b2.bug_id = bp2.bug_id))
ON (bp1.product_id = bp2.product_id AND (b1.date_reported < b2.date_reported
OR b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id))
WHERE b2.bug_id IS NULL;
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id
FROM Bugs b1 JOIN BugsProducts bp1 USING(bug_id)
LEFT OUTER JOIN (Bugs AS b2 JOIN BugsProducts AS bp2 USING(bug_id))
ON (bp1.product_id = bp2.product_id AND (b1.date_reported < b2.date_reported
OR b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id))
WHERE b2.bug_id IS NULL;
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id,b2.bug_id
FROM Bugs b1 JOIN BugsProducts bp1 USING(bug_id)
LEFT OUTER JOIN (Bugs AS b2 JOIN BugsProducts AS bp2 USING(bug_id))
ON (bp1.product_id = bp2.product_id AND (b1.date_reported < b2.date_reported
OR b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id));
SELECT product_id, MAX(date_reported) AS latest, MAX(bug_id) AS latest_bug_id FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;
SELECT product_id, MAX(date_reported) AS latest, GROUP_CONCAT(bug_id) AS bug_id_list FROM Bugs JOIN BugsProducts USING (bug_id) GROUP BY product_id;