主页 / dba / 问题 / 9482
Accepted
giannis christofakis
Asked: 2011-12-21 09:40:41 +0800 CST

参赛作品数量

  • 772

我有以下表格。第一个表格的帖子带有类别表的外键。我正在寻找一个查询，该查询可为我提供每个类别的帖子数量。

帖子表。

 select pstOccuId,pstTitle from job_post;
+-----------+-------------------------------------------+
| pstOccuId | pstTitle                                  |
+-----------+-------------------------------------------+
|         1 | Software Engineer Recruit                 |
|         1 | Web Developer Recruit                     |
|         7 | Saxophonist                               |
|         5 | Construction Company looking for plumber. |
+-----------+-------------------------------------------+

类别表。

 select occuDscr,occuId from occupation_field;
+---------------------+--------+
| occuDscr            | occuId |
+---------------------+--------+
| Software Engineer   |      1 |
| Economics           |      2 |
| Structural Engineer |      3 |
| Legal Advisors      |      4 |
| Plumbers            |      5 |
| Social Advisors     |      6 |
| Musicians           |      7 |
+---------------------+--------+

我正在寻找一个能给我这样的查询。

+---------------------+--------+
| occuDscr            | amount |
+---------------------+--------+
| Software Engineer   |      2 |
| Plumbers            |      1 |
| Musicians           |      1 |
+---------------------+--------+
mysql query

3 个回答

  1. Best Answer
     select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount` 
 from occupation_field a
 INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
 GROUP BY a.occuId;

    如果要显示包含 0 个帖子的类别，则需要使用LEFT JOIN而不是INNER. 此外，对于除 mysql 之外的其他 RDMS，您必须GROUP BY a.occuId, a.occuDscr（Mysql 让您只需GROUP BY a.occuId

    由@RolandoMySQLDBA更新

    @a1ex07 查询的输出如下所示：

    mysql> select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount`
    ->  from occupation_field a
    ->  INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
    ->  GROUP BY a.occuId;
+--------+-------------------+--------+
| occuId | occuDscr          | amount |
+--------+-------------------+--------+
|      1 | Software Engineer |      2 |
|      5 | Plumbers          |      1 |
|      7 | Musicians         |      1 |
+--------+-------------------+--------+
3 rows in set (0.00 sec)
    • 3 
  2. SELECT
    oc.occuDscr,pst.amount
FROM
    (SELECT COUNT(1) amount,pstOccuId 
    FROM job_post GROUP BY pstOccuId) pst
    INNER JOIN occupation_field oc
    ON (pst.pstOccuId = oc.occuId)
ORDER BY
    pst.amount DESC,oc.occuDscr
;

    这是您的样本数据：

    mysql> use test
Database changed
mysql> drop table if exists occupation_field;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists job_post;
Query OK, 0 rows affected (0.07 sec)

mysql> create table occupation_field
    -> (occuDscr varchar(50),
    -> occuId int not null auto_increment,
    -> primary key (occuId));
Query OK, 0 rows affected (0.08 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 job_post
    -> (pstOccuId int not null,pstTitle varchar(50));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into job_post values
    -> (1,'Software Engineer Recruit'),
    -> (1,'Web Developer Recruit'),
    -> (7,'Saxophonist'),
    -> (5,'Construction Company looking for plumber');
Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

    这是结果：

    mysql> SELECT
    ->     oc.occuDscr,pst.amount
    -> FROM
    ->     (SELECT COUNT(1) amount,pstOccuId
    ->     FROM job_post GROUP BY pstOccuId) pst
    ->     INNER JOIN occupation_field oc
    ->     ON (pst.pstOccuId = oc.occuId)
    -> ORDER BY
    ->     pst.amount DESC,oc.occuDscr
    -> ;
+-------------------+--------+
| occuDscr          | amount |
+-------------------+--------+
| Software Engineer |      2 |
| Musicians         |      1 |
| Plumbers          |      1 |
+-------------------+--------+
3 rows in set (0.00 sec)

mysql>

    要获取所有职业的计数，请按以下方式查询：

    SELECT 
    oc.occuDscr,IFNULL(pst.amount,0) amount
FROM
    occupation_field oc LEFT JOIN
    (SELECT COUNT(1) amount,pstOccuId  
    FROM job_post GROUP BY pstOccuId) pst 
    ON (oc.occuId = pst.pstOccuId) 
ORDER BY 
    pst.amount DESC,oc.occuDscr 
;

    这是结果：

    mysql> SELECT
    ->     oc.occuDscr,IFNULL(pst.amount,0) amount
    -> FROM
    ->     occupation_field oc LEFT JOIN
    ->     (SELECT COUNT(1) amount,pstOccuId
    ->     FROM job_post GROUP BY pstOccuId) pst
    ->     ON (oc.occuId = pst.pstOccuId)
    -> ORDER BY
    ->     pst.amount DESC,oc.occuDscr
    -> ;
+---------------------+--------+
| occuDscr            | amount |
+---------------------+--------+
| Software Engineer   |      2 |
| Musicians           |      1 |
| Plumbers            |      1 |
| Economics           |      0 |
| Legal Advisors      |      0 |
| Social Advisors     |      0 |
| Structural Engineer |      0 |
+---------------------+--------+
7 rows in set (0.00 sec)

mysql>

    试试看 ！！！

    • 3

  3. 只是一个替代方案，您还可以执行以下查询，带来相同的结果。

    SELECT occuDscr, COUNT(occuId) AS 'amount'
 FROM occupation_field of,job_post jp
 WHERE of.occuId = jp.pstOccuId
   GROUP BY occuDscr, occuId
   ORDER BY amount DESC;

    SQL小提琴

    • 0

相关问题