好象这样就可以了: SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND Flag <> 0
楼上说得: 好象这样就可以了: SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND Flag <> 0 这根原来的意思是有区别的,如果存在这么两条记录:DepartID RoleID ObjectID Action Flag #### 01 01 1 0 #### 01 01 1 1 那么Action就会被选中,而我要求是不能选中的不知有没有人知道求两个集合差集用什么?SQL帮助中有如下提示: Except 查找两个集合之间不同的项,可以选择保留重复项。语法 Except(«Set1», «Set2»[, ALL])注释 在查找不同的项之前先消除两个集合中的重复项。可选的 ALL 标志保留重复项。清除 «Set1» 中的匹配重复项并保留非匹配重复项。我照此做却又错误,请高手指正
那就这样写好了: SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND Action not in (SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND Flag = 0)
多谢楼上,这样问题就解决了。 但顺便再问一下,SQL中有没有求集合差的运算
知道intersect是求交集的 求差不清楚(反正不是减^_^)SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND (Flag <> 0 or Flag is null)
集合与记录集不是同一概念,在SQL中没有集合的概念
CREATE PROCEDURE dbo.upSecListObjectActions ( @DepartID uniqueidentifier, @RoleID int, @ObjectID int) AS SET NOCOUNT ON SELECT Action FROM dbo.tblRoleRights WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID AND (Flag <> 0 or Flag is null) GO ---------------------- 试试吧
SELECT Action
FROM dbo.tblRoleRights
WHERE DepartID = @DepartID AND
ObjectID = @ObjectID AND
RoleID = @RoleID AND
Flag <> 0
楼上说得:
好象这样就可以了:
SELECT Action
FROM dbo.tblRoleRights
WHERE DepartID = @DepartID AND
ObjectID = @ObjectID AND
RoleID = @RoleID AND
Flag <> 0
这根原来的意思是有区别的,如果存在这么两条记录:DepartID RoleID ObjectID Action Flag
#### 01 01 1 0
#### 01 01 1 1
那么Action就会被选中,而我要求是不能选中的不知有没有人知道求两个集合差集用什么?SQL帮助中有如下提示:
Except
查找两个集合之间不同的项,可以选择保留重复项。语法
Except(«Set1», «Set2»[, ALL])注释
在查找不同的项之前先消除两个集合中的重复项。可选的 ALL 标志保留重复项。清除 «Set1» 中的匹配重复项并保留非匹配重复项。我照此做却又错误,请高手指正
SELECT Action FROM dbo.tblRoleRights
WHERE DepartID = @DepartID AND
ObjectID = @ObjectID AND
RoleID = @RoleID AND
Action not in
(SELECT Action FROM dbo.tblRoleRights
WHERE DepartID = @DepartID AND
ObjectID = @ObjectID AND
RoleID = @RoleID AND
Flag = 0)
但顺便再问一下,SQL中有没有求集合差的运算
WHERE DepartID = @DepartID AND ObjectID = @ObjectID AND RoleID = @RoleID
AND (Flag <> 0 or Flag is null)
@DepartID uniqueidentifier,
@RoleID int,
@ObjectID int)
AS
SET NOCOUNT ON SELECT
Action
FROM
dbo.tblRoleRights
WHERE
DepartID = @DepartID AND
ObjectID = @ObjectID AND
RoleID = @RoleID AND
(Flag <> 0 or Flag is null) GO
----------------------
试试吧