sql在与另一个表连接之后引用一个表

rdlzhqv9 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(295)

我使用这个mysql查询来输出一个id为18的用户与其他用户的最新聊天信息。在我最后添加之前，查询一直有效 JOIN ，用于计算未查看MSG的数量( unviewed_total ). 我得到的错误是'column not found' T2.rid1 以及 T2.rid2 . 无法正确引用这些列。

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, T3.unviewed_total FROM 
                (SELECT T1.user2_id, users.uname, users.ava, max(cdate) maxDate, T1.rid rid1, T1.sec_rid rid2 FROM
                (SELECT chat.user_to user2_id, max(msg_time) cdate,request_id rid,secondary_rid sec_rid
                    FROM chat WHERE chat.user_from=18
                    GROUP BY chat.user_to
                union distinct
                (SELECT chat.user_from user2_id, max(msg_time) cdate,request_id rid,secondary_rid sec_rid
                    FROM chat WHERE chat.user_to=18
                    GROUP BY chat.user_from)) T1
                inner join users on (users.uid = T1.user2_id)
                group by T1.user2_id
                order by maxDate desc) T2
            join chat on (T2.maxDate = chat.msg_time)  
            join (SELECT COUNT(viewed) unviewed_total FROM chat WHERE viewed='0' AND user_to=18 AND request_id IN (T2.rid1,T2.rid2)) T3
            ORDER BY T2.maxDate DESC

mysql Join

来源：https://stackoverflow.com/questions/48288746/sql-referring-on-one-table-after-join-with-another-table

1条答案

按热度按时间

6vl6ewon1#

该联接的内部查询不知道表t2。我建议将连接移到select语句。
改变

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, T3.unviewed_total FROM

到

SELECT T2.maxDate, T2.ava, T2.uname,chat.user_to,chat.user_from,chat.body,chat.request_id,chat.secondary_rid, (SELECT COUNT(viewed) unviewed_total FROM chat WHERE viewed='0' AND user_to=18 AND request_id IN (T2.rid1,T2.rid2)) unviewed_total

有关此技术的详细信息：https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

赞(0）回复(0）举报 2021-06-21

我来回答

sql在与另一个表连接之后引用一个表

1条答案

相关问题

热门标签

最新问答