读书人

这个sql语句会简化吗

发布时间: 2013-12-05 22:27:44 作者: rapoo

这个sql语句能简化吗?



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

读书人网 >SQL Server

热点推荐