时间戳

首页 > 解决方案 > SQL 子查询，使用 WHERE & 'IN' 过滤特定行

mysql - SQL 子查询，使用 WHERE & 'IN' 过滤特定行

问题描述

请在此处使用并复制下面的代码为我的问题添加上下文。

如您所见，我有一张桌子，其中某些父亲的名字相同，我想为我的最终列表选择father最多的。dogs您可以在 中看到整个表格Query #1，我想要的返回结果Query #2，Father's但是当我试图让父亲只返回 1 次时John，Query #3它显示了约翰神父有 9 条狗和 10 条狗的全部记录。

我怎样才能Query #3只选择一个拥有 Max Dogs 的父亲，并返回其余的列？

创建表代码：

CREATE TABLE IF NOT EXISTS `table_3` 
(
    `id` int(6) unsigned NOT NULL,
    `Father` varchar(200) NOT NULL,
    `Dogs` varchar(200) NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `table_3` (`id`,`Father`, `Dogs`) 
VALUES ('1', 'John', '10'),
       ('2', 'John','9'),
       ('3', 'Joe', '4'),
       ('4', 'Jeremy', '4'),
       ('5', 'Jack', '4'),
       ('6', 'NULL', '5');

查询 #1

select Father from table_3;

查询 #1 输出：

id  Father  Dogs
1   John    10
2   John    9
3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

查询 #2

select b.Father from (select Father, max(Dogs)
from table_3
group by 1
)b;

查询 #2 输出

Father
Jack
Jeremy
Joe
John
NULL

查询 #3

select * from table_3 a
where a.Father in (
select b.Father from (select Father, max(Dogs)
from table_3 
group by 1
)b);

查询 #3 输出

id  Father  Dogs
1   John    10
2   John    9
3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

查询 #3 的所需输出

id  Father  Dogs
1   John    10

3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

标签： mysqlsql

解决方案

尝试这个 -

SELECT * FROM table_3 A
INNER JOIN( 
    SELECT father,MAX(Dogs) Dogs 
    -- You need to CAST your Dogs column to INT before you apply MAX on this column
    FROM table_3
    GROUP BY Father
)B
ON A.father = B.father
AND A.Dogs = B.Dogs

如果您的数据库允许，您也可以尝试使用行号，如下所示 -

SELECT * FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY father ORDER BY Dogs DESC) RN
    FROM table_3
) A WHERE RN = 1

推荐阅读