主页 / dba / 问题 / 194230
Accepted
Carol.Kar
Asked: 2017-12-31 02:51:27 +0800 CST

过滤具有特定状态的记录

  • 772

我正在使用 mysql，我有两个表，产品和修订版。

加入他们给我下表：

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               |

产品具有标识产品的唯一 ID。由于价格变化，每个产品可能有几个不同的价格。每个产品都有一个修订 ID。

修订表有一个 revision_status 并且可以有三个不同的值：1for approved price、0forclosed revision和nullfor to be revised。

作为最终结果，我想获得所有revision_status = 1具有revision_status = null.

最后结果

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

我尝试了以下内容：

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;

然而，这也给了我修改后的revision_status = 0

我创建了这个sqlfiddle作为最小可行示例。

1 个产品可以有 1 个修订版。最初一个产品revision_status有空。检查后，状态设置为 1 - 已批准 - 或 0 - 未批准。在应用程序中，我有一个逻辑，只有 1 个产品 - 具有相同的名称 - 可以具有 revision_status 1。我正在寻找一种方法来显示具有 revision_status 1 和 null 的所有修订，并始终在 null 之上显示 1ns。（如结果表所示）。

mysql

1 个回答

  1. Best Answer

    尝试以下查询（对于初学者）：

    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

    如果这为您提供了所需的结果，也许我们应该通过重构 WHERE 子句来显式涵盖 (revision_status) 情况“1”和“null”，如下所示：

    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

相关问题