MySQL 查询连接和计数查询

发布于 2024-09-18 05:13:36 字数 1819 浏览 7 评论 0原文

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

这些是我的表格:

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

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

谢谢, 史蒂文

I'm trying to pull values from a database for a web app where a moderator can add companies to a list of specified industries. This request needs to pull each industry's name along with a count of attached active companies, as an overview to the moderator.

These are my tables:

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    |
|--------------------------|

This query works but does not account for inactive companies:

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

I need it to pull the counts for active companies only, but when I try this I get strange results:

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

I know i'm missing something simple, I just can't figure out what

Thanks,
Steven

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

花之痕靓丽 2024-09-25 05:13:36

您可能想尝试以下操作:

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)

You may want to try the following:

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;

It should return the following result:

+------+-------------+-------+
| 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)
暗地喜欢 2024-09-25 05:13:36

第二个查询(仅适用于活动记录)正在与 Companies 表进行交叉连接。

试试这个 - 恐怕我没有测试它,但应该可以工作:

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

编辑:

添加了一个查询,应该注意 O 计数行业的情况

SELECT industries.id, industries.industry, count(x.id) 
FROM industries JOIN
(
    SELECT links.id, links.industry, company.id 
    FROM companies  
    INNER JOIN links on links.company  = companies.id
    WHERE companies.active=1 
) x
ON industries.id = x.industry  
AND industries.mod=2  
GROUP BY industries.id  

The 2nd query (for active records only) is doing a cross join with Companies table.

Try this - afraid i didnt test it but should work :

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

EDIT :

Added a query that should take care for case with Industry with O count

SELECT industries.id, industries.industry, count(x.id) 
FROM industries JOIN
(
    SELECT links.id, links.industry, company.id 
    FROM companies  
    INNER JOIN links on links.company  = companies.id
    WHERE companies.active=1 
) x
ON industries.id = x.industry  
AND industries.mod=2  
GROUP BY industries.id  
我为君王 2024-09-25 05:13:36

试试这个:

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

Try this:

SELECT industries.id, industries.industry, count(links.id) as count FROM industries 
LEFT JOIN links on links.industry=industries.id 
LEFT JOIN links.company = companies.id 
WHERE industries.mod=2 AND companies.active=1 
GROUP BY industries.id 
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文