问个高手，case when中第8行=号处有错误，

[evaELLIS]

写了个存储过程，想按条件来形成where条件，但是，在case when中有错误，高手们看看



CREATE proc sp_T_Message_mailcount_w
@id varchar(50)

as
begin
SELECT *,(select count(id) from dbo.T_message_q where acceptPersonID=@id and flag='0' and mailtype=a.id)as newMsg ,
(select count(id) from dbo.T_message_q where case when a.id='7' then (mailType=a.id) ELSE (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id) end) as countMsg

FROM T_message_type a
WHERE (typeuser = @id ) OR
(typeuser = '0') AND (inde <> 'CDS')
ORDER BY id
end
GO

[fcuandy]

错误原因在于, CASE WHEN结果不能输出逻辑表达式.
我帮你改改.

[vfssqs]

WHERE 附近少了个字段

[fcuandy]

改就是这么改了,至于你自己的逻辑是否正确我就不清楚了.


SELECT *,
(select count(id)
from dbo.T_message_q
where acceptPersonID=@id and flag='0' and mailtype=a.id
)as newMsg ,
(select count(id)
from dbo.T_message_q
where 1=
(case when a.id='7' then
case when mailType=a.id then 1 else 0 end
ELSE
case when (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id) then 1 else 0 end
end)
) as countMsg
FROM T_message_type a
WHERE (typeuser = @id ) OR (typeuser = '0') AND (inde <> 'CDS')
ORDER BY id

[vfssqs]

改成这样楼主看行不行．


select count(id) from dbo.T_message_q where ((a.id='7' and mailType=a.id)
or (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id))

[子陌红尘]

CREATE proc sp_T_Message_mailcount_w
@id varchar(50)

as
begin
SELECT
*,
(select count(id) from dbo.T_message_q where acceptPersonID=@id and flag='0' and mailtype=a.id)as newMsg ,
(select count(id) from dbo.T_message_q where a.id!='7' and mailType=a.id) +
(select count(id) from dbo.T_message_q where a.id!='7' and ((acceptPersonID=@id AND mailType=a.id) OR (sendPersonID=@id and mailTypeSend=a.id)))
as countMsg
FROM
T_message_type a
WHERE
(typeuser = @id )
OR
(typeuser = '0')
AND
(inde <> 'CDS')
ORDER BY id
end
GO

[playwarcraft]

CREATE proc sp_T_Message_mailcount_w
@id varchar(50)

as
begin
SELECT *,(select count(id) from dbo.T_message_q where acceptPersonID=@id and flag='0' and mailtype=a.id)as newMsg ,
case when a.id=7 then (select count(id) from dbo.T_message_q where mailType=a.id)
ELSE (select count(id) from dbo.T_message_q where (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id))
end as countMsg
FROM T_message_type a
WHERE (typeuser = @id ) OR
(typeuser = '0') AND (inde <> 'CDS')
ORDER BY id
end
GO

[fcuandy]

楼上两位的写法也是一种思路,但我认为逻辑分支上有遗漏.
子陌老大的逻辑语句我没细看. 总之上上面那位的逻辑语句肯定是有问题的.

我们将
这几个条件表达式用几个值来代替:
A:a.id='7'
B:mailType=a.id
C:(acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id)

即,A,B,C分别代表了三个条件,然后自己画画逻辑分支图,对照楼主的逻辑就可以看出来 A OR C 有逻辑遗漏的地方.

用语句来说明就是两段代码的对比:

DECLARE @A BIT,@B BIT,@C BIT
SELECT @A=1,@B=0,@C=1 --改变A,B,C不同值的组合,就有不同的结果出现.
IF @A=1
BEGIN
IF @B=1
PRINT 'x'
END
ELSE
BEGIN
IF @C=1
PRINT 'x'
END




IF @A=1 OR @C=1
PRINT 'x'

[fcuandy]

中间插了一楼...

[playwarcraft]

居然默視我待的樓層:(

[fcuandy]

如果按 vfssqs(lailailai) 朋友的思路的话,应该这样写:

select count(id) from dbo.T_message_q where ((a.id='7' and mailType=a.id)
or (NOT a.id='7') AND (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id))

[fcuandy]

我回贴时没看到你的..

[playwarcraft]

子陌老大用2個count,相加也一樣得到結果...

[vfssqs]

fcuandy(现在流行散分)

谢谢上楼。哈哈。唉。粗心致命。

[evaELLIS]

高，都是高手呀，

fcuandy(现在流行散分)
的case when就是挺好.因条件需要判断的or 和and太多，就一下when比较方法，能看出逻辑表达的意思，
谢谢，以上的高手