所以我必须将一个表连接到另一个表中的值,但它应该只从连接的表中返回 1 个值。
这些表的关系非常模糊 - 我正在处理的数据的性质也是如此。"Cookie"表中的名称可以是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 |
|-----------------------------------------|
如何只能获得"巧克力片"的一个值?
您始终可以在一个子查询中使用COALESCE
,但您的分组依据不清楚
SQL 小提琴演示
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 |