SQL子查询，使用WHERE & 'IN‘筛选特定行

Question

请使用here并复制下面的代码，以便在我的问题中添加上下文。

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

如何让Query #3只选择一个拥有最大狗的父亲，并同时返回其余的列？

创建表格代码：

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

Answer 1

试试这个-

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

Answer 2

使用这个..

select * from table_3 group by Father order by Dogs

或

SELECT id, Father, max(CONVERT(Dogs,SIGNED)) from table_3 group by Father

Answer 3

我认为下面的代码可以在sql server中帮助你：

SELECT Father,
   MAX(CONVERT(INT, Dogs))
FROM   table_3
GROUP BY
   father
ORDER BY
   MAX(CONVERT(INT, Dogs)) DESC

或

SELECT DISTINCT Father,
   MAX(CONVERT(INT, Dogs)) OVER(PARTITION BY Father) AS avgsales
FROM   table_3

在Mysql中

更改：

CONVERT(INT, Dogs)

至

CAST(Dogs AS SIGNED)