数据库联合查找

最新推荐文章于 2023-05-12 14:26:14 发布

原创最新推荐文章于 2023-05-12 14:26:14 发布 · 391 阅读

0 ·

CC 4.0 BY-SA版权

数据结构专栏收录该内容

6 篇文章

订阅专栏

本文通过一系列SQL查询语句展示了如何从复杂的数据表中筛选特定条件下的记录，包括去除重复记录、按时间排序等操作，对于理解数据库查询优化及提高查询效率具有较高的参考价值。

-- SELECT * FROM t_videoTalkRecord;

-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id)
-- FROM t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND
-- currentLoginUserId = '9999' AND recordType = 3 ORDER BY currentRecordSaveTimeStamp DESC;

-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3 ORDER BY currentRecordSaveTimeStamp DESC;

-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3;

-- SELECT * FROM t_videoTalkRecord WHERE userId IN(SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3);

-- SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' GROUP BY userId HAVING count(*) >= 1
--
-- SELECT * FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 2 GROUP BY userId HAVING count(*) >= 1

-- SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 2 GROUP BY userId HAVING count(*) >= 1

-- //查找去过重的未接记录
-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 2 GROUP BY userId HAVING count(*) >= 1) AND
-- currentLoginUserId = '9999' AND recordType = 2 ORDER BY currentRecordSaveTimeStamp DESC;

-- //好友关系去除重复
-- SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 3 GROUP BY userId HAVING count(*) >= 1
--
-- //查找好友去除重复 - 好友userid
-- SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 3 GROUP BY userId HAVING count(*) >= 1) AND
-- currentLoginUserId = '9999' AND recordType = 3;

-- //查找未接记录已经去重未接重复记录并且去除非好友未接记录
-- SELECT * FROM (SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 2 GROUP BY userId HAVING count(*) >= 1) AND
-- currentLoginUserId = '9999' AND recordType = 2) WHERE userId IN(SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 3 GROUP BY userId HAVING count(*) >= 1) AND
-- currentLoginUserId = '9999' AND recordType = 3);

-- //查找好友已经去重好友重复记录
SELECT * FROM (SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 3 GROUP BY userId HAVING count(*) >= 1) AND
currentLoginUserId = '9999' AND recordType = 3) WHERE userId IN(SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 3 GROUP BY userId HAVING count(*) >= 1) AND
currentLoginUserId = '9999' AND recordType = 3) ORDER BY currentRecordSaveTimeStamp DESC;

-- SELECT * FROM t_videoTalkRecord WHERE id IN () AND
-- currentLoginUserId = '9999' AND recordType = 2 ORDER BY currentRecordSaveTimeStamp DESC;

-- SELECT * FROM t_videoTalkRecord WHERE currentLoginUserId = '9999' AND recordType = 2;

-- SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 2;

-- SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1

-- SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3

-- SELECT * FROM t_videoTalkRecord WHERE userId IN (SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3);

-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- (SELECT * FROM t_videoTalkRecord WHERE userId IN (SELECT userId FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 2)) GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3 ORDER BY currentRecordSaveTimeStamp DESC;

-- SELECT * FROM t_videoTalkRecord WHERE userId IN(SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3 ORDER BY currentRecordSaveTimeStamp DESC);

-- SELECT * FROM t_videoTalkRecord WHERE userId IN (SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1));

-- SELECT * FROM t_videoTalkRecord WHERE id IN (SELECT max(id) FROM
-- t_videoTalkRecord GROUP BY userId HAVING count(*) >= 1) AND currentLoginUserId = '9999'
-- AND recordType = 3