我在查询中使用多个 Left Join 时遇到了一些麻烦。有些表与左表是一对一的关系，有些是一对多的关系。查询看起来像这样:

Select 
    files.filename,
    coalesce(count(distinct case
                when dm_data.weather like '%clear%' then 1
                    end),
            0) as clear,
    coalesce(count(distinct case
                when dm_data.weather like '%lightRain%' then 1
                    end),
            0) as lightRain,
    coalesce(count(case
                when kc_data.type like '%bicycle%' then 1
                    end),
            0) as bicycle,
    coalesce(count(case
                when kc_data.type like '%bus%' then 1
                    end),
            0) as bus,
    coalesce(count(case
                when kpo_data.movement like '%walking%' then 1
                    end),
            0) as walking,
    coalesce(count(case
                when kpo_data.type like '%pedestrian%' then 1
                    end),
            0) as pedestrian
from
    files
        left join
    dm_data ON dm_data.id = files.id
        left join
    kc_data ON kc_data.id = files.id
        left join
    kpo_data ON kpo_data.id = files.id
where
    files.filename in (X, Y, Z, ........)
group by files.filename;

这里，dm_data 表与 'files' 表具有一对一关系(这就是我使用 'Distinct' 的原因)，而 kc_data 和 kpo_data 数据与 'files' 表具有一对多关系. (对于一个 files.id，kc_data 和 kpo_data 可以有 10 到 20 行)。此查询工作正常。

当我添加另一个与另一个一对多表 pd_markings 的左连接时，问题就出现了(一个 files.id 可以有 100 行)。

Select 
    files.filename,
    coalesce(count(distinct case
                when dm_data.weather like '%clear%' then 1
                    end),
            0) as clear,
    coalesce(count(distinct case
                when dm_data.weather like '%lightRain%' then 1
                    end),
            0) as lightRain,
    coalesce(count(case
                when kc_data.type like '%bicycle%' then 1
                    end),
            0) as bicycle,
    coalesce(count(case
                when kc_data.type like '%bus%' then 1
                    end),
            0) as bus,
    coalesce(count(case
                when kpo_data.movement like '%walking%' then 1
                    end),
            0) as walking,
    coalesce(count(case
                when kpo_data.type like '%pedestrian%' then 1
                    end),
            0) as pedestrian,
    **coalesce(count(case
                when pd_markings.movement like '%walking%' then 1
                    end),
            0) as walking**
from
    files
        left join
    dm_data ON dm_data.id = files.id
        left join
    kc_data ON kc_data.id = files.id
        left join
    kpo_data ON kpo_data.id = files.id
        left join
    **kpo_data ON pd_markings.id = files.id**
where
    files.filename in (X, Y, Z, ........)
group by files.filename;

现在所有的值都变成了彼此的倍数。有任何想法吗？？？

请注意，前两列返回 1 或 0 值。这实际上是期望的结果，因为一对一关系表对任何 files.id 只有 1 行或 0 行，所以如果我不使用 'Distinct' 那么结果值是错误的(我猜是因为其他表针对同一文件返回多于一行。id)不，不幸的是，除了"file"表之外，我的表没有自己唯一的 ID 列。

最佳答案

您需要flatten the results您的查询，以获得正确的计数。

您说您的文件表与其他表之间存在一对多关系

如果SQL只有关键字LOOKUP，而不是把所有东西都塞进JOIN关键字，那么应该很容易推断出A表和B表之间的关系是不是一个-一对一，使用 JOIN 会自动表示一对多。我离题了。不管怎样，我应该已经推断出你的文件对dm_data是一对多的；而且，针对 kc_data 的文件也是一对多的。 LEFT JOIN 是第一个表和第二个表之间的关系是一对多的另一个暗示；这不是确定的，有些编码器只是用 LEFT JOIN 编写所有内容。查询中的 LEFT JOIN 没有任何问题，但如果查询中有多个一对多表，那肯定会失败，你的查询将针对其他行生成重复行。

from
    files
        left join
    dm_data ON dm_data.id = files.id
        left join
    kc_data ON kc_data.id = files.id

因此，根据这些知识，您表明文件与 dm_data 是一对多的，并且它也与 kc_data 是一对多的。我们可以得出结论，将这些连接链接起来并将它们分组到一个整体查询中是有问题的。

一个例子，如果你有三个表，即 app(files)、ios_app(dm_data)、android_app(kc_data)，这是 ios 的示例数据:

test=# select * from ios_app order by app_code, date_released;
 ios_app_id | app_code | date_released | price  
------------+----------+---------------+--------
          1 | AB       | 2010-01-01    | 1.0000
          3 | AB       | 2010-01-03    | 3.0000
          4 | AB       | 2010-01-04    | 4.0000
          2 | TR       | 2010-01-02    | 2.0000
          5 | TR       | 2010-01-05    | 5.0000
(5 rows)

这是你的 android 的数据:

test=# select * from android_app order by app_code, date_released;
.android_app_id | app_code | date_released |  price  
----------------+----------+---------------+---------
              1 | AB       | 2010-01-06    |  6.0000
              2 | AB       | 2010-01-07    |  7.0000
              7 | MK       | 2010-01-07    |  7.0000
              3 | TR       | 2010-01-08    |  8.0000
              4 | TR       | 2010-01-09    |  9.0000
              5 | TR       | 2010-01-10    | 10.0000
              6 | TR       | 2010-01-11    | 11.0000
(7 rows)    

如果您仅使用此查询:

select x.app_code, 
    count(i.date_released) as ios_release_count, 
    count(a.date_released) as android_release_count
from app x
left join ios_app i on i.app_code = x.app_code
left join android_app a on a.app_code = x.app_code
group by x.app_code
order by x.app_code

输出将是错误的:

 app_code | ios_release_count | android_release_count 
----------+-------------------+-----------------------
 AB       |                 6 |                     6
 MK       |                 0 |                     1
 PM       |                 0 |                     0
 TR       |                 8 |                     8
(4 rows)

您可以将链式联接视为笛卡尔积，因此如果您在第一个表上有 3 行，在第二个表上有 2 行，则输出将为 6

这是可视化，看到每个 ios AB 都有 2 个重复的 android AB。有 3 个 ios AB，那么当您执行 COUNT(ios_app.date_released) 时计数是多少？那将变成6；与 COUNT(android_app.date_released) 相同，这也将是 6。同样地，每个 ios TR 有 4 个重复的 android TR，在 ios 中有 2 个 TR，因此我们可以计数8.

.app_code | ios_release_date | android_release_date 
----------+------------------+----------------------
 AB       | 2010-01-01       | 2010-01-06
 AB       | 2010-01-01       | 2010-01-07
 AB       | 2010-01-03       | 2010-01-06
 AB       | 2010-01-03       | 2010-01-07
 AB       | 2010-01-04       | 2010-01-06
 AB       | 2010-01-04       | 2010-01-07
 MK       |                  | 2010-01-07
 PM       |                  | 
 TR       | 2010-01-02       | 2010-01-08
 TR       | 2010-01-02       | 2010-01-09
 TR       | 2010-01-02       | 2010-01-10
 TR       | 2010-01-02       | 2010-01-11
 TR       | 2010-01-05       | 2010-01-08
 TR       | 2010-01-05       | 2010-01-09
 TR       | 2010-01-05       | 2010-01-10
 TR       | 2010-01-05       | 2010-01-11
(16 rows)

因此，您应该做的是在将每个结果连接到其他表和查询之前展平每个结果。

如果您的数据库支持 CTE，请使用它。它非常简洁并且非常 self 记录:

with ios_app_release_count_list as
(
 select app_code, count(date_released) as ios_release_count
 from ios_app
 group by app_code
)
,android_release_count_list as
(
 select app_code, count(date_released) as android_release_count 
 from android_app 
 group by app_code  
)
select
 x.app_code, 
 coalesce(i.ios_release_count,0) as ios_release_count, 
 coalesce(a.android_release_count,0) as android_release_count
from app x
left join ios_app_release_count_list i on i.app_code = x.app_code
left join android_release_count_list a on a.app_code = x.app_code
order by x.app_code;

而如果您的数据库还没有 CTE 功能，比如 MySQL，您应该改为这样做:

select x.app_code, 
 coalesce(i.ios_release_count,0) as ios_release_count, 
 coalesce(a.android_release_count,0) as android_release_count
from app x
left join
(
 select app_code, count(date_released) as ios_release_count
 from ios_app
 group by app_code
) i on i.app_code = x.app_code
left join
(
 select app_code, count(date_released) as android_release_count 
 from android_app 
 group by app_code   
) a on a.app_code = x.app_code
order by x.app_code

该查询和 CTE 风格的查询将显示正确的输出:

 app_code | ios_release_count | android_release_count 
----------+-------------------+-----------------------
 AB       |                 3 |                     2
 MK       |                 0 |                     1
 PM       |                 0 |                     0
 TR       |                 2 |                     4
(4 rows)

现场测试

不正确的查询:http://www.sqlfiddle.com/#!2/9774a/2

正确查询:http://www.sqlfiddle.com/#!2/9774a/1

关于MySql:多个左连接给出错误的输出，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10431660/