因此,我必须将一个表联接到另一个表的值上,但是它应该只从联接的表返回1个值。
这些表之间的关系非常模糊-我正在处理的数据的性质也是如此。在“”饼干'表的名称可能是任何一个CookieAttr1
,CookieAttr2
或CookieAttr3
。
Cookies
|-----------------------------|
| CookieName | CookieValue |
|-------------+---------------|
| Choc-Chip | 1200 |
| Dough | 500 |
|-----------------------------|
CookieColor
|-----------------------------------------------------------------|
| Id | CookieAttr1 | CookieAttr2 | CookieAttr3 | Color |
|-----+---------------+---------------+---------------+-----------|
| 1 | | Choc-Chip | Dough | Red |
| 2 | | Choc-Chip | Crumbs | Orange |
| 3 | | Sultanas | Dough | Red |
| 4 | | Choc-Chip | Dough | Blue |
| 5 | Dough | | Rounded | Purple |
| 6 | Dough | Big | Rounded | Green |
| 7 | Rounded | | Rounded | Pink |
|-----------------------------------------------------------------|
到目前为止,该表是按优先级进行连接的-如果CookieAttr1
为null,则将在其上进行连接CookieAttr2
,然后再进行连接CookieAttr3
(它将始终具有一个值)
SELECT
CookieName,
CookieValue,
Color
FROM Cookies
LEFT OUTER JOIN (
SELECT
Color,
CookieAttr1,
CookieAttr2,
CookieAttr3
FROM CookieColor
GROUP BY CookieAttr3
) CookieColor
ON COALESCE(CookieAttr1, CookieAttr2, CookieAttr3) = CookieName
出现问题是,该表可以返回重复项,因为按分组进行分组CookieAttr3
可能具有CookieAttr2
与其连接的多个相同值。
CookieColor (GROUPED BY `CookieAttr3`)
|-----------------------------------------------------------------|
| Id | CookieAttr1 | CookieAttr2 | CookieAttr3 | Color |
|-----+---------------+---------------+---------------+-----------|
| 1 | | Choc-Chip | Dough | Red |
| 2 | | Choc-Chip | Crumbs | Orange |
| 5 | Dough | | Rounded | Purple |
|-----------------------------------------------------------------|
==========> result of query
|-----------------------------------------|
| CookieName | CookieValue | Color |
|-------------+---------------+-----------|
| Choc-Chip | 1200 | Red |
| Choc-Chip | 1200 | Orange |
| Dough | 500 | Purple |
|-----------------------------------------|
==========> Preferred Result
|-----------------------------------------|
| CookieName | CookieValue | Color |
|-------------+---------------+-----------|
| Choc-Chip | 1200 | Red |
| Dough | 500 | Purple |
|-----------------------------------------|
我怎样才能只为“ Choc-Chip”获得一个价值?
您始终可以COALESCE
在一个子查询中使用a ,但是您的group by并不是很清楚
SELECT CookieAttr, Color, CookieValue
FROM
(
SELECT `Id`,
COALESCE(CookieAttr1, CookieAttr2, CookieAttr3) CookieAttr,
`Color`
FROM CookieColor
) C
JOIN Cookies
ON C.CookieAttr = Cookies.CookieName
输出
但是不知道group by
你想要什么。
| CookieAttr | Color | CookieValue |
|------------|--------|-------------|
| Choc-Chip | Orange | 1200 |
| Choc-Chip | Blue | 1200 |
| Choc-Chip | Red | 1200 |
| Dough | Purple | 500 |
| Dough | Green | 500 |
如果添加
GROUP BY CookieAttr;
但是颜色是随机的。
| CookieAttr | Color | CookieValue |
|------------|--------|-------------|
| Choc-Chip | Orange | 1200 |
| Dough | Purple | 500 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句