MySQL最大的每个组的麻烦 [英] MySQL greatest-n-per-group trouble
问题描述
我使用的是MySQL数据库，并且有一个关于计算机应用程序的基本博客类型系统。表格如下所示：
POSTS
post_id
post_created
post_type - 可以是文章，评论，功能，无论
post_status - 'a'已批准或'd'草稿
APPS
app_id
app_name
app_platform - Windows，linux，unix等。
APP_TO_POST - 链接我的帖子到其相关应用程序
atp_id
atp_app_id
atp_post_id
< m使用以下基本查询来拉取名称为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 2009年10月20日1
Photoshop Windows Dec. 1，2009 3
Photoshop Macintosh 2009年11月10日2
任何人都可以借更改该查询以仅仅拉取每个应用程序平台的最近的文章？例如，我想我的结果看起来像这样：
app_name app_platform post_created post_id
Photoshop Windows Dec 。1，2009 3
Photoshop Macintosh 2009年11月10日2
'Photoshop Windows'文章，因为它不是最新的。
MAX（post_created）和 GROUP BY app_platform 我的结果并不总是正确分组。从我的理解我需要执行某种子连接的子查询？
code> JOIN ，我建议先创建 VIEW ：
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';
然后你可以使用NULL自连接：
SELECT a1.app_name，a1.app_platform，a1.post_created，a1.post_id
来自文章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）;
结果：
code> + ----------- + -------------- + ----------------- ---- + --------- +
| app_name | app_platform |后处理| post_id |
+ ----------- + -------------- + ------------------ --- + --------- +
| Photoshop | Macintosh | 2010-10-06 08:00:00 | 4 |
| Photoshop | Windows | 2010-10-06 09:00:00 | 5 |
+ ----------- + -------------- + ------------------ --- + --------- +
集合中的2行（0.00秒）
作为旁注，一般来说，您不需要代理键，代表您的联结表。您可以设置一个复合主键（最好是引用表的外键）：
CREATE TABLE app_to_post b $ b 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;
Hey everyone. I believe this is a 'greatest-n-per-group' question but even after looking at several questions on StackOverflow, I'm unsure how to apply this to my situation...
I'm using a MySQL database and have a basic blog-type system set up about Computer Applications... The tables look like this:
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
I'm using the following basic query to pull all articles for the application with the name 'Photoshop' where the post type is an 'Article' and the status of the article is 'a' for approved:
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'
Which gets me these expected results:
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
Would anyone be able to lend a hand on how I could alter that query to only pull the most recent article per application platform? So for example, I'd like my results to look like this:
app_name app_platform post_created post_id
Photoshop Windows Dec. 1, 2009 3
Photoshop Macintosh Nov. 10th, 2009 2
And omit one of the 'Photoshop Windows' articles because it isn't the most recent one.
If I simply tack on a MAX(post_created) and a GROUP BY app_platform my results don't always group correctly. From how I understand it I need to perform some kind of inner join of a sub query?
Since you have plenty of JOINs, I suggest creating a VIEW first:
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';
Then you can use a NULL-self-join:
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;
Test case:
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);
Result:
+-----------+--------------+---------------------+---------+
| 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)
As a side-note, in general you don't need a surrogate key for your junction table. You might as well set up a composite primary key (and ideally foreign keys to the referenced tables):
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;
这篇关于MySQL最大的每个组的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
