I have this problem joining across multiple tables and grouping by columns on those tables. I spent a few hours at work today with no success, I think I have a misunderstanding of joins or multiple column grouping.
我有这个问题加入多个表并在这些表上按列分组。我今天在工作上花了几个小时没有成功,我想我对连接或多列分组有误解。
I have included an SQL dump at the end of this post so you can see the database for yourself - it'll probably explain better than my post is about to..!
我在这篇文章的末尾包含了一个SQL转储,因此您可以自己查看数据库 - 它可能比我的帖子更好地解释......!
Basically, it's a WordPress blog which needs the three 'latest posts' pulled out of the database. The complication is only one post from each category - so you can't pull out three latest posts and have them all in the same category.
基本上,这是一个WordPress博客,需要从数据库中取出三个“最新帖子”。复杂功能只是每个类别中的一个帖子 - 因此您无法提取三个最新帖子并将它们全部归入同一类别。
I thought this would be a simple 'group by' on the post id, and the category id (term_id), followed by a LIMIT 3?
我认为这将是post id上的一个简单的'group by',以及类别id(term_id),然后是LIMIT 3?
In this example though I'll try and pull out 5 latest articles from unique categories.
在这个例子中,虽然我将尝试从独特的类别中提取5篇最新文章。
So there are four tables:
所以有四个表:
- wp_posts, which contains the blog posts. I only want to return post_type of 'post' from this table.
- wp_terms, this defines 'key words'.
- wp_term_taxonomy, a table which defines what each keyword is - e.g., is it a category.
- wp_term_relationships, which links the 'key words' in wp_terms to posts.
wp_posts,其中包含博客文章。我只想从这个表中返回'post'的post_type。
wp_terms,这定义了'关键词'。
wp_term_taxonomy,一个定义每个关键字是什么的表 - 例如,它是一个类别。
wp_term_relationships,它将wp_terms中的“关键词”链接到帖子。
You will see the tables and demo data below. Quickly though, what I thought - clearly incorrectly - would be a query like this:
您将在下面看到表格和演示数据。但很快,我认为 - 显然是错误的 - 将是这样的查询:
SELECT * FROM wp_posts
INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.id
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
INNER JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_type = 'post'
GROUP BY wp_term_taxonomy.term_id wp_posts.id,
ORDER BY post_date DESC
LIMIT 5
With the following data in the tables, I would expect the above query to return Training Puppy, Bathroom Makeover, Post Nachos and Sewing Buttons. The last two would get ignored as they are duplicate post ids or term ids. But instead, I get those four followed by Changing Fuses.
通过表格中的以下数据,我希望上面的查询能够返回Training Puppy,Bathroom Makeover,Post Nachos和Sewing Buttons。最后两个将被忽略,因为它们是重复的后id或术语ID。但相反,我得到了这四个,然后是改变保险丝。
What is the correct way to query for the data that I want?
查询我想要的数据的正确方法是什么?
table wp_posts
+----+-------------------+---------------------+-----------+
| id | post_title | post_date | post_type |
+----+-------------------+---------------------+-----------+
| 5 | Boiling Eggs | 2009-06-11 22:57:07 | post |
| 6 | Boiling Eggs | 2009-06-11 22:57:05 | revision |
| 2 | About | 2009-06-11 22:55:37 | page |
| 7 | Changing Fuses | 2009-06-11 22:57:19 | post |
| 8 | Changing Fuses | 2009-06-11 22:57:15 | revision |
| 9 | Sewing Buttons | 2009-06-11 22:57:28 | post |
| 10 | Sewing Buttons | 2009-06-11 22:57:25 | revision |
| 11 | Posh Nachos | 2009-06-11 22:57:35 | post |
| 12 | Posh Nachos | 2009-06-11 22:57:34 | revision |
| 13 | Bathroom Makeover | 2009-06-11 22:57:44 | post |
| 14 | Bathroom Makeover | 2009-06-11 22:57:41 | revision |
| 15 | Training Puppy | 2009-06-11 22:57:51 | post |
| 16 | Training Puppy | 2009-06-11 22:57:48 | revision |
| 17 | Boiling Eggs | 2009-06-11 22:57:07 | revision |
| 18 | Training Puppy | 2009-06-11 22:57:51 | revision |
| 19 | Bathroom Makeover | 2009-06-11 22:57:44 | revision |
| 20 | Posh Nachos | 2009-06-11 22:57:35 | revision |
+----+-------------------+---------------------+-----------+
table wp_terms
+---------+---------------+---------------+------------+
| term_id | name | slug | term_group |
+---------+---------------+---------------+------------+
| 1 | Uncategorized | uncategorized | 0 |
| 2 | Blogroll | blogroll | 0 |
| 3 | Food | foot | 0 |
| 4 | DIY | diy | 0 |
| 5 | Crafts | crafts | 0 |
| 6 | Pets | pets | 0 |
| 7 | puppy | puppy | 0 |
| 8 | dog | dog | 0 |
| 9 | training | training | 0 |
| 10 | bathroom | bathroom | 0 |
| 11 | rennovate | rennovate | 0 |
| 12 | mexican | mexican | 0 |
| 13 | snack | snack | 0 |
| 14 | fast food | fast-food | 0 |
+---------+---------------+---------------+------------+
table wp_term_taxonomy
+------------------+---------+---------------+-------------+--------+-------+
| term_taxonomy_id | term_id | taxonomy | description | parent | count |
+------------------+---------+---------------+-------------+--------+-------+
| 1 | 1 | category | | 0 | 0 |
| 2 | 2 | link_category | | 0 | 7 |
| 3 | 3 | category | | 0 | 2 |
| 4 | 4 | category | | 0 | 2 |
| 5 | 5 | category | | 0 | 1 |
| 6 | 6 | category | | 0 | 1 |
| 8 | 7 | post_tag | | 0 | 1 |
| 9 | 8 | post_tag | | 0 | 1 |
| 10 | 9 | post_tag | | 0 | 1 |
| 11 | 10 | post_tag | | 0 | 1 |
| 12 | 11 | post_tag | | 0 | 1 |
| 13 | 12 | post_tag | | 0 | 1 |
| 14 | 13 | post_tag | | 0 | 1 |
| 15 | 14 | post_tag | | 0 | 1 |
+------------------+---------+---------------+-------------+--------+-------+
table wp_term_relationships
+-----------+------------------+------------+
| object_id | term_taxonomy_id | term_order |
+-----------+------------------+------------+
| 1 | 2 | 0 |
| 2 | 2 | 0 |
| 3 | 2 | 0 |
| 4 | 2 | 0 |
| 5 | 2 | 0 |
| 6 | 2 | 0 |
| 7 | 2 | 0 |
| 6 | 1 | 0 |
| 18 | 1 | 0 |
| 8 | 1 | 0 |
| 7 | 4 | 0 |
| 10 | 1 | 0 |
| 9 | 5 | 0 |
| 12 | 1 | 0 |
| 11 | 3 | 0 |
| 14 | 1 | 0 |
| 13 | 4 | 0 |
| 16 | 1 | 0 |
| 15 | 6 | 0 |
| 17 | 1 | 0 |
| 5 | 3 | 0 |
| 15 | 8 | 0 |
| 15 | 9 | 0 |
| 15 | 10 | 0 |
| 19 | 1 | 0 |
| 13 | 11 | 0 |
| 13 | 12 | 0 |
| 20 | 1 | 0 |
| 11 | 13 | 0 |
| 11 | 14 | 0 |
| 11 | 15 | 0 |
+-----------+------------------+------------+
SQL Dump
CREATE TABLE IF NOT EXISTS `wp_posts` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`post_author` bigint(20) unsigned NOT NULL default '0',
`post_date` datetime NOT NULL default '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
`post_content` longtext NOT NULL,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`post_status` varchar(20) NOT NULL default 'publish',
`comment_status` varchar(20) NOT NULL default 'open',
`ping_status` varchar(20) NOT NULL default 'open',
`post_password` varchar(20) NOT NULL default '',
`post_name` varchar(200) NOT NULL default '',
`to_ping` text NOT NULL,
`pinged` text NOT NULL,
`post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
`post_content_filtered` text NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL default '0',
`guid` varchar(255) NOT NULL default '',
`menu_order` int(11) NOT NULL default '0',
`post_type` varchar(20) NOT NULL default 'post',
`post_mime_type` varchar(100) NOT NULL default '',
`comment_count` bigint(20) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `wp_posts`
--
INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(5, 1, '2009-06-11 22:57:07', '2009-06-11 21:57:07', 'fadgagadf', 'Boiling Eggs', '', 'publish', 'open', 'open', '', 'boiling-eggs', '', '', '2009-06-11 22:58:03', '2009-06-11 21:58:03', '', 0, 'http://project1.searbe.co.uk/?p=5', 0, 'post', '', 0),
(6, 1, '2009-06-11 22:57:05', '2009-06-11 21:57:05', '', 'Boiling Eggs', '', 'inherit', 'open', 'open', '', '5-revision', '', '', '2009-06-11 22:57:05', '2009-06-11 21:57:05', '', 5, 'http://project1.searbe.co.uk/?p=6', 0, 'revision', '', 0),
(2, 1, '2009-06-11 22:55:37', '2009-06-11 21:55:37', 'This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from. You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress.', 'About', '', 'publish', 'open', 'open', '', 'about', '', '', '2009-06-11 22:55:37', '2009-06-11 21:55:37', '', 0, 'http://project1.searbe.co.uk/?page_id=2', 0, 'page', '', 0),
(7, 1, '2009-06-11 22:57:19', '2009-06-11 21:57:19', 'fgsgfafga', 'Changing Fuses', '', 'publish', 'open', 'open', '', 'changing-fuses', '', '', '2009-06-11 22:57:19', '2009-06-11 21:57:19', '', 0, 'http://project1.searbe.co.uk/?p=7', 0, 'post', '', 0),
(8, 1, '2009-06-11 22:57:15', '2009-06-11 21:57:15', '', 'Changing Fuses', '', 'inherit', 'open', 'open', '', '7-revision', '', '', '2009-06-11 22:57:15', '2009-06-11 21:57:15', '', 7, 'http://project1.searbe.co.uk/?p=8', 0, 'revision', '', 0),
(9, 1, '2009-06-11 22:57:28', '2009-06-11 21:57:28', 'ghsghgs', 'Sewing Buttons', '', 'publish', 'open', 'open', '', 'sewing-buttons', '', '', '2009-06-11 22:57:28', '2009-06-11 21:57:28', '', 0, 'http://project1.searbe.co.uk/?p=9', 0, 'post', '', 0),
(10, 1, '2009-06-11 22:57:25', '2009-06-11 21:57:25', '', 'Sewing Buttons', '', 'inherit', 'open', 'open', '', '9-revision', '', '', '2009-06-11 22:57:25', '2009-06-11 21:57:25', '', 9, 'http://project1.searbe.co.uk/?p=10', 0, 'revision', '', 0),
(11, 1, '2009-06-11 22:57:35', '2009-06-11 21:57:35', 'hhjhdh', 'Posh Nachos', '', 'publish', 'open', 'open', '', 'posh-nachos', '', '', '2009-06-11 22:59:34', '2009-06-11 21:59:34', '', 0, 'http://project1.searbe.co.uk/?p=11', 0, 'post', '', 0),
(12, 1, '2009-06-11 22:57:34', '2009-06-11 21:57:34', '', 'Posh Nachos', '', 'inherit', 'open', 'open', '', '11-revision', '', '', '2009-06-11 22:57:34', '2009-06-11 21:57:34', '', 11, 'http://project1.searbe.co.uk/?p=12', 0, 'revision', '', 0),
(13, 1, '2009-06-11 22:57:44', '2009-06-11 21:57:44', 'hjhjdhjdjdh', 'Bathroom Makeover', '', 'publish', 'open', 'open', '', 'bathroom-makeover', '', '', '2009-06-11 22:59:19', '2009-06-11 21:59:19', '', 0, 'http://project1.searbe.co.uk/?p=13', 0, 'post', '', 0),
(14, 1, '2009-06-11 22:57:41', '2009-06-11 21:57:41', '', 'Bathroom Makeover', '', 'inherit', 'open', 'open', '', '13-revision', '', '', '2009-06-11 22:57:41', '2009-06-11 21:57:41', '', 13, 'http://project1.searbe.co.uk/?p=14', 0, 'revision', '', 0),
(15, 1, '2009-06-11 22:57:51', '2009-06-11 21:57:51', 'hjhjdjdy', 'Training Puppy', '', 'publish', 'open', 'open', '', 'training-puppy', '', '', '2009-06-11 22:59:05', '2009-06-11 21:59:05', '', 0, 'http://project1.searbe.co.uk/?p=15', 0, 'post', '', 0),
(16, 1, '2009-06-11 22:57:48', '2009-06-11 21:57:48', '', 'Training Puppy', '', 'inherit', 'open', 'open', '', '15-revision', '', '', '2009-06-11 22:57:48', '2009-06-11 21:57:48', '', 15, 'http://project1.searbe.co.uk/?p=16', 0, 'revision', '', 0),
(17, 1, '2009-06-11 22:57:07', '2009-06-11 21:57:07', 'fadgagadf', 'Boiling Eggs', '', 'inherit', 'open', 'open', '', '5-revision-2', '', '', '2009-06-11 22:57:07', '2009-06-11 21:57:07', '', 5, 'http://project1.searbe.co.uk/?p=17', 0, 'revision', '', 0),
(18, 1, '2009-06-11 22:57:51', '2009-06-11 21:57:51', 'hjhjdjdy', 'Training Puppy', '', 'inherit', 'open', 'open', '', '15-revision-2', '', '', '2009-06-11 22:57:51', '2009-06-11 21:57:51', '', 15, 'http://project1.searbe.co.uk/?p=18', 0, 'revision', '', 0),
(19, 1, '2009-06-11 22:57:44', '2009-06-11 21:57:44', 'hjhjdhjdjdh', 'Bathroom Makeover', '', 'inherit', 'open', 'open', '', '13-revision-2', '', '', '2009-06-11 22:57:44', '2009-06-11 21:57:44', '', 13, 'http://project1.searbe.co.uk/?p=19', 0, 'revision', '', 0),
(20, 1, '2009-06-11 22:57:35', '2009-06-11 21:57:35', 'hhjhdh', 'Posh Nachos', '', 'inherit', 'open', 'open', '', '11-revision-2', '', '', '2009-06-11 22:57:35', '2009-06-11 21:57:35', '', 11, 'http://project1.searbe.co.uk/?p=20', 0, 'revision', '', 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_terms`
--
CREATE TABLE IF NOT EXISTS `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(200) NOT NULL default '',
`slug` varchar(200) NOT NULL default '',
`term_group` bigint(10) NOT NULL default '0',
PRIMARY KEY (`term_id`),
UNIQUE KEY `slug` (`slug`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
--
-- Dumping data for table `wp_terms`
--
INSERT INTO `wp_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES
(1, 'Uncategorized', 'uncategorized', 0),
(2, 'Blogroll', 'blogroll', 0),
(3, 'Food', 'foot', 0),
(4, 'DIY', 'diy', 0),
(5, 'Crafts', 'crafts', 0),
(6, 'Pets', 'pets', 0),
(7, 'puppy', 'puppy', 0),
(8, 'dog', 'dog', 0),
(9, 'training', 'training', 0),
(10, 'bathroom', 'bathroom', 0),
(11, 'rennovate', 'rennovate', 0),
(12, 'mexican', 'mexican', 0),
(13, 'snack', 'snack', 0),
(14, 'fast food', 'fast-food', 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_term_relationships`
--
CREATE TABLE IF NOT EXISTS `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL default '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL default '0',
`term_order` int(11) NOT NULL default '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `wp_term_relationships`
--
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES
(1, 2, 0),
(2, 2, 0),
(3, 2, 0),
(4, 2, 0),
(5, 2, 0),
(6, 2, 0),
(7, 2, 0),
(6, 1, 0),
(18, 1, 0),
(8, 1, 0),
(7, 4, 0),
(10, 1, 0),
(9, 5, 0),
(12, 1, 0),
(11, 3, 0),
(14, 1, 0),
(13, 4, 0),
(16, 1, 0),
(15, 6, 0),
(17, 1, 0),
(5, 3, 0),
(15, 8, 0),
(15, 9, 0),
(15, 10, 0),
(19, 1, 0),
(13, 11, 0),
(13, 12, 0),
(20, 1, 0),
(11, 13, 0),
(11, 14, 0),
(11, 15, 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_term_taxonomy`
--
CREATE TABLE IF NOT EXISTS `wp_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL auto_increment,
`term_id` bigint(20) unsigned NOT NULL default '0',
`taxonomy` varchar(32) NOT NULL default '',
`description` longtext NOT NULL,
`parent` bigint(20) unsigned NOT NULL default '0',
`count` bigint(20) NOT NULL default '0',
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
--
-- Dumping data for table `wp_term_taxonomy`
--
INSERT INTO `wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES
(1, 1, 'category', '', 0, 0),
(2, 2, 'link_category', '', 0, 7),
(3, 3, 'category', '', 0, 2),
(4, 4, 'category', '', 0, 2),
(5, 5, 'category', '', 0, 1),
(6, 6, 'category', '', 0, 1),
(8, 7, 'post_tag', '', 0, 1),
(9, 8, 'post_tag', '', 0, 1),
(10, 9, 'post_tag', '', 0, 1),
(11, 10, 'post_tag', '', 0, 1),
(12, 11, 'post_tag', '', 0, 1),
(13, 12, 'post_tag', '', 0, 1),
(14, 13, 'post_tag', '', 0, 1),
(15, 14, 'post_tag', '', 0, 1);
2 个解决方案
#1
update: I apologize for flubbing my understanding of your original question. I skimmed it and thought it was another example of the "top N from each group" question I see so frequently.
更新:我为弄乱我对你原来问题的理解而道歉。我撇去它并认为这是我经常看到的“每组中的前N个”问题的另一个例子。
Here's a solution to your original question, which is the most recent five posts, with at most one post from each category:
这是您原始问题的解决方案,这是最近的五个帖子,每个类别最多只有一个帖子:
SELECT t.name, p1.post_title, p1.post_date
FROM wp_term_taxonomy x
INNER JOIN wp_terms t ON (t.term_id = x.term_id)
INNER JOIN wp_term_relationships r1 ON (x.term_taxonomy_id = r1.term_taxonomy_id)
INNER JOIN wp_posts p1 ON (r1.object_id = p1.id AND p1.post_type = 'post')
LEFT OUTER JOIN (wp_term_relationships r2
INNER JOIN wp_posts p2 ON (r2.object_id = p2.id AND p2.post_type = 'post'))
ON (x.term_taxonomy_id = r2.term_taxonomy_id AND p1.post_date < p2.post_date)
WHERE x.taxonomy = 'category'
AND r2.term_taxonomy_id IS NULL
GROUP BY x.term_id
ORDER BY p1.post_date DESC
LIMIT 5;
Here's the output in my test:
这是我测试中的输出:
+--------+-------------------+---------------------+
| name | post_title | post_date |
+--------+-------------------+---------------------+
| Pets | Training Puppy | 2009-06-11 22:57:51 |
| DIY | Bathroom Makeover | 2009-06-11 22:57:44 |
| Food | Posh Nachos | 2009-06-11 22:57:35 |
| Crafts | Sewing Buttons | 2009-06-11 22:57:28 |
+--------+-------------------+---------------------+
PS: Many thanks for posting the DDL and INSERT statements in your original question! Not many folks asking SQL questions do that.
PS:非常感谢您在原始问题中发布DDL和INSERT语句!没有多少人问过SQL问题。
Below is my first answer, based on my incorrect understanding of the question:
以下是我的第一个答案,基于我对该问题的错误理解:
So you want all posts such that there are fewer than five other posts with the same category term and a more recent post_date?
所以你想要所有的帖子,以便有少于五个其他帖子具有相同的类别术语和更新的post_date?
You need to join to the posts table twice:
您需要两次加入posts表:
SELECT x.*, t.*, r1.*, p1.*
FROM wp_term_taxonomy x
INNER JOIN wp_terms t ON (t.term_id = x.term_id)
INNER JOIN wp_term_relationships r1 ON (r1.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_posts p1 ON (p1.id = r1.object_id)
INNER JOIN wp_term_relationships r2 ON (r2.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_posts p2 ON (p2.id = r2.object_id AND p2.post_date > p1.post_date)
WHERE x.taxonomy = 'category'
AND p1.post_type = 'post' AND p2.post_type = 'post'
GROUP BY x.term_id, p1.id
HAVING COUNT(*) < 5;
#2
This is much simplier version but thanks to your idea bill :
这是一个更简单的版本,但感谢你的想法法案:
SELECT wt.name, wp.post_title, wp.post_date
FROM wp_term_relationships AS wtr
INNER JOIN wp_posts AS wp ON ( wtr.object_id = wp.id )
INNER JOIN wp_term_taxonomy AS wtt ON ( wtr.term_taxonomy_id = wtt.term_taxonomy_id)
INNER JOIN wp_terms AS wt ON ( wtt.term_id = wt.term_id )
WHERE wp.post_type = 'post'
and wtt.taxonomy = 'category'
group by wtt.term_id
order by wp.post_date desc;
#1
update: I apologize for flubbing my understanding of your original question. I skimmed it and thought it was another example of the "top N from each group" question I see so frequently.
更新:我为弄乱我对你原来问题的理解而道歉。我撇去它并认为这是我经常看到的“每组中的前N个”问题的另一个例子。
Here's a solution to your original question, which is the most recent five posts, with at most one post from each category:
这是您原始问题的解决方案,这是最近的五个帖子,每个类别最多只有一个帖子:
SELECT t.name, p1.post_title, p1.post_date
FROM wp_term_taxonomy x
INNER JOIN wp_terms t ON (t.term_id = x.term_id)
INNER JOIN wp_term_relationships r1 ON (x.term_taxonomy_id = r1.term_taxonomy_id)
INNER JOIN wp_posts p1 ON (r1.object_id = p1.id AND p1.post_type = 'post')
LEFT OUTER JOIN (wp_term_relationships r2
INNER JOIN wp_posts p2 ON (r2.object_id = p2.id AND p2.post_type = 'post'))
ON (x.term_taxonomy_id = r2.term_taxonomy_id AND p1.post_date < p2.post_date)
WHERE x.taxonomy = 'category'
AND r2.term_taxonomy_id IS NULL
GROUP BY x.term_id
ORDER BY p1.post_date DESC
LIMIT 5;
Here's the output in my test:
这是我测试中的输出:
+--------+-------------------+---------------------+
| name | post_title | post_date |
+--------+-------------------+---------------------+
| Pets | Training Puppy | 2009-06-11 22:57:51 |
| DIY | Bathroom Makeover | 2009-06-11 22:57:44 |
| Food | Posh Nachos | 2009-06-11 22:57:35 |
| Crafts | Sewing Buttons | 2009-06-11 22:57:28 |
+--------+-------------------+---------------------+
PS: Many thanks for posting the DDL and INSERT statements in your original question! Not many folks asking SQL questions do that.
PS:非常感谢您在原始问题中发布DDL和INSERT语句!没有多少人问过SQL问题。
Below is my first answer, based on my incorrect understanding of the question:
以下是我的第一个答案,基于我对该问题的错误理解:
So you want all posts such that there are fewer than five other posts with the same category term and a more recent post_date?
所以你想要所有的帖子,以便有少于五个其他帖子具有相同的类别术语和更新的post_date?
You need to join to the posts table twice:
您需要两次加入posts表:
SELECT x.*, t.*, r1.*, p1.*
FROM wp_term_taxonomy x
INNER JOIN wp_terms t ON (t.term_id = x.term_id)
INNER JOIN wp_term_relationships r1 ON (r1.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_posts p1 ON (p1.id = r1.object_id)
INNER JOIN wp_term_relationships r2 ON (r2.term_taxonomy_id = x.term_taxonomy_id)
INNER JOIN wp_posts p2 ON (p2.id = r2.object_id AND p2.post_date > p1.post_date)
WHERE x.taxonomy = 'category'
AND p1.post_type = 'post' AND p2.post_type = 'post'
GROUP BY x.term_id, p1.id
HAVING COUNT(*) < 5;
#2
This is much simplier version but thanks to your idea bill :
这是一个更简单的版本,但感谢你的想法法案:
SELECT wt.name, wp.post_title, wp.post_date
FROM wp_term_relationships AS wtr
INNER JOIN wp_posts AS wp ON ( wtr.object_id = wp.id )
INNER JOIN wp_term_taxonomy AS wtt ON ( wtr.term_taxonomy_id = wtt.term_taxonomy_id)
INNER JOIN wp_terms AS wt ON ( wtt.term_id = wt.term_id )
WHERE wp.post_type = 'post'
and wtt.taxonomy = 'category'
group by wtt.term_id
order by wp.post_date desc;