gpt4 book ai didi

mysql - 使用 MySQL 进行复杂的 SQL 查询?

转载 作者:行者123 更新时间:2023-11-29 00:58:55 25 4
gpt4 key购买 nike

我有一个数据库:

[business] must have a [location]
[location] may have many [business]
[location] may have many [postcode]
[postcode] may be for many [location]
[postcode] must have a [state]

我想要一个查询来返回每个州在企业数量方面排名前 10 位的位置。

为了更有意义,它可能会出来:

Western Australia
- perth
- bunbury
- etc. up to 10

(即珀斯拥有西澳大利亚最多的上市公司)

Victoria
- melbourne
- st kilda
- etc. up to 10

等等

我希望在不使用 UNION 的情况下实现这一目标。我已经有一段时间没有做复杂的 SQL 了,这让我很头疼。

我正在使用 MySQL。

类似的表达方式是,如果您有类别、产品和订单,并且想要每个类别的订单数排名前十的产品。

最佳答案

这个查询应该有效。

select * from (
select a.state, a.location_id, C,
@n:=case when @s=a.state then @n+1 else 1 end counter,
@s:=a.state
from (select @s:=null) b, (
select pc.state, b.location_id, COUNT(b.location_id) C
from postcode pc
inner join location_postcode lp on lp.postcode_id=pc.id
inner join business b on b.location_id=lp.location_id
group by pc.state, b.location_id
order by pc.state, C desc
) a
) c
where counter <= 10

我使用了应该易于理解的字段名称,假设这些表存在规定的关系:

business M-1 location
location M-M postcode
(expands to) => location 1-M location_postcode M-1 postcode
postcode M-1 state

查询已使用此数据进行了测试:

create table business (location_id int);
insert into business select floor(rand()*10);
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
insert into business select floor(rand()*10) from business;
create table location_postcode (location_id int, postcode_id int);
insert into location_postcode select 1,1;
insert into location_postcode select 2,1;
insert into location_postcode select 3,1;
insert into location_postcode select 4,2;
insert into location_postcode select 5,1;
insert into location_postcode select 5,2;
insert into location_postcode select 6,1;
insert into location_postcode select 6,3;
insert into location_postcode select 7,1;
insert into location_postcode select 7,4;
insert into location_postcode select 8,5;
insert into location_postcode select 9,6;
insert into location_postcode select 10,7;
create table postcode (id int, state int);
insert into postcode select 1,1;
insert into postcode select 2,2;
insert into postcode select 3,3;
insert into postcode select 4,4;
insert into postcode select 5,4;
insert into postcode select 6,5;
insert into postcode select 7,5;

这并没有为每个“前 10 名”创建足够的记录,但您会看到 COUNTER 列的排名是如何正确的。要查看它针对这个小数据集的工作情况,请先将此过滤器留在那里

where counter <= 10

检查 COUNTER 列,然后将其减少到 2 或 3,以仅显示每个州的前 2 或 3 个。

关于mysql - 使用 MySQL 进行复杂的 SQL 查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4676423/

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