主页 / dba / 问题 / 10472
Accepted
giannis christofakis
Asked: 2012-01-11 13:07:45 +0800 CST

加入三个表

  • 772

我有那三张桌子

SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field;
+---------------+------------------------+
| Occupation Id | Occupation Description |
+---------------+------------------------+
|             1 | Software Engineer      |
|             2 | Economics              |
|             3 | Structural Engineer    |
|             4 | Legal Advisors         |
|             5 | Plumbers               |
|             6 | Social Advisors        |
|             7 | Musicians              |
+---------------+------------------------+

SELECT usrId AS 'User Id',usrName AS 'User Name',usrOccuId AS 'User Occupation Id' FROM users;
+---------+------------+--------------------+
| User Id | User Name  | User Occupation Id |
+---------+------------+--------------------+
|       2 | goodys     |                  6 |
|       5 | realmadrid |                  7 |
|       6 | petousis   |                  6 |
+---------+------------+--------------------+

SELECT pstId AS 'Post Id',pstTitle AS 'Post Title',pstOccuId AS 'Post Occupation Id' FROM job_post ORDER BY pstId;
+---------+-------------------------------------------+--------------------+
| Post Id | Post Title                                | Post Occupation Id |
+---------+-------------------------------------------+--------------------+
|       4 | Software Engineer Recruit                 |                  1 |
|       5 | Web Developer Recruit                     |                  1 |
|       6 | Saxophonist                               |                  7 |
|       7 | Construction Company looking for plumber. |                  5 |
|       8 | Economic Analyst                          |                  2 |
|       9 | Legal Advisor                             |                  4 |
|      10 | Economic Assistant                        |                  2 |
+---------+-------------------------------------------+--------------------+

我想将下面的两个表结合起来，以便有一个包含职业描述、每个职位的帖子和每个职位的用户。

SELECT occuDscr job,count(pstOccuId) AS 'Posts' FROM job_post INNER JOIN occupation_field on pstOccuId = occuId GROUP BY job;
+-------------------+-------+
| job               | Posts |
+-------------------+-------+
| Economics         |     2 |
| Legal Advisors    |     1 |
| Musicians         |     1 |
| Plumbers          |     1 |
| Software Engineer |     2 |
+-------------------+-------+

SELECT occuDscr job,count(usrOccuId) AS 'Users' FROM users INNER JOIN occupation_field on usrOccuId = occuId GROUP BY job;
+-----------------+-------+
| job             | Users |
+-----------------+-------+
| Musicians       |     1 |
| Social Advisors |     2 |
+-----------------+-------+

像这样的东西

    +-------------------+-------+-------+
    | job               | Posts | Users |
    +-------------------+-------+-------+
    | Economics         |     2 |     0 |
    | Legal Advisors    |     1 |     0 |
    | Musicians         |     1 |     1 |
    | Plumbers          |     1 |     0 |
    | Software Engineer |     2 |     0 |
    | Social Advisors   |     0 |     1 |
    +-------------------+-------+-------+
mysql join

2 个回答

  1. Best Answer

    我基本上对您的两个查询执行了occupation_field表的左连接，通过 occuDscr 字段连接。这是您的新查询：

    SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    USING (occuDscr)
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    USING (occuDscr)
;

    如果您不清楚 USING 子句，这里是您的没有 USING 子句

    SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    ON oc.occuDscr = postInfo.occuDscr
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    ON oc.occuDscr = userInfo.occuDscr
;

    这是您加载的示例数据：

    mysql> drop database if exists giannosfor;
Query OK, 3 rows affected (0.13 sec)

mysql> create database giannosfor;
Query OK, 1 row affected (0.00 sec)

mysql> use giannosfor
Database changed
mysql> create table occupation_field
    -> (occuId int not null auto_increment,
    -> occuDscr varchar(40),
    -> primary key (occuId));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into occupation_field (occuDscr) values
    -> ('Software Engineer'),('Economics'),('Structural Engineer'),
    -> ('Legal Advisors'),('Plumbers'),('Social Advisors'),('Musicians');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create table users
    -> (usrId int not null,
    -> usrName varchar(20),
    -> usrOccuId int not null);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into users values
    -> (2,'goodys',6),(5,'realmadrid',7),(6,'petousis',6);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table job_post
    -> (pstId int not null,pstTitle varchar(60),
    -> pstOccuId int not null,
    -> primary key (pstId));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into job_post values
    -> ( 4,'Software Engineer Recruit',1),
    -> ( 5,'Web Developer Recruit',1),
    -> ( 6,'Saxophonist',7),
    -> ( 7,'Construction Company looking for plumber.',5),
    -> ( 8,'Economic Analyst',2),
    -> ( 9,'Legal Advisor',4),
    -> (10,'Economic Assistant',2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from occupation_field;
+--------+---------------------+
| occuId | occuDscr            |
+--------+---------------------+
|      1 | Software Engineer   |
|      2 | Economics           |
|      3 | Structural Engineer |
|      4 | Legal Advisors      |
|      5 | Plumbers            |
|      6 | Social Advisors     |
|      7 | Musicians           |
+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from users;
+-------+------------+-----------+
| usrId | usrName    | usrOccuId |
+-------+------------+-----------+
|     2 | goodys     |         6 |
|     5 | realmadrid |         7 |
|     6 | petousis   |         6 |
+-------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from job_post;
+-------+-------------------------------------------+-----------+
| pstId | pstTitle                                  | pstOccuId |
+-------+-------------------------------------------+-----------+
|     4 | Software Engineer Recruit                 |         1 |
|     5 | Web Developer Recruit                     |         1 |
|     6 | Saxophonist                               |         7 |
|     7 | Construction Company looking for plumber. |         5 |
|     8 | Economic Analyst                          |         2 |
|     9 | Legal Advisor                             |         4 |
|    10 | Economic Assistant                        |         2 |
+-------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)

mysql>

    这是第一个查询的执行

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

    这是第二个查询的执行

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     ON oc.occuDscr = postInfo.occuDscr
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     ON oc.occuDscr = userInfo.occuDscr
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.01 sec)

mysql>

    试试看 ！！！

    更新 2012-01-11 11:50 EDT

    要删除没有帖子的作业，请将第一个 LEFT JOIN 更改为 INNER JOIN：

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
5 rows in set (0.00 sec)

mysql>

    要删除没有用户的作业，请将第二个 LEFT JOIN 更改为 INNER JOIN：

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------------+-------+-------+
| job             | Posts | Users |
+-----------------+-------+-------+
| Musicians       |     1 |     1 |
| Social Advisors |     0 |     2 |
+-----------------+-------+-------+
2 rows in set (0.00 sec)

mysql>

    要删除没有用户或没有帖子的作业，请将两个 LEFT JOIN 更改为 INNER JOIN：

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------+-------+-------+
| job       | Posts | Users |
+-----------+-------+-------+
| Musicians |     1 |     1 |
+-----------+-------+-------+
1 row in set (0.00 sec)

mysql>

    更新 2012-01-11 12:03 EDT

    要删除没有 Post 或 Users 的 Jobs，请添加 WHERE 子句来检查其中一个是否大于零：

    mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> WHERE
    ->     IFNULL(postInfo.PostCount,0)>0 OR
    ->     IFNULL(userInfo.UserCount,0)>0
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Social Advisors   |     0 |     2 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
6 rows in set (0.00 sec)

mysql>
    • 3

  2. 这应该是你所追求的（或者至少足以让你走上正轨）：

    SELECT occuDscr job, 
       SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Posts', 
       SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Users'
FROM  occupation_field
          LEFT OUTER JOIN
      users ON usrOccuId = occuId
          LEFT OUTER JOIN
      job_post ON pstOccuId = occuId
GROUP BY job
HAVING (SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) > 0 
     OR SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) > 0)

    occupation_field LEFT OUTER JOIN users ON usrOccuId = occuId 本质上是说“给我来自 的所有行occupation_field，如果users没有匹配的行，occuId那么用NULL值填充它”。另请参阅关于外部连接的维基百科文章。

    之后，我们在语句NULL中将值视为 0 ，这一切都完成了。CASESUM

    编辑 @RolandoMySQLDBA 2012-01-11 11:42 EDT

    嘿西蒙。我针对我创建的数据集运行了您的查询。

    mysql> SELECT occuDscr job,
    ->        SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Posts',
    ->        SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Users'
    -> FROM  occupation_field
    ->           LEFT OUTER JOIN
    ->       users ON usrOccuId = occuId
    ->           LEFT OUTER JOIN
    ->       job_post ON pstOccuId = occuId
    -> GROUP BY job
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

    您的查询按已发布的方式工作。+1 !!!

    • 2

相关问题