select * from Question where (Id=(select case when Mid>0 then Mid else 29 end from Question as t2 where Id=29) or Mid=(select case when Mid>0 then Mid else 29 end from Question as t2 where Id=29)) and isnull(Reply,'')=''
这段代码可以实现我想要的效果,但是有点长了,有没有办法简化这段代码,求解~~~
------解决方案--------------------
简化了一点点:
select * from Question t
where exists(select 1 from Question as t2 where Id=29 and
case when Mid>0 then t.mid else t.id end= case when Mid>0 then Mid else 29 end)
and isnull(Reply,'')=''
------解决方案--------------------
WITH a1 AS
(
SELECT CASE WHEN Mid > 0 THEN Mid ELSE 29 END Mid FROM Question WHERE Id = 29
)
SELECT *
FROM Question a
JOIN a1 b ON a.id=b.mid OR a.mid=b.mid
WHERE ISNULL(a.Reply, '') = ''
------解决方案--------------------
WITH a1 AS
(
SELECT CASE WHEN Mid > 0 THEN Mid ELSE 29 END Mid FROM Question
WHERE Id = 29
)
SELECT *
FROM Question a
JOIN a1 b ON a.id=b.mid OR a.mid=b.mid
WHERE (a.Reply IS NULL OR a.Reply='')
------解决方案--------------------
select * from Question A left join Question B on A.id=B.Mid where A.id=29 and B.mid>0
and A.Reply is null