改就是这么改了,至于你自己的逻辑是否正确我就不清楚了. 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
改成这样楼主看行不行. 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
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
楼上两位的写法也是一种思路,但我认为逻辑分支上有遗漏. 子陌老大的逻辑语句我没细看. 总之上上面那位的逻辑语句肯定是有问题的.我们将 这几个条件表达式用几个值来代替: 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'
如果按 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))
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
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))
@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
@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
子陌老大的逻辑语句我没细看. 总之上上面那位的逻辑语句肯定是有问题的.我们将
这几个条件表达式用几个值来代替:
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'
or (NOT a.id='7') AND (acceptPersonID=@id ) AND (mailType=a.id) OR (sendPersonID=@id ) and (mailTypeSend=a.id))
的case when就是挺好.因条件需要判断的or 和and太多,就一下when比较方法,能看出逻辑表达的意思,
谢谢,以上的高手