在"Links"表的查询返回结果中,我还想添加一个额外的列,该列显示"DisplayName"值,该值是根据每行"toTable"的值从JOIN
条件中选择的。此外,有时"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 |
我想要的查询结果:WHERE链接.fromTable='A'或链接.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')
I thin您需要inline 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')
SQLFiddle演示
好吧,使用您的示例查询,您可以这样做:
SELECT Links.*, COALESCE(A.Name,CONCAT(C.CategoryB,'-',C.CategoryA)) AS DisplayName
FROM Links
LEFT OUTER JOIN A ON Links.toID = A.ID
AND Links.fromType IN ('A') AND Links.toType IN ('B')
LEFT OUTER JOIN C ON Links.toID = C.ID
AND Links.fromType IN ('A') AND Links.toType IN ('C')
只要您总是从联接的表中最多获得一个匹配(也就是说,您没有两个具有相同Links.fromType IN ...
和Links.toType IN ...
条件的不同联接),这就可以工作。