Mysql-使用COALESCE检索正确的值? [英] MySql - Retrieve correct values using COALESCE?

问题描述

我在这里有一个SQL提琴: http://www.sqlfiddle.com/#!2/fbd48/1

I have an sql fiddle here: http://www.sqlfiddle.com/#!2/fbd48/1

花朵的颜色应该是红色，熊的颜色应该是棕色，但是两种颜色都应该显示红色.

The colors should be red for flower and brown for bear, but it shows red for both.

不确定COALESCE是否适合这里，但是是否在类似下面的内容中被接受: MySQL-检索行来自不同表的值，具体取决于表中的行的值

Not sure if COALESCE fits here, but was in the accepted answer for something similar here: MySQL - Retrieve row value from different table depending on value of row in a table

推荐答案

SELECT mem.member_name, g.*
, coalesce(f.flower_color, b.bear_color) as color
from members mem
inner join general g on mem.member_id = g.member_id
left join flowers f on g.gift_item_id = f.flower_id AND g.gift_item = 'flower'
left join bears b on g.gift_item_id = b.bear_id AND g.gift_item = 'bear'
WHERE g.month='june'

没有办法将花与熊区别开来，所以我在连接处添加了测试.这很臭.您可能会重新考虑自己的架构.

There was no way to differentiate a flower from a bear, so I added tests to the joins. This is pretty smelly. you might rethink your schema.

这篇关于Mysql-使用COALESCE检索正确的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！