gpt4 book ai didi

mysql - 使 MySQL 'IN' 搜索返回所有相关行

转载 作者:行者123 更新时间:2023-11-30 23:11:24 24 4
gpt4 key购买 nike

我必须在三个表中使用 mysql 执行查询。

interface
---------------------
|id | name |
---------------------
|1 | inter1 |
---------------------
|2 | inter2 |
---------------------

inetrapp
--------------------------------------
|id | interid | appid |
--------------------------------------
|1 | 1 | 20 |
--------------------------------------
|2 | 1 | 21 |
--------------------------------------
|3 | 2 | 22 |
--------------------------------------
|4 | 2 | 23 |
--------------------------------------

app
--------------------------------------
id | appid | appname |
--------------------------------------
1 | 20 | sap |
--------------------------------------
2 | 21 | sap1 |
--------------------------------------
3 | 22 | wes |
--------------------------------------
4 | 23 | wes1 |
--------------------------------------

查询是这样的

 select  ti.id as id,  
ti.name as name,
GROUP_CONCAT(DISTINCT tapp.appname order by ti.id SEPARATOR ",") as applications
from interface ti inner join interapp tiap on ti.id = tiap.interid inner join app as tapp on tiap.appid = tapp.appid where tapp.appname in ("sap1");

这给了我以下结果

--------------------------------------
|id | name | applications |
--------------------------------------
|1 | inter1 | sap1 |
--------------------------------------

但我需要所有与 inter1 有关系的应用程序,(即)我希望得到以下结果。

--------------------------------------
|id | name | applications |
--------------------------------------
|1 | inter1 | sap,sap1 |
--------------------------------------

请指导我修改上述查询。提前谢谢你。

最佳答案

SELECT  a.id,
a.name,
GROUP_CONCAT(DISTINCT c.appname ORDER BY a.id SEPARATOR ',') result
FROM interface a
INNER JOIN inetrapp b
ON a.id = b.interid
INNER JOIN app c
ON b.appid = c.appid
INNER JOIN
(
SELECT DISTINCT a.interid
FROM inetrapp a
INNER JOIN app b
ON a.appid = b.appid
WHERE b.appname IN ('sap1')
) d ON a.id = d.interid
GROUP By a.id, a.name

关于mysql - 使 MySQL 'IN' 搜索返回所有相关行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19583635/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com