如何在带有 MIN 的 SELECT 中包含第三列 [英] How can I include third column in a SELECT with MIN

问题描述

在此查询中，我还想在同一查询中选择第三列 wp_postmeta.post_id.如果不通过涉及子查询的 JOIN 附加它，我无法弄清楚如何做到这一点.难道没有更简单的方法，不使用子查询吗?(如果解决方案高效且不太占用资源，则愿意在子查询上让步.)

MariaDB 10.1

<块引用>

创建表 wp_posts (ID 整数主键 auto_increment，post_title varchar(30),post_type varchar(30));

<上一页>✓

<块引用>

创建表 wp_postmeta (ID 整数主键 auto_increment，post_id 整数，meta_key varchar(30) not null 默认 '_regular_price',meta_value 整数不为空);

<上一页>✓

<块引用>

插入 wp_posts (post_title, post_type) 值('苹果派'，'产品')，('法式吐司'，'产品')，(谢泼德派"，产品")，('果酱'，'产品')，('果酱'，'产品')，('盘子'，'不是产品')，('桶'，'不是产品')，('支票簿'，'不是产品')，('法式吐司'，'产品')，('法式吐司'，'产品')，('香蕉'，'产品')，('香蕉'，'产品')，('香蕉'，'产品')；

<上一页>✓

<块引用>

插入 wp_postmeta (post_id, meta_value) 值(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_postsON wp_postmeta.post_id = wp_posts.idWHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price'按 wp_posts.post_title 分组由 wp_posts.post_title 订购

<上一页>post_title |最小值(wp_postmeta.meta_value):----------- |--------------------------:苹果派 |10香蕉 |2法式吐司 |1果酱派 |8谢泼德派 |9

db<>小提琴这里

我的原始问题不够清楚和明确 - 我想折叠重复的 post_titles 并使用 wp_postmeta.post_id 对应于 >MIN(pm.meta_value)

解决方案

如果我没看错的话，你的问题可以用window functions解决:

选择 x.post_title, x.id从 (SELECT wp_posts.post_title, wp_postmeta.id, ROW_NUMBER() OVER (PARTITION BY wp_posts.post_title ORDER BY wp_postmeta.meta_value) rnFROM wp_postmeta内部加入 wp_posts ON wp_postmeta.post_id = wp_posts.idWHERE wp_posts.post_type = 'Product' AND wp_postmeta.meta_key = '_regular_price') x 其中 x.rn = 1;

查看这个 DB Fiddle 演示

窗口函数是处理此类用例的方法(如果我理解正确的话).它们通常在效率和可读性方面胜过其他解决方案.

<小时>

如果您的 RDBMS 不支持窗口函数，一种解决方案是使用带有 NOT EXISTS 条件的相关子查询，以确保给定帖子标题没有具有较小元值的记录.

SELECT p.post_title, MIN(m.id)从wp_postmeta m内部联接 wp_posts pON p.id = m.post_id和 p.post_type = '产品'在哪里m.meta_key = '_regular_price'不存在(选择 1从wp_postmeta m1内部连接 ​​wp_posts p1ON p1.id = m1.post_id和 p1.post_type = '产品'在哪里m1.meta_key = '_regular_price'和 p1.post_title = p.post_titleAND m1.meta_value 

DB Fiddle 演示

注意:我在您的 SQL 中做了一些清理(添加了表别名并将一些条件移至相关连接的 then 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;

See this DB Fiddle demo

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

DB Fiddle demo

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屋！