mysql多变左联接,MySql：多个左连接提供错误的输出

我在查询中使用多个左连接时有点麻烦.有些表与左表有一对一的关系,有些与一对多关系.查询如下所示：

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)不幸的是,我的表除了’files’表之外没有自己唯一的ID列.

解决方法:

你说你从文件表到其他表有一对多的关系

如果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(文件),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,请使用.它非常整洁,非常自我记录：

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)

现场测试

标签：mysql

来源： https://codeday.me/bug/20190626/1290675.html