gpt4 book ai didi

To display manager details if more than one manager is from same city(如果多个经理来自同一城市,则显示经理详细信息)

转载 作者:bug小助手 更新时间:2023-10-25 10:57:44 27 4
gpt4 key购买 nike



I have a table manager(managerid, name, address, city, phone). I have got to display the city, name and phone details if more than one manager is from the same city.
My code is:

我有一个桌子经理(经理ID、姓名、地址、城市、电话)。如果多个经理来自同一城市,我必须显示城市、姓名和电话详细信息。我的代码是:


  select m.city, m.name, m.phone
from manager m
group by m.name
having count(m.city) > 1;

But this shows me an error in the first line saying "not a group by expression".

Please help!

但这向我展示了第一行中的一个错误,即“不是按表达式分组”。请救救我!


更多回答
优秀答案推荐

A simple method uses exists:

一个简单的方法使用EXISTS:


select m.*
from manager m
where exists (select 1
from manager m2
where m2.city = m.city and m2.managerid <> m.managerid
);

You can also use count() as a window function:

您还可以将count()用作窗口函数:


select m.*
from (select m.*, count(*) over (partition by city) as cnt_city
from manager m
) m
where cnt_city > 1;


SELECT city, managerid
FROM manager
WHERE city IN (
SELECT city
FROM manager
GROUP BY city
HAVING COUNT(*) > 1
);

选择城市,经理ID从所在城市中选择(按计数(*)>1的城市从经理组中选择城市);


更多回答

This worked, thanks a ton! But would you mind if I ask for the explanation of the second piece of code?

这招奏效了,非常感谢!但是你介意我问一下第二段代码的解释吗?

@yaminir . . . It uses a window function (which Oracle calls analytic functions).

@yminir。。。它使用窗口函数(Oracle将其称为分析函数)。

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

您的答案可以通过其他支持信息来改进。请编辑以添加更多详细信息,如引用或文档,以便其他人可以确认您的答案是正确的。你可以在帮助中心找到更多关于如何写出好答案的信息。

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com