大家好。我相信这是一个“每个小组最重要”的问题，但即使在看了StackOverflow上的几个问题之后，我也不确定如何将这个问题应用到我的情况中……
我正在使用一个mysql数据库，并有一个关于计算机应用程序的基本blog类型系统…桌子看起来像这样：
POSTS
post_id
post_created
post_type -- could be article, review, feature, whatever
post_status -- 'a' approved or 'd' for draft
APPS
app_id
app_name
app_platform -- Windows, linux, unix, etc..
APP_TO_POST -- links my posts to its relevant application
atp_id
atp_app_id
atp_post_id
我正在使用以下基本查询为应用程序提取名为“photoshop”的所有文章，其中文章类型为“文章”，文章状态为“A”表示已批准：
SELECT apps.app_name, apps.app_platform, posts.post_created, posts.post_id
FROM apps
JOIN app_to_post ON app_to_post.atp_app_id = apps.app_id
JOIN posts ON app_to_post.atp_post_id = posts.post_id
WHERE apps.app_name = 'Photoshop'
AND
posts.post_type = 'Article'
AND
posts.post_status = 'a'
这让我得到了这些预期的结果：
app_name app_platform post_created post_id
Photoshop Windows Oct. 20th, 2009 1
Photoshop Windows Dec. 1, 2009 3
Photoshop Macintosh Nov. 10th, 2009 2
有谁能帮我介绍一下如何修改查询，使其只针对每个应用程序平台提取最新的文章吗？例如，我希望我的结果如下：
app_name app_platform post_created post_id
Photoshop Windows Dec. 1, 2009 3
Photoshop Macintosh Nov. 10th, 2009 2
省略其中一篇文章，因为它不是最新的。
如果我只是简单地加上一个
'Photoshop Windows'和一个MAX(post_created)我的结果并不总是正确分组。根据我的理解，我需要执行子查询的某种内部连接？ 最佳答案
由于您有大量的JOINs，我建议首先创建一个VIEWs：
CREATE VIEW articles AS
SELECT a.app_name, a.app_platform, p.post_created, p.post_id
FROM apps a
JOIN app_to_post ap ON ap.atp_app_id = a.app_id
JOIN posts p ON ap.atp_post_id = p.post_id
WHERE p.post_type = 'Article' AND p.post_status = 'a';
然后可以使用空的自联接：
SELECT a1.app_name, a1.app_platform, a1.post_created, a1.post_id
FROM articles a1
LEFT JOIN articles a2 ON
a2.app_platform = a1.app_platform AND a2.post_created > a1.post_created
WHERE a2.post_id IS NULL;
测试用例：
CREATE TABLE posts (
post_id int,
post_created datetime,
post_type varchar(30),
post_status char(1)
);
CREATE TABLE apps (
app_id int,
app_name varchar(40),
app_platform varchar(40)
);
CREATE TABLE app_to_post (
atp_id int,
atp_app_id int,
atp_post_id int
);
INSERT INTO posts VALUES (1, '2010-10-06 05:00:00', 'Article', 'a');
INSERT INTO posts VALUES (2, '2010-10-06 06:00:00', 'Article', 'a');
INSERT INTO posts VALUES (3, '2010-10-06 07:00:00', 'Article', 'a');
INSERT INTO posts VALUES (4, '2010-10-06 08:00:00', 'Article', 'a');
INSERT INTO posts VALUES (5, '2010-10-06 09:00:00', 'Article', 'a');
INSERT INTO apps VALUES (1, 'Photoshop', 'Windows');
INSERT INTO apps VALUES (2, 'Photoshop', 'Macintosh');
INSERT INTO app_to_post VALUES (1, 1, 1);
INSERT INTO app_to_post VALUES (1, 1, 2);
INSERT INTO app_to_post VALUES (1, 2, 3);
INSERT INTO app_to_post VALUES (1, 2, 4);
INSERT INTO app_to_post VALUES (1, 1, 5);
结果：
+-----------+--------------+---------------------+---------+
| app_name | app_platform | post_created | post_id |
+-----------+--------------+---------------------+---------+
| Photoshop | Macintosh | 2010-10-06 08:00:00 | 4 |
| Photoshop | Windows | 2010-10-06 09:00:00 | 5 |
+-----------+--------------+---------------------+---------+
2 rows in set (0.00 sec)
另外，一般来说，你的surrogate key不需要junction table。您最好设置一个复合主键（最好是引用表的外键）：
CREATE TABLE app_to_post (
atp_app_id int,
atp_post_id int,
PRIMARY KEY (atp_app_id, atp_post_id),
FOREIGN KEY (atp_app_id) REFERENCES apps (app_id),
FOREIGN KEY (atp_post_id) REFERENCES posts (post_id)
) ENGINE=INNODB;
关于sql - MySQL最大的每组麻烦，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3869571/