代码之家 › 专栏 › 技术社区 › ptrcao

如何将第三列包含在带有MIN的SELECT中

groupwise-maximum mariadb syntax sql

ptrcao · 技术社区 · 7 年前

在这个查询中,我还想选择第三列, wp_postmeta.post_id ,在同一查询中。如果不通过包含子查询的联接附加它,我就无法理解如何实现这一点。如果不使用子查询,难道没有更简单的方法吗?(如果解决方案有效且资源不太密集,则愿意在子查询上让步。)

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<&燃气轮机;不停摆弄 here

post_titles 并使用对应于 MIN(pm.meta_value)

3 回复 | 直到 6 年前

GMB 7 年前

如果我没有弄错,你的问题可以用 窗口功能 :

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

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

如果您的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

DB Fiddle demo

注意:我在您的SQL中做了一些清理(添加了表别名,并将一些条件移到了相关join的then ON子句中)。

Gordon Linoff 7 年前

GROUP BY ?

SELECT p.post_title, p.id, MIN(pm.meta_value)
FROM wp_postmeta pm JOIN
     wp_posts p
     ON pm.post_id = p.id 
WHERE p.post_type = 'Product' AND pm.meta_key = '_regular_price' 
GROUP BY p.post_title, p.id
ORDER BY p.post_title;

这个 post_id 与 p.id ,所以你不妨用它。

编辑:

SELECT p.post_title, p.id, pm.meta_value
FROM wp_postmeta pm JOIN
     wp_posts p
     ON pm.post_id = p.id 
WHERE p.post_type = 'Product' AND
      pm.meta_key = '_regular_price' AND
      pm.meta_value = (SELECT MIN(pm2.meta_value)
                       FROM wp_postmeta pm2 JOIN
                            wp_posts p2
                            ON pm2.post_id = p2.id 
                       WHERE p2.post_title = p.post_title
                      )
ORDER BY p.post_title;

MladenB 7 年前

WITH T1
AS ( SELECT   post_title, MIN(id) AS ID
     FROM     wp_posts
     GROUP BY post_title )
SELECT   T1.ID, wp_posts.post_title, MIN(wp_postmeta.meta_value)
FROM     wp_postmeta
         JOIN wp_posts ON wp_postmeta.post_id = wp_posts.id
         JOIN T1 ON T1.post_title = wp_posts.post_title
WHERE    wp_posts.post_type = 'Product'
         AND wp_postmeta.meta_key = '_regular_price'
GROUP BY T1.ID,  wp_posts.post_title
ORDER BY wp_posts.post_title;

编辑:

您的表格wp_帖子应该如下所示

Id, post_title, post_type
1, 'Apple Pie','Product'
2, 'French Toast','Product'
3, 'Shepards Pie','Product'
4, 'Jam Pie','Product'
5, 'Plate','Not a Product'
6, 'Bucket','Not a Product'
7, 'Chequebook','Not a Product'
8, 'Banana','Product'

比wp_Posteta表中的数据还要多

post_id, meta_value
1,  10
2,  5
3,  9
4,  8
4,  11
5,  12 
6,  10
7,  6
2,  1
2,  1
8,  7
8,  2
8,  2

看起来像这样

SELECT   wp_postmeta.post_id, 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 = 'REG PRICE'
GROUP BY wp_postmeta.post_id,  wp_posts.post_title
ORDER BY wp_posts.post_title;