SQL计算多列去重关联的数量

Question

我有一个问题，需要在同一张表上多次进行JOIN并计算JOIN的数量。

这是数据库设置（SQL Fiddle）：

CREATE TABLE state
(
  [t_id] int,
  [true_id] int,
  [false_id] int,
  [msg] varchar(32) 
);

INSERT INTO state
  (t_id, true_id, false_id, msg)
VALUES
  (5, 6, 7, 'CASE_1'),
  (10, 11, 12, 'CASE_2'),
  (20, 21, 22, 'CASE_N'),
  (30, 31, 32, 'FOOOO');

CREATE TABLE step
(
      [id] int,
      [f_id] int,
      [state_type] int,
      [state_value] int
);

INSERT INTO step
    (id,f_id,state_type, state_value)
VALUES
    (1, 5, 5, 7),
    (2, 5, 5, 7),
    (3, 5, 5, 6),

    (4, 5, 10, 12),
    (5, 5, 10, 12),
    (6, 5, 10, 11),

    (7, 6, 10, 12),
    (8, 6, 10, 12),

    (9, 7, 20, 21),
    (10, 7, 20, 21),

    (11, 7, 30, 32),
    (12,7, 30, 31);

这是我的当前查询：

SELECT state.msg, 
  COUNT(state_true.true_id) AS Trues,
  COUNT(state_false.false_id) AS Falses
FROM state
  INNER JOIN step ON state.t_id = step.state_type
  LEFT OUTER JOIN state AS state_true ON step.state_value = state_true.true_id
  LEFT OUTER JOIN state AS state_false ON step.state_value = state_false.false_id
GROUP BY state.msg, step.f_id

这是我得到的内容：

msg     Trues   Falses
CASE_1  1       2
CASE_2  1       2
CASE_2  0       2
CASE_N  2       0
FOOOO   1       1

这是我需要的内容：

msg     Trues   Falses
CASE_1  1       0
CASE_2  1       1
CASE_N  1       0
FOOOO   1       0

解释：

我需要统计每个state_type和f_id组合中有多少个true和false。

有6个条目，其中f_id = 5 -> (1,2,3,4,5,6)。如果有一个与(f_id,state_type)相同的条目，则只应计算最后一个。因此，对于f_id 5，条目1,2,4,5不应计入计数，因为它们被3和6 覆盖。

因此，在处理前6个条目后，应该有CASE_1 true => 1 false => 0和CASE_2 true => 1 false => 0

__ 编辑 __

 TABLE step:

(1, 5, 5, 7),  -- do not count
(2, 5, 5, 7),  -- do not count
(3, 5, 5, 6),  -- this is the last entry with 
               -- (f_id,state_type) => (5,5) combination. 
               -- it overwrites the 2 previous ones => count CASE_1 true

(4, 5, 10, 12), -- do not count
(5, 5, 10, 12), -- do not count
(6, 5, 10, 11), -- count CASE_2 true

(7, 6, 10, 12), -- do not count
(8, 6, 10, 12), -- count CASE_2 false

(9, 7, 20, 21),  -- do not count
(10, 7, 20, 21), -- count CASE_N false

(11, 7, 30, 32), -- do not count
(12,7, 30, 31);  -- count FOOOO true

- eddy

---

非常好的提问方式（包含 SQL fiddle 和创建脚本）。但是，我很难理解解释。 - Sateesh Pagolu

呈现得很好，但我也有同样的感觉 :( - mohan111

你为什么要使用 true_id 和 false_id 进行连接？这是必需的吗？ - Sateesh Pagolu

我这样做是为了获取结果中的列，一个用于真值，一个用于假值。 - eddy

Answer 1

我不确定我完全理解你的意图，但也许下面的查询可能是你想要的？根据你提供的样本数据，它似乎可以产生正确的输出。

SELECT state.msg, 
  SUM(CASE WHEN true_id  = state_value THEN 1 ELSE 0 END) AS Trues,
  SUM(CASE WHEN false_id = state_value THEN 1 ELSE 0 END) AS Falses
FROM state
JOIN step ON state.t_id = step.state_type
JOIN (SELECT MAX(id) mid FROM step GROUP BY f_id, state_type) a ON a.mid = step.id
GROUP BY state.msg;

请试一试。如果我误解了，我会删除答案。

- jpw

---

是的，这看起来不错 :) - eddy