select * from feed_comment where
feed_id in (select id from feed where user_id =5151)
union all
select * from feed_comment where to_id in(
select id from feed_comment where user_id = 5151
)
union all
select * from feed_comment where id in (
select to_id from feed_comment where to_id in(
select id from feed_comment where user_id = 5151
) )
order by create_time desc
一個完整的SQL陳述句，查詢關聯的太多，求教大佬如何優化
uj5u.com熱心網友回復：
have a tryselect a.* from feed_comment a, (select id from feed where user_id =5151) b
where a.feed_id = b.id
or a.to_id = b.id
union all
select a.* from feed_comment a, (select id from feed where user_id =5151) b, (select id, to_id from feed_comment ) c
where a.id = c.id
and c.to_id = b.id
uj5u.com熱心網友回復：
不對哦，資料查詢不全uj5u.com熱心網友回復：
select a.* from feed_comment a left join feed b on a.feed_id=b.id left join feed_comment c a.id=c.to_id where user_id=5151 order by a.create_time desc
隨手寫個 希望表結構提供參考下
uj5u.com熱心網友回復：
sql寫錯了。正確的話結果也不對；CREATE TABLE `feed` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`content` varchar(255) DEFAULT NULL,
`cover` varchar(255) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`location` varchar(255) DEFAULT NULL,
`medium` varchar(500) NOT NULL,
`recommend` bit(1) NOT NULL,
`status` int(11) NOT NULL,
`topic_id` bigint(20) DEFAULT NULL,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_id` bigint(20) NOT NULL,
`video` bit(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2174 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `feed_comment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`content` varchar(255) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`feed_id` bigint(20) NOT NULL,
`to_id` bigint(20) DEFAULT NULL,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4;
uj5u.com熱心網友回復：
看錯表名了，修改一下
select * from feed_comment where id in (
select to_id from feed_comment where to_id in(
select id from feed_comment where user_id = 5151
) )
這個你確定沒寫錯？同一個表，中間層to_id in 內層id, 再外層id in 中間層to_id，那不等價于外層id in 內層id，也就是等價于以下SQL。
select * from feed_comment where user_id = 5151 --內外層id一致實際上就是選出內層就可以
--所以整個就可以簡化
select * from feed_comment
where feed_id in (select id from feed where user_id =5151)
or to_id in (select id from feed_comment where user_id = 5151)
or user_id = 5151
--或者簡化為
select a.* from feed_comment a, (select id from feed where user_id =5151) b, (select id from feed_comment where user_id = 5151) c
where a.feed_id = b.id
or a.to_id = c.id
or a.user_id = 5151
uj5u.com熱心網友回復：
問題就出在這條SQL陳述句，簡化之后資料會對不上。to_id對應的是feed_comment里面的id.資料結構上面有uj5u.com熱心網友回復：
提供足夠的測驗資料及具體需求，最好再說明一下資料特征。轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/94554.html
標籤：Java EE
上一篇：一個演算法的問題，寫遞推方程式