如何用IF / case选择内连接字段? [英] How to choose inner join field with IF/case?
问题描述
有点棘手。
我正在清理一些数据,但由于数据的大小,它需要相当长的一段时间要做,因此我需要在升级执行时运行该应用程序。所以,我有一张评论表和一张照片表。每条评论记录都存储在 object_id 中的 photo_d_id 或存储在 object_id 中的 photo_id :
I am in the middle of cleaning up some data but because of the size of the data, it's going to take quite a while to do and thus I need the app to run while the upgrade is performing. So, I have a comments table and a photos table. Each comment record has either the photo_d_id stored in object_id or the photo_id stored in object_id:
if comment_type = 8 then object_id = photo.photo_p_id
if comment_type = 17 then object_id = photo.photo_id
我需要内部加入查询中的照片表,但需要根据照片表中的正确字段执行内部连接的 comment_type 即可。这是不可能的,但举一个我想做的例子:
I need to inner join the photo table in the query but need to perform the innerjoin on the correct field from the photo table based off the comment_type. This isn't possible, but gives an example of what I want to do:
select *.comments,*.photos
FROM comments
CASE
when comments.comment_type = 8 then inner join photos on comments.object_id = photo.photo_p_id
when comments.comment_type = 17 then inner join photos on comments.object_id = photo.photo_id
任何想法?
推荐答案
尝试此查询(已修改) -
Try this query (edited) -
SELECT c.*,
CASE c.comment_type WHEN 8 THEN p1.photo_p_id WHEN 17 THEN p2.photo_id ELSE NULL END photo_id,
CASE c.comment_type WHEN 8 THEN p1.column1 WHEN 17 THEN p2.column1 ELSE NULL END column1
FROM comments c
LEFT JOIN photos p1
ON c.object_id = p1.photo_p_id
LEFT JOIN photos p2
ON c.object_id = p2.photo_id
另一个变种 -
SELECT c.*, p.*
FROM comments c
JOIN photos p
ON c.comment_type = 8 AND c.object_id = p.photo_p_id OR c.comment_type = 17 AND c.object_id = p.photo_id
这篇关于如何用IF / case选择内连接字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!