如何在带有MIN的SELECT中包含第三列 [英] How can I include third column in a SELECT with MIN
问题描述
在此查询中，我还想在同一查询中选择第三列wp_postmeta.post_id.我不知道如何通过不包含涉及子查询的JOIN附加它来做到这一点.没有使用子查询，难道没有更简单的方法吗? (如果解决方案有效且不太耗费资源，则将让步子查询.)
MariaDB 10.1
create table wp_posts ( ID integer primary key auto_increment, post_title varchar(30), post_type varchar(30) );
✓
create table wp_postmeta ( ID integer primary key auto_increment, post_id integer, meta_key varchar(30) not null default '_regular_price', meta_value integer not null );
✓
insert into wp_posts (post_title, post_type) values ('Apple Pie','Product'), ('French Toast','Product'), ('Shepards Pie','Product'), ('Jam Pie','Product'), ('Jam Pie','Product'), ('Plate','Not a Product'), ('Bucket','Not a Product'), ('Chequebook','Not a Product'), ('French Toast','Product'), ('French Toast','Product'), ('Banana','Product'), ('Banana','Product'), ('Banana','Product');
✓
insert into wp_postmeta (post_id, meta_value) values (1,10), (2,5), (3,9), (4,8), (5,11), (6,12), (7,10), (8,6), (9,1), (10,1), (11,7), (12,2), (13,2);
✓
SELECT wp_posts.post_title, MIN(wp_postmeta.meta_value) FROM wp_postmeta JOIN wp_posts ON wp_postmeta.post_id = wp_posts.id WHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price' GROUP BY wp_posts.post_title ORDER BY wp_posts.post_title
post_title | MIN(wp_postmeta.meta_value) :----------- | --------------------------: Apple Pie | 10 Banana | 2 French Toast | 1 Jam Pie | 8 Shepards Pie | 9
db<>小提琴此处
我对最初的问题不够清楚和明确-我想折叠重复的post_titles并使用与MIN(pm.meta_value)
wp_postmeta.post_id
如果我正确地跟随了您，则可以使用 window函数来解决您的问题:
SELECT x.post_title, x.id
FROM (
SELECT wp_posts.post_title, wp_postmeta.id, ROW_NUMBER() OVER (PARTITION BY wp_posts.post_title ORDER BY wp_postmeta.meta_value) rn
FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price'
) x WHERE x.rn = 1;
窗口函数是用于此类用例的方式(如果我理解正确的话).在效率和可读性方面，它们通常会击败其他解决方案.
如果RDBMS不支持窗口功能，一种解决方案是使用具有NOT EXISTS条件的相关子查询，以确保对于给定的帖子标题，不存在具有较小元值的记录.
SELECT p.post_title, MIN(m.id)
FROM
wp_postmeta m
INNER JOIN wp_posts p
ON p.id = m.post_id
AND p.post_type = 'Product'
WHERE
m.meta_key = '_regular_price'
AND NOT EXISTS (
SELECT 1
FROM
wp_postmeta m1
INNER JOIN wp_posts p1
ON p1.id = m1.post_id
AND p1.post_type = 'Product'
WHERE
m1.meta_key = '_regular_price'
AND p1.post_title = p.post_title
AND m1.meta_value < m.meta_value
)
GROUP BY p.post_title
注意:我对您的SQL做了一些清理(添加了表别名，并将某些条件移到了相关联接的ON子句中.)
In this query, I also want to SELECT a third column, wp_postmeta.post_id, in the same query. I can't figure out how to do this without appending it via a JOIN which involves subquerying. Isn't there a simpler easier way, without the use of subqueries? (Willing to concede on subqueries if the solution is efficient and not too resource-intensive.)
MariaDB 10.1
create table wp_posts ( ID integer primary key auto_increment, post_title varchar(30), post_type varchar(30) );
✓
create table wp_postmeta ( ID integer primary key auto_increment, post_id integer, meta_key varchar(30) not null default '_regular_price', meta_value integer not null );
✓
insert into wp_posts (post_title, post_type) values ('Apple Pie','Product'), ('French Toast','Product'), ('Shepards Pie','Product'), ('Jam Pie','Product'), ('Jam Pie','Product'), ('Plate','Not a Product'), ('Bucket','Not a Product'), ('Chequebook','Not a Product'), ('French Toast','Product'), ('French Toast','Product'), ('Banana','Product'), ('Banana','Product'), ('Banana','Product');
✓
insert into wp_postmeta (post_id, meta_value) values (1,10), (2,5), (3,9), (4,8), (5,11), (6,12), (7,10), (8,6), (9,1), (10,1), (11,7), (12,2), (13,2);
✓
SELECT wp_posts.post_title, MIN(wp_postmeta.meta_value) FROM wp_postmeta JOIN wp_posts ON wp_postmeta.post_id = wp_posts.id WHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price' GROUP BY wp_posts.post_title ORDER BY wp_posts.post_title
post_title | MIN(wp_postmeta.meta_value) :----------- | --------------------------: Apple Pie | 10 Banana | 2 French Toast | 1 Jam Pie | 8 Shepards Pie | 9
db<>fiddle here
Edit: I wasn't clear and explicit enough in my original question - I want to collapse the duplicate post_titles and use the wp_postmeta.post_id corresponding to the MIN(pm.meta_value)
If I followed you correctly, your question can be solved using window functions :
SELECT x.post_title, x.id
FROM (
SELECT wp_posts.post_title, wp_postmeta.id, ROW_NUMBER() OVER (PARTITION BY wp_posts.post_title ORDER BY wp_postmeta.meta_value) rn
FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price'
) x WHERE x.rn = 1;
Window functions are the way to go for such use cases (if I understood it right). They usually beat Other solutions in terms of efficiency and readability.
If your RDBMS does not support window functions, one solution is to use a correlated subquery with a NOT EXISTS condition that ensures that there is no record with a smaller meta value for the given post title.
SELECT p.post_title, MIN(m.id)
FROM
wp_postmeta m
INNER JOIN wp_posts p
ON p.id = m.post_id
AND p.post_type = 'Product'
WHERE
m.meta_key = '_regular_price'
AND NOT EXISTS (
SELECT 1
FROM
wp_postmeta m1
INNER JOIN wp_posts p1
ON p1.id = m1.post_id
AND p1.post_type = 'Product'
WHERE
m1.meta_key = '_regular_price'
AND p1.post_title = p.post_title
AND m1.meta_value < m.meta_value
)
GROUP BY p.post_title
NB : I did a little cleaning in your SQL (added table alias and moved some condition to then ON clause of the relevant join).
这篇关于如何在带有MIN的SELECT中包含第三列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
