拆分并加入Where子句MySql

Question

我有两张桌子。

Table First

+----+---------+-------+
| ID | Name    | URl   |
+----+---------+-------+
| 1  | Product | http1 |
+----+---------+-------+
| 2  | Cheif   | http2 |
+----+---------+-------+
| 3  | Client  | http3 |
+----+---------+-------+

Table Second
+-----+----------------+-------------+
| ID  | Dashboard      | Definition  |
+-----+----------------+-------------+
| 1.1 | Product,Cheif  | Overview    |
+-----+----------------+-------------+
| 2.1 | Cheif          | malpractice |
+-----+----------------+-------------+
| 3.1 | Client,Product | Vanity      |
+-----+----------------+-------------+

我期待着产出

+----+----------------+-------------+-------------+
| ID | Dashboard      | Definition  | Url         |
+----+----------------+-------------+-------------+
| 1  | Product,Cheif  | Overview    | http1,http2 |
+----+----------------+-------------+-------------+
| 2  | Cheif          | malpractice | http2       |
+----+----------------+-------------+-------------+
| 3  | Client,Product | Vanity      | http3,http1 |
+----+----------------+-------------+-------------+

我尝试使用if、locate、substring然后创建视图进行拆分，结果发现别名不能在where子句上使用。

拆分逻辑如下所示。

SELECT IF(
        LOCATE(‘,’, `dashboard`) > 0,
        SUBSTRING(`dashboard`, 1, LOCATE(‘,’, `dashboard`) - 1),
        `u_dashboard`
    ) AS memberfirst,
    IF(
        LOCATE(‘,’, `dashboard`) > 0,
        SUBSTRING(`dashboard`, LOCATE(‘,’, `dashboard`) + 1),
        NULL
    ) AS memberlast
FROM `table`;

Answer 1

您可以使用FIND_IN_SET()连接表并使用GROUP_CONCAT()进行聚合：

select t2.id, t2.dashboard, t2.definition,
       group_concat(t1.url order by find_in_set(t1.name, t2.dashboard)) url
from TableSecond t2 left join TableFirst t1
on find_in_set(t1.name, t2.dashboard)
group by t2.id, t2.dashboard, t2.definition

见演示。

结果：

>  id | dashboard      | definition  | url        
> --: | :------------- | :---------- | :----------
> 1.1 | Product,Cheif  | Overview    | http1,http2
> 2.1 | Cheif          | malpractice | http2      
> 3.1 | Client,Product | Vanity      | http3,http1