SQL 问题 - 帮助查询计数 + 最小值

Question

>Tables：

[dbo].[Dealer]
[number] [firstName] [surName]   [birthDate] [startWorkingDate] [ID]
Num Dealer|full name of Dealers| birthdate | date + time       | id number
[dbo].[Game]
[gameTypeName] [gameStartDateTime] [gameEndTime] [DealerInGame]
name of game  | date + time      | date + time| same numbers like [Dealer].[number]

我希望查询做什么： 返回参与最少游戏的荷官(员工编号和全名(的查询。

SELECT top 1 number,firstName +' '+ surName as 'full name' FROM Dealer
where  exists (SELECT gameTypeName,COUNT( gameTypeName)  FROM Game 
GROUP BY gameTypeName)

该查询只为我带来了第一个游戏数量最少的查询，但如果有 2 个或更多具有相同值的荷官，则不会显示所有荷官。

怎么了？

Answer 1

我不认为MySQL像SQL Server那样支持TOP运算符，但是您可以使用LIMIT获取第一条记录，如下所示：

SELECT number,
firstName +' '+ surName as 'full name'
FROM Dealer
where  exists (SELECT gameTypeName,COUNT( gameTypeName)
FROM Game 
GROUP BY gameTypeName)
LIMIT 1;

Answer 2

连接两个表，使用COUNT()，然后使用ORDER BY和LIMIT 1。这是它的MySQL语法：

SELECT number, CONCAT(firstName, ' ', lastName) AS fullName
FROM Dealer AS d
JOIN Game AS g ON g.DealerInGame = d.number
GROUP BY d.number
ORDER BY COUNT(*)
LIMIT 1

Answer 3

您可以执行以下操作join：

select d.number, CONCAT(d.firstName, ' ', d.lastName) as fullName
from Dealer d inner join ( 
select DealerInGame
from Game
group by DealerInGame
having count(*) <= 1
) g on g.DealerInGame = d.number;

Answer 4

如果你想要所有游戏数量最少的庄家，你需要玩最少的游戏，然后找到所有拥有该游戏数量的庄家。

WITH GameCount as (
SELECT 
DealerInGame, 
Count(*) Games
FROM Game
)
SELECT 
d.firstName + ' ' + d.surName fullName,
d.number
FROM GameCount g
LEFT JOIN Dealer d on d.Number = g.DealerInGame
WHERE g.Games = (
SELECT Min(Games)
FROM GameCount)