SQL子查询以按列值获取行

Question

我有一个像这样的PostgreSQL表：

CREATE TABLE foo(man_id, subgroup, power, grp)
AS VALUES
    (1, 'Sub_A',  4, 'Group_A'),
    (2, 'Sub_B', -1, 'Group_A'),
    (3, 'Sub_A', -1, 'Group_B'),
    (4, 'Sub_B',  6, 'Group_B'),
    (5, 'Sub_A',  5, 'Group_A'),
    (6, 'Sub_B',  1, 'Group_A'),
    (7, 'Sub_A', -1, 'Group_B'),
    (8, 'Sub_B',  2, 'Group_B'),
    (9, 'Sub_C',  2, 'Group_B');

功率计算的工作方式如下：

• grp Sub_A中子群Group_A的总功率为(4 +5)=9
• grp Sub_B中子群Group_A的总幂为(-1)+1)=0
• grp Sub_A中子群Group_B的总幂为(-1) +(-1)= -2
• grp Sub_B中子群Group_B的总功率为(6 +2)=8

因此，Sub_A在Group_A中的幂与Sub_A在Group_B中的幂不相等。

因此，Sub_B在Group_A中的幂与Sub_B在Group_B中的幂不相等。

我想查询数据库并获取行，对于相同的subgroup名称，在所有其他grp名称之间，总power并不相等。

这样做的推荐方法是什么？

我可以找到总功率之和：

SELECT sum(power) AS total_power
FROM   foo
GROUP  BY grp

MySQL解决方案也将被接受。

Answer 1

单程：

SELECT f.*
FROM  (
   SELECT subgroup
   FROM  (
      SELECT subgroup, grp, sum(power) AS total_power
      FROM   foo
      GROUP  BY subgroup, grp
      ) sub
   GROUP  BY 1
   HAVING min(total_power) <> max(total_power)  -- can fail for NULL values;
   ) sg
JOIN foo f USING (subgroup);

在您的示例中，除了最后一个带有“Sub_C”的行外，所有行都有限定条件。

与你之前的问题密切相关：

• 对于给定的子群，所有的群都有相等的总幂吗？

类似的解释和考虑。

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=cbb27a027a05f636749e7b0db2c01231

Answer 2

我认为一个表达你的问题的方法是，你想要把一个群中的子群的幂之和，然后找出一个同名的子群是否存在于另一个具有不同幂次的群中。

第一步是像你想要的那样拥有全部的力量：

SELECT grp, subgroup, sum(power) as power
FROM foo
GROUP BY grp, subgroup

这应该会给你这样的结果：

grp      subgroup  power
-------  --------  -----
Group_A  Sub_A      9
Group_A  Sub_B      0
Group_B  Sub_A     -2
Group_B  Sub_B      8
Group_B  Sub_C      2

一旦您有了它，您就可以使用CTE将结果与其自身连接起来，以便进行比较，以获得所需的结果。您没有指定是否希望Sub_C出现，如果“not”符合“完全不同的电源”的条件，那么您需要使用左联接并检查别名b中的空值。联接中的<使每个差异只出现一次，低阶组为grp1。

WITH totals AS (
    SELECT grp, subgroup, sum(power) as power
    FROM foo
    GROUP BY grp, subgroup
    ORDER BY grp, subgroup
)
SELECT a.subgroup,
       a.grp as grp1, a.power as Power1,
       b.grp as grp2, b.power as Power2
FROM totals a
    INNER JOIN totals b ON b.subgroup = a.subgroup
                           and a.grp < b.grp
WHERE b.power <> a.power
ORDER BY a.subgroup, a.grp, b.grp 

Answer 3

with totals as (
    select grp, subgroup, sum(power) as total_power
    from foo
    group by grp, subgroup
)
select * from totals t1
where t1.total_power <> all (
    select t2.total_power from totals t2
    where t2.subgroup = t.subgroup and t2.grp <> t1.grp
)

或

with totals as (
    select grp, subgroup, sum(power) as total_power
    from foo
    group by grp, subgroup
), matches as (
    select grp, subgroup, count(*) over (partition by subgroup, total_power) as matches
)
select * from counts where matches = 1;