加入Coalesce（Formula1，Formula2，Formula3）

Using MySQL, I'm trying to do a JOIN similar to how you would use a JOIN with OR statements like:

使用MySQL,我正在尝试进行类似于如何将JOIN与OR语句一起使用的JOIN:

JOIN bioguide ON (bioguide.fulldistrict=house.districtname) OR
(left(bioguide.firstname,3)=left(house.first,3) AND bioguide.lastname=house.last) OR
(bioguide.fulldistrict=house.districtname AND bioguide.lastname=house.last)

As you probably know, the problem with doing it this way is that if a bunch of different ways work, you get multiple results per row.

您可能知道,这样做的问题在于,如果一堆不同的方法有效,那么每行会得到多个结果。

My hope is that there's a way to use the JOIN and use it the way the COALESCEfunction works, essentially stating (even though it doesn't work):

我希望有一种方法可以使用JOIN并以COALESCEfunction的工作方式使用它,基本上说明了(即使它不起作用):

JOIN bioguide ON COALESCE(bioguide.firstname=house.first AND
bioguide.lastname=house.last),(left(bioguide.firstname,3)=left(house.first,3) AND
bioguide.lastname=house.last),(bioguide.fulldistrict=house.districtname AND
bioguide.lastname=house.last))

If that worked, it'd be telling SQL to first JOIN on (bioguide.firstname=house.first AND bioguide.lastname=house.last) then on (left(bioguide.firstname,3)=left(house.first,3) AND bioguide.lastname=house.last), etc.

如果有效,它会告诉SQL首先加入(bioguide.firstname = house.first AND bioguide.lastname = house.last)然后开启(左(bioguide.firstname,3)= left(house.first,3 )和bioguide.lastname = house.last)等

Is something like this possible? Please let me know if other information would be helpful.

这样的事情可能吗?如果其他信息有用,请告诉我。

Thanks everybody!

1 个解决方案

#1

You can structure the query with the different matches in different tables, and then use coalesce() in the select to get what you want:

您可以使用不同表中的不同匹配来构造查询,然后在select中使用coalesce()来获取所需内容:

select coalesce(bg1.col1, bg2.col2, bg3.col3)
from . . . left outer join
     bioguide bg1
     on bg1.fulldistrict=house.districtname left outer join
     bioguide bg2
     on left(bg2.firstname,3)=left(house.first,3) AND bg2.lastname=house.last left outer join
     bioguide bg3
     on bg3.fulldistrict=house.districtname AND bg3.lastname=house.last

This can still result in multiple rows, if there are multiple matches for one of the comparisons (if the same row in bioguide matches all three, you have no problem with duplicated rows).

这仍然会导致多行,如果其中一个比较有多个匹配(如果bioguide中的同一行匹配所有三个,则重复行没有问题)。

If so, then judiciously use a group by:

如果是这样,那么明智地使用一个组:

group by <id column that identifies each row in the result set>

#1