两张表
[oblog_log] :blog文章表
[oblog_comment] :对blog文章的评论我现在想列出用户15714的没有被放入回收站的blog文章的评论。求SQL语句怎么样构建。用到的字段
[oblog_log]:
logid——blog文章的ID isdel——blog文章是否被放入了回收站(1=是/0=否)
[oblog_comment]:
userid——评论所属blog文章的用户ID
select *
from [oblog_comment]
where userid = 15714
and (select isdel from [oblog_log] where logid = ????) != 1
[oblog_log] :blog文章表
[oblog_comment] :对blog文章的评论我现在想列出用户15714的没有被放入回收站的blog文章的评论。求SQL语句怎么样构建。用到的字段
[oblog_log]:
logid——blog文章的ID isdel——blog文章是否被放入了回收站(1=是/0=否)
[oblog_comment]:
userid——评论所属blog文章的用户ID
select *
from [oblog_comment]
where userid = 15714
and (select isdel from [oblog_log] where logid = ????) != 1
[oblog_comment]表中mainid字段为 评论所属blog文章的id
from oblog_log x1,oblog_comment x2
where x1.logid=x2.mainid and x1.isdel=0
from [oblog_comment] A
where userid = 15714
and exists(select 1 from oblog_log B where A.mainid=B.logid and B.isdel=0)
--logid——blog文章的ID isdel——blog文章是否被放入了回收站(1=是/0=否)
declare @oblog_comment table(userid int,mainid int)
--userid——评论所属blog文章的用户ID
insert @oblog_log values(1,0)
insert @oblog_log values(2,0)
insert @oblog_log values(3,1)
insert @oblog_comment values(15714 ,1)
insert @oblog_comment values(15714 ,2)
insert @oblog_comment values(15714 ,3)select * from @oblog_comment join @oblog_log on logid=mainid and isdel=0userid mainid logid isdel
----------- ----------- ----------- -----------
15714 1 1 0
15714 2 2 0(所影响的行数为 2 行)
from [oblog_comment]
where userid = 15714
and exists (select * from [oblog_log] where [oblog_log].logid = [oblog_comment].mainid and [oblog_log].isdel =0 )
同样