gpt4 book ai didi

MySQL 查询连接和计数查询

转载 作者:可可西里 更新时间:2023-11-01 08:08:11 25 4
gpt4 key购买 nike

我正在尝试从一个网络应用程序的数据库中提取值,管理员可以在该应用程序中将公司添加到指定行业的列表中。此请求需要提取每个行业的名称以及附属活跃公司的数量,作为主持人的概览。

这些是我的表格:

companies

____________________________________
| id | company | active |
|---------------------------|--------|
| 12 | Ton-o-Bricks Haulage | 0 |
| 16 | Roofs 'n' Walls | 1 |
| 23 | Handy Services | 1 |
| 39 | Carpentharry | 1 |
|---------------------------|--------|

industries
________________________
| id | industry | mod |
|------------------|-----|
| 2 | Roofing | 2 |
| 4 | Carpentry | 2 |
| 7 | Handyman | 2 |
| 8 | Haulage | 2 |
| 9 | Electrician | 2 |
|------------------|-----|

links
___________________________
| id | industry | company |
|--------------------------|
| 1 | 2 | 23 |
| 2 | 4 | 16 |
| 3 | 4 | 39 |
| 4 | 7 | 23 |
| 5 | 2 | 16 |
| 6 | 8 | 12 |
|--------------------------|

此查询有效但不考虑不活跃公司:

SELECT industries.id, industries.industry, count(links.id) as count FROM industries LEFT JOIN links on links.industry=industries.id WHERE industries.mod=2 GROUP BY industries.id

// -Results =======

2 Roofing 2
4 Carpentry 2
7 Handyman 1
8 Haulage 1
9 Electrician 0

我需要它来统计活跃的公司,但是当我尝试这样做时,我得到了奇怪的结果:

SELECT industries.id, industries.industry, count(links.id) as count FROM industries LEFT JOIN links on links.industry=industries.id, companies WHERE industries.mod=2 AND companies.active=1 GROUP BY industries.id

// -Results =======

2 Roofing 6
4 Carpentry 6
7 Handyman 3
8 Haulage 3
9 Electrician 0

我知道我错过了一些简单的东西,我就是想不通是什么

谢谢,史蒂文

最佳答案

您可能想尝试以下方法:

SELECT      i.id, i.industry, count(l.id) as count
FROM industries i
LEFT JOIN (
SELECT l.industry, l.id
FROM links l
JOIN companies c ON (l.company = c.id AND c.active = 1)
) l ON (l.industry = i.id)
WHERE i.mod = 2
GROUP BY i.id, i.industry;

它应该返回以下结果:

+------+-------------+-------+
| id | industry | count |
+------+-------------+-------+
| 2 | Roofing | 2 |
| 4 | Carpentry | 2 |
| 7 | Handyman | 1 |
| 8 | Haulage | 0 |
| 9 | Electrician | 0 |
+------+-------------+-------+
5 rows in set (0.00 sec)

关于MySQL 查询连接和计数查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3657725/

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