有更好的方法吗?
SELECT subs. * ,
CASE subs.member_type
WHEN 'member' THEN
( SELECT CONCAT_WS( ' ', members.first_name, members.last_name )
FROM members
WHERE members.id = subs.member_id)
ELSE
( SELECT members_anon.username
FROM members_anon
WHERE members_anon.id = subs.member_id)
END AS fullname,
CASE subs.member_type
WHEN 'member' THEN
( SELECT members.email
FROM members
WHERE members.id = subs.member_id)
ELSE
( SELECT members_anon.email
FROM members_anon
WHERE members_anon.id = subs.member_id)
END AS email
FROM subs
WHERE subs.item_id =19
AND subs.item_type = 'blog'
LIMIT 0 , 30
理想情况下,我希望只有一个
CASE
部分,从相关的表中返回名称和电子邮件。
subs.member_type = 'member'
,那么SELECT部分将是:subs.*,m.username,m.email
。所以ma上的LEFT OUTER
会被执行,但不会包含在结果集中。对吗?我想这不会对性能产生任何影响,因为所有的*.id
列都是主键,对吧? - Michael Robinson