返回count作为附加列

9q78igpj 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(206)

我对mysql有点陌生，但我不太知道如何做到这一点：基本上，我有两个表：一个是公司表，另一个是位置表。locations表具有公司的外键，因此公司可以有多个位置。我有一个简单的select查询，返回公司id和位置id，如下所示：

SELECT location.id as location_id, company.id as company_id FROM company 
INNER JOIN location ON (location.company_id = company.id);

我想知道是否有一种方法，也返回一个额外的列的位置数。它会返回如下结果：

number_of_locations |  company_id | location_id
2 | 1 | 1
2 | 1 | 2
1 | 2 | 3
2 | 4 | 5
2 | 4 | 6

谢谢！

sql mysql mariadb

来源：https://stackoverflow.com/questions/62648970/returning-count-as-an-additional-column

2条答案

按热度按时间

bqf10yzr1#

下面是用于预期输出的sql-
解决方案#1-

SELECT count(location.id) over (partition by location.company_id) as number_of_locations,
company.id as company_id,
location.id as location_id
FROM company 
INNER JOIN location ON (location.company_id = company.id);

解决方案#2-

SELECT cl.number_of_locations,
company.id as company_id,
location.id as location_id
FROM company 
INNER JOIN location ON location.company_id = company.id
INNER JOIN (SELECT count(location.id) as number_of_locations,
company.id as company_id
FROM company 
INNER JOIN location ON location.company_id = company.id GROUP BY company.id) cl
ON company.id = cl.company_id;

赞(0）回复(0）举报 2021-08-01

puruo6ea2#

只需使用窗口功能：

select t.*, count(*) over (partition by company_id) as number_of_locations
from t;

赞(0）回复(0）举报 2021-08-01

我来回答

返回count作为附加列

2条答案

相关问题

热门标签

最新问答