Início / dba / Perguntas / 9482
Accepted
giannis christofakis
Asked: 2011-12-21 09:40:41 +0800 CST

Quantidade de entradas

  • 772

Eu tenho as tabelas abaixo. A primeira tem postagens com uma chave estrangeira para uma tabela de categoria. Estou procurando uma consulta que me dê a quantidade de postagens em cada categoria.

A mesa de correio.

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

A tabela de categorias.

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

Estou procurando uma consulta que me dê algo assim.

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

3 respostas

  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;

    Se você deseja exibir categorias com 0 posts, você precisa usar LEFT JOINem vez de INNER. Além disso, para outros RDMS, exceto o mysql, você precisa GROUP BY a.occuId, a.occuDscr(o Mysql permite que você tenha apenasGROUP BY a.occuId

    ATUALIZAÇÃO por @RolandoMySQLDBA

    A saída da consulta de @a1ex07 é semelhante a esta:

    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
;

    Aqui estão seus dados de amostra:

    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>

    Aqui está o resultado:

    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>

    Para obter contagens para todas as ocupações, consulte desta forma:

    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 
;

    Aqui está esse resultado:

    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>

    De uma chance !!!

    • 3

  3. Apenas uma alternativa, você também pode executar a consulta abaixo, traz o mesmo resultado.

    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 Fiddle

    • 0

relate perguntas