在对“链接”表的查询返回的结果中,我还想添加一个额外的列,该列显示从JOIN中选择的“DisplayName”值,该值以每行“toTable”的值为条件.此外,有时’DisplayName’值是列连接的结果.
我的问题是我该怎么做?
我可以写单独的查询并与UNION ALL结合,但我想知道是否有条件的方法来做它
“链接”表中的每个条目描述两个表(可能有3个或更多个表)中的一对条目之间的链接.
链接表:
| LinkID | fromTable | fromID | toTable | toID |
| 1 | A | 1 | B | 1 |
| 2 | A | 1 | C | 2 |
| 3 | B | 1 | C | 1 |
| 4 | C | 1 | A | 1 |
我想要的查询结果:
例如,Links.fromTable =’A’或Links.fromTable =’C’
| LinkID | fromTable | fromID | toTable | toID | DisplayName |
| 1 | A | 1 | B | 1 | Some Title |
| 2 | A | 1 | C | 2 | Loud-Quack |
| 4 | C | 1 | A | 1 | Linus |
3个包含不同列的表:
Table A:
| ID | Name |
| 1 | Linus |
–
Table B:
| ID | Title |
| 1 | some title |
–
Table C:
| ID | CategoryA | CategoryB |
| 1 | Bark | Softly |
| 2 | Quack | Loud |
–
这就是我这样做的UNION ALL,它不是很灵活:
编辑:实际上我认为以下是错误的,我现在正在看这个:
SELECT Links.*, A.Name AS DisplayName
FROM Links
JOIN A ON Links.toID = A.ID
WHERE Links.fromType IN ('A') AND Links.toType IN ('B')
UNION ALL
SELECT Links.*, CONCAT(C.CategoryB,'-',C.CategoryA) AS DisplayName
FROM Links
JOIN C ON Links.toID = C.ID
WHERE Links.fromType IN ('A') AND Links.toType IN ('C')
解决方法:
我瘦你需要内联CASE声明
SELECT a.*,
CASE toTable
WHEN 'A' THEN b.Name
WHEN 'B' THEN c.Title
WHEN 'C' THEN CONCAT(d.CategoryB , '-',d.CategoryA )
END AS DisplayName
FROM `links` a
LEFT JOIN tableA b
ON a.toID = b.ID
LEFT JOIN tableB c
ON a.toID = c.ID
LEFT JOIN tableC d
ON a.toID = d.ID
WHERE fromtable IN ('A', 'C')
标签:sql,mysql,join,conditional
来源: https://codeday.me/bug/20190901/1786081.html