gpt4 book ai didi

mysql - 如何添加 1 个内连接而不是 3 个内连接

转载 作者:行者123 更新时间:2023-11-29 10:31:41 25 4
gpt4 key购买 nike

在这里,我尝试内部联接 SEARCHED_TAGS 表和 USER 表。这里我使用相同的连接三次。不仅如此,下面我多次使用这个查询。是否会导致查询速度减慢?如何才能将该查询写入一次而不是 3 次。

下面您可以看到查询。

(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
inner join USER usr
on st.SEARCH_BY=usr.USER_ID
where (st.SEARCH_TEXT like :tagText) and (st.SEARCH_BY = :userId) and (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)
UNION
(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
inner join USER usr
on st.SEARCH_BY = usr.USER_ID
where (st.SEARCH_TEXT like :tagText) and not (st.SEARCH_BY = :userId) and (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)
UNION
(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
inner join USER usr
on st.SEARCH_BY= usr.USER_ID
where (st.SEARCH_TEXT like :tagText) and not (st.SEARCH_BY = :userId) and not (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)

我可以这样写而不是上面吗?

inner join USER usr
on st.SEARCH_BY=usr.USER_ID
(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
where (st.SEARCH_TEXT like :tagText) and (st.SEARCH_BY = :userId) and (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)
UNION
(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
where (st.SEARCH_TEXT like :tagText) and not (st.SEARCH_BY = :userId) and (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)
UNION
(
select st.SEARCH_TEXT as SEARCH_TEXT
from SEARCHED_TAGS st
where (st.SEARCH_TEXT like :tagText) and not (st.SEARCH_BY = :userId) and not (usr.DEP_ID = :userDep)
order by st.SEARCH_TIME desc
limit 2
)

谢谢..

最佳答案

由于限制语句,这可能是普通 MySQL 所能做到的最好的。但是使用 session 变量我们可以做得更好一些:

SELECT SEARCH_TEXT
FROM
(
SELECT SEARCH_TEXT, grp,
(@num:=if(@group = grp, @num +1, if(@group := grp, 1, 1))) row_number
FROM
(
SELECT
st.SEARCH_TEXT as SEARCH_TEXT,
st.SEARCH_TIME,
CASE WHEN (st.SEARCH_TEXT like :tagText) AND (st.SEARCH_BY = :userId) AND
(usr.DEP_ID = :userDep) THEN 1
WHEN (st.SEARCH_TEXT like :tagText) AND NOT (st.SEARCH_BY = :userId) AND
(usr.DEP_ID = :userDep) THEN 2
WHEN (st.SEARCH_TEXT like :tagText) AND NOT (st.SEARCH_BY = :userId) AND NOT
(usr.DEP_ID = :userDep) THEN 3
ELSE 4 END AS grp
FROM SEARCHED_TAGS st
INNER JOIN USER usr
ON st.SEARCH_BY = usr.USER_ID
) t1
CROSS JOIN (select @num:=0, @group:=null) t2
ORDER BY grp, t1.SEARCH_TIME DESC
) t
WHERE
t.row_number <= 2 AND
t.grp <= 3;

此代码未经测试,主要是因为您的查询太复杂,无法轻松测试,但如果您遇到任何问题,我可以与您一起迭代。

关于mysql - 如何添加 1 个内连接而不是 3 个内连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47277533/

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