Início / dba / Perguntas / 194230
Accepted
Carol.Kar
Asked: 2017-12-31 02:51:27 +0800 CST

Filtrar registros que tenham um determinado status

  • 772

Estou usando mysql e tenho duas tabelas, produto e revisão.

Juntando-os me dá a seguinte tabela:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id 
ORDER BY p.id;

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 2  | Produkt1 | 4      | 2           | 2  | 0               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 6  | Produkt2 | 78     | 6           | 6  | 0               |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |
| 10 | Produkt4 | 33     | 10          | 10 | 1               |

Um Produto tem um ID exclusivo que identifica um produto. Devido a alterações de preços cada produto pode ter vários preços diferentes. Cada produto tem um ID de revisão.

A tabela de revisão tem um revision_status e pode ter três valores diferentes: 1for approved price, 0for closed revisione nullfor to be revised.

Como resultado final gostaria de obter todos os registros que possuem revision_status = 1e também um arquivo revision_status = null.

Resultado final

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |

Eu tentei o seguinte:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id 
WHERE p.name IN (
  SELECT p.name
  FROM product AS p
  INNER JOIN revisions AS r ON p.revision_id = r.id 
  GROUP BY p.name
  HAVING COUNT(r.revision_status) > 1)
ORDER BY p.id;

No entanto, isso também me dá revisões de volta que têm umrevision_status = 0

Eu criei este sqlfiddle como um exemplo mínimo viável.

1 Produto pode ter 1 Revisão. Inicialmente um produto tem revision_statusde nulo. Depois de verificado, o status é definido como 1 - aprovado - ou 0 - não aprovado. Na aplicação tenho uma lógica, que apenas 1 produto - com o mesmo nome - pode ter revision_status 1. Estou procurando uma forma de mostrar todas as revisões que possuem revision_status 1 e null e exibir os 1ns sempre em cima dos nulls. (Como mostrado na tabela de resultados).

mysql

1 respostas

  1. Best Answer

    Tente a seguinte consulta (para iniciantes):

    select
  P.id
, P.name
, P.prices
, R.revision_status
, R.id
from Product P
  join Revisions R on P.revision_id = R.id
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name
    having Product.name = P.name
  ) > 1
order by P.id, R.revision_status ;

--
-- result
-- "name" of the Product with the ID 4 changed to: "Produkt2"
-- before executing the query
--
+------+----------+--------+-----------------+------+
| id   | name     | prices | revision_status | id   |
+------+----------+--------+-----------------+------+
| 1    | Produkt1 | 10     | 1               | 1    |
| 3    | Produkt1 | 2      | null            | 3    |
| 4    | Produkt2 | 42     | 1               | 4    |
| 5    | Produkt2 | 43     | null            | 5    |
| 7    | Produkt3 | 1      | 1               | 7    |
| 8    | Produkt3 | 3      | null            | 8    |
| 9    | Produkt3 | 4      | null            | 9    |
+------+----------+--------+-----------------+------+
7 rows in set (0.01 sec)

-- MySQL 5.7

    Se isso fornecer o resultado necessário, talvez devêssemos cobrir os casos (revision_status) '1' e 'null' explicitamente, refatorando a cláusula WHERE da seguinte maneira:

    select ... from ... -- same as above
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = '1'
  ) >= 1
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = 'null'
  ) >= 1
order by P.id, R.revision_status ;

-- ... = 'null' due to your test data, maybe this needs IS NULL in reality.
    • 1

relate perguntas