两张表:
表结构如下:
CREATE TABLE [dbo].[tblPermission](
[PermissionID] [uniqueidentifier] NOT NULL,
[PermissionName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ModuleID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_tblPermission] PRIMARY KEY CLUSTERED
(
[PermissionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[tblObject](
[ObjectID] [uniqueidentifier] NOT NULL,
[ParentObjectID] [uniqueidentifier] NOT NULL,
[ModuleID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]初始数据如下:
insert dbo.tblObject
values
('88880000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000008888','88880000-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('00000001-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000001','00000001-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('00000002-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000002','00000002-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('AF119E17-AA16-45EA-9587-1E9F08422E21','00000000-0000-0000-0000-000000000002','00000002-0000-0000-0000-000000000002')insert tblPermission
values(newid(),'test1','88880000-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test2','00000001-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test3','00000002-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test4','00000002-0000-0000-0000-000000000002')结构大体如上:
我的语句如下:
SELECT OBJ.ObjectID,OBJ.ParentObjectID,OBJ.MOduleID,P.PermissionID,P.PermissionName
FROM
tblObject OBJ,tblPermission P
WHERE OBJ.ModuleID=P.ModuleID但是我需要的数据是:
假如tblObject这个表中的ObjectID与ModuleID相等,查询结果的ParentObjectID为ObjectID的值,如果不相等话就返回ParentObjectID本身的值。
谢谢各位指点。。
表结构如下:
CREATE TABLE [dbo].[tblPermission](
[PermissionID] [uniqueidentifier] NOT NULL,
[PermissionName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ModuleID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_tblPermission] PRIMARY KEY CLUSTERED
(
[PermissionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[tblObject](
[ObjectID] [uniqueidentifier] NOT NULL,
[ParentObjectID] [uniqueidentifier] NOT NULL,
[ModuleID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]初始数据如下:
insert dbo.tblObject
values
('88880000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000008888','88880000-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('00000001-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000001','00000001-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('00000002-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000002','00000002-0000-0000-0000-000000000001')
insert dbo.tblObject
values
('AF119E17-AA16-45EA-9587-1E9F08422E21','00000000-0000-0000-0000-000000000002','00000002-0000-0000-0000-000000000002')insert tblPermission
values(newid(),'test1','88880000-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test2','00000001-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test3','00000002-0000-0000-0000-000000000001')
insert tblPermission
values(newid(),'test4','00000002-0000-0000-0000-000000000002')结构大体如上:
我的语句如下:
SELECT OBJ.ObjectID,OBJ.ParentObjectID,OBJ.MOduleID,P.PermissionID,P.PermissionName
FROM
tblObject OBJ,tblPermission P
WHERE OBJ.ModuleID=P.ModuleID但是我需要的数据是:
假如tblObject这个表中的ObjectID与ModuleID相等,查询结果的ParentObjectID为ObjectID的值,如果不相等话就返回ParentObjectID本身的值。
谢谢各位指点。。
OBJ.ObjectID,
ParentObjectID=case when obj.ObjectID=obj.ModuleID then ObjectID else ParentObjectID end ,
OBJ.MOduleID,
P.PermissionID,
P.PermissionName
FROM
tblObject OBJ,tblPermission P
WHERE OBJ.ModuleID=P.ModuleID
SELECT distinct OBJ.ObjectID,
OBJ.ParentObjectID=(CASE WHEN OBJ.OBJECTID=OBJ.MODULEID THEN OBJECTID ELSE PARENTOBJECTID END),
OBJ.MOduleID,P.PermissionID,P.PermissionName
FROM tblObject OBJ inner join tblPermission P on OBJ.ModuleID=P.ModuleID
from tblObject
/*88880000-0000-0000-0000-000000000001
00000001-0000-0000-0000-000000000001
00000002-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000002*/
--不太明白你的意思
SELECT OBJ.ObjectID,OBJ.ParentObjectID,OBJ.MOduleID,P.PermissionID,P.PermissionName
FROM
(
select objectID=(case when ObjectID=ModuleID then ObjectID else ParentObjectID end),ParentObjectID,ModuleID
from tblObject
) OBJ,tblPermission P
WHERE OBJ.ModuleID=P.ModuleID
/*ObjectID ParentObjectID MOduleID PermissionID PermissionName
------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ --------------------------------------------------
00000000-0000-0000-0000-000000000002 00000000-0000-0000-0000-000000000002 00000002-0000-0000-0000-000000000002 6970F125-2EFA-4662-99B6-259680073C26 test4
88880000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000008888 88880000-0000-0000-0000-000000000001 629BC8D4-F406-4B61-BD97-7959EF7E4D13 test1
00000001-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 00000001-0000-0000-0000-000000000001 91A321AD-F019-4FEA-9CAE-997C6F1C52D8 test2
00000002-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 00000002-0000-0000-0000-000000000001 FF067EAF-421E-442C-8E14-E27E4B86229B test3(4 row(s) affected)
*/
OBJ.ObjectID,OBJ.ParentObjectID,OBJ.MOduleID,P.PermissionID,P.PermissionName
FROM
(select objectID=(case ObjectID when ModuleID then ObjectID else ParentObjectID end),ParentObjectID,ModuleID
from
tblObject
) OBJ,tblPermission P
WHERE
OBJ.ModuleID=P.ModuleID