主页 / dba / 问题 / 301002
Accepted
JJM50
Asked: 2021-10-13 04:39:48 +0800 CST

查询获取列中所有唯一条目的总和

  • 772

目前我正在使用 mysql 返回具有相同单词的列中所有数据的总和。为此，我使用以下查询：

SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, 
SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, 
SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, 
SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, 
SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, 
SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, 
SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let 
FROM `crm_logs`

这会在我的数据库中为我指定的所有术语提供正确的输出，但现在我发现数据库中除了上面指定的变量之外还有更多的状态变量。所以我想要一种方法来拥有与该语句相同的功能，但使状态变量动态化。

基本上，如果这是查询，SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft则草稿的出现都应该是动态的。

mysql case

3 个回答

  1. 编写一个存储过程，使用 和 构造查询并CONCAT()执行它。PREPAREEXECUTE

    或者使用其字符串函数以您的应用程序语言构建查询。

    至于维基答案中的讨论，我添加了这个

    COALESCE(SUM(status_to = 'Draft'), 0)

    status_to但这只有在可能的情况下才有必要NULL。否则，这有效：

    SUM(status_to = 'Draft')
    • 2
  2. Best Answer

    有一种更简单的方法可以做你想做的事——我昨晚在想这个——在这里看我的第二把小提琴。

    您可以执行以下操作（下面的所有代码都可以在此处的小提琴中找到）：

    CREATE TABLE test
(
  t_id INTEGER NOT NULL PRIMARY KEY,
  status_to VARCHAR (50)
);

    然后填充它：

    INSERT INTO test VALUES
(7, NULL), (8, 'Test_val_1'), (9, 'Test_val_2'), 
 (3, 'Publish'), (4,  'Action'),  (5, 'Sold'),  (6, 'Let'), (10, 'Draft'), (11, 'Unpublish'), 
(12, 'Publish'), (13, 'Action'), (14, 'Sold'), (15, 'Let'), (26, 'Draft'), (16, 'Unpublish'),
(17, 'Publish'), (18, 'Action'), (19, 'Sold'), (20, 'Let'), (27, 'Draft'), (21, 'Unpublish'),
(22, 'Publish'), (23, 'Action'), (24, 'Sold'), (25, 'Let'), (1,  'Draft'),  (2, 'Unpublish'),
(29, NULL), (30, NULL), (35, 'Test_val_1'), (31, 'Test_val_2'),
(45, 'Single_1'), (46, 'Single_2');

    请注意，我添加了 2 个单例值 (Single_1和Single_2) 来检查如何计算DISTINCT表中大于 1 条记录的值......使用“经典”SQL 时要困难得多 - 使用窗口函数，它是很容易。

    新的“简单”查询是：

    SELECT
  COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
  test;

    （新）结果：

    Unique count
          10

    注意这个新查询和我的旧查询的小提琴底部结果的极端差异！EXPLAIN ANALYZE

    此查询演示了如何轻松挑选出具有多个记录的DISINCT值：status_to

    SELECT COUNT(rn) OVER (PARTITION BY rn) AS "Unique count"
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1
GROUP BY status_to
ORDER BY status_to
LIMIT 1;

    结果：

    Unique count
           8

    8 = 10（总计）减去 2 个单例！

    我还包括了这个 SQL——它可以让你轻松地（手动）计算UNIQUE条目的数量。

    SELECT status_to, MAX(rn)
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1        <<==== ****
GROUP BY status_to
ORDER BY status_to;

    结果：

    status_to    MAX(rn)
  Action          4
  Draft           4
  Let             4
  Publish         4
  Sold            4
  Test_val_1      2
  Test_val_2      2
  Unpublish       4
8 rows              <<---- note 8 rows!

    我在第二个小提琴中留下了更多的片段——如果你真的想了解 SQL，我会邀请你看看新旧版本，并尝试使用 PostgreSQL 运行代码——这很有趣！

    DISTINCT最后，如果您希望使用所有值及其s动态构造一行（根据您的原始问题）COUNT()，那么您可以使用某种形式的RECURSIVE CTE- 但这是另一个问题！

    • 2

  3. jkavalik：您应该改为：

    select status_to, count(1) 
from crm_logs 
group by status_to

    它有行而不是列的数据，但这是一个细节。如果确实需要，也可以使用动态 SQL 来解决，但group by作为基础查询仍然更好。

    Akina：在 MySQL 中，您可能使用的不是SUM(CASE WHEN status_to = 'Draft' THEN 1 END)但更简单（和清晰）SUM(status_to = 'Draft')。

    此外，如果没有匹配的行，此表达式将生成零而不是 NULL。

    count(status_to = 'Draft') 无论值如何，都会计算所有非空值。仅当不存在非空值时SUM(status_to = 'Draft')才会产生空status_to值。

    Lennart :count(nullif(status_to = 'Draft',0)) 会处理这两种情况。

    • 0

相关问题