SQL - 根据来自同一查询的不同选择的查询分组

Question

我正在尝试按 id 按结果分组，但它不起作用我返回时仍然得到重复的行...这是我的查询:

SELECT firstTable.id     as id,
   secondTable.holder as holder

FROM (select tb3.id as id
  from table1 tb1
         inner join table2 tb2 on tb1.tb2_id = tb2.id
         inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
         inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
  group by tb3.id) as firstTable,
 (select id_holder,
         sum(temporaryTable.holder) as holder
  from (
         select (select cast(tb4.helper as integer)) as helper,
                count(distinct tb4.id)               as holder,
                tb3.id                               as id_holder
         from table1 tb1
                inner join table2 tb2 on tb1.tb2_id = tb2.id
                inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
                inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
         group by tb3.id, tb4.helper
       ) as temporaryTable
  where temporaryTable.helper between 7 and 8
  group by id_holder) as secondTable

Answer 1

您需要一个连接条件，这样您就不会在两个查询之间获得完整的叉积。

SELECT firstTable.id as id,
       secondTable.holder as holder
FROM (
    select tb3.id as id
    from table1 tb1
    inner join table2 tb2 on tb1.tb2_id = tb2.id
    inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
    inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
    group by tb3.id) as firstTable
JOIN (
    select id_holder,
           sum(temporaryTable.holder) as holder
    from (
        select cast(tb4.helper as integer) as helper,
               count(distinct tb4.id) as holder,
               tb3.id as id_holder
        from table1 tb1
        inner join table2 tb2 on tb1.tb2_id = tb2.id
        inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
        inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
        group by tb3.id, tb4.helper
    ) as temporaryTable
    where temporaryTable.helper between 7 and 8
    group by id_holder) as secondTable
ON firstTable.id = temporaryTable.id_holder