22,209
社区成员
发帖
与我相关
我的任务
分享
select u.caozuoyuan_gndm,m.moduleid,m.[MKName]
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
where u.caozuoyuan_xm='管理员'
/*
caozuoyuan_gndm moduleid MKName
1,2,3,4,5,6 1 增加车辆信息
1,2,3,4,5,6 2 管理车辆信息
1,2,3,4,5,6 3 管理换油宝
1,2,3,4,5,6 4 base
1,2,3,4,5,6 5 车型查找
1,2,3,4,5,6 6 人力资源
*/
select u.caozuoyuan_gndm,m.moduleid,m.[MKName]
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
where u.caozuoyuan_xm='管理员'
/*
caozuoyuan_gndm moduleid MKName
1,2,3,4,5,6 1 增加车辆信息
1,2,3,4,5,6 2 管理车辆信息
1,2,3,4,5,6 3 管理换油宝
1,2,3,4,5,6 4 base
1,2,3,4,5,6 5 车型查找
1,2,3,4,5,6 6 人力资源
*/
select u.caozuoyuan_gndm,m.moduleid,m.[MKName]
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
/*
caozuoyuan_gndm moduleid MKName
1,2,3,4,5,6 1 增加车辆信息
1,2,3,4,5,6 2 管理车辆信息
1,2,3,4,5,6 3 管理换油宝
1,2,3,4,5,6 4 base
1,2,3,4,5,6 5 车型查找
1,2,3,4,5,6 6 人力资源
*/
您这段代码 虽然简洁 但是我怎么没看明白呢 是如何 界定是那个用户的 条件在那里? 应该有 where caozuoyuan_xm='管理员' 没找到应该在那里加if object_id('[user_caozuoyuan]') is not null drop table [user_caozuoyuan]
go
create table [user_caozuoyuan]([Id] int,[caozuoyuan_xm] varchar(6),[caozuoyuan_password] int,[caozuoyuan_Gndm] varchar(11),[dept_mc] varchar(2),[GongSiMc] varchar(4))
insert [user_caozuoyuan]
select 18,'管理员',1234567,'1,2,3,4,5,6','01','总部'
if object_id('[ModuleName]') is not null drop table [ModuleName]
go
create table [ModuleName]([Moduleid] int,[MKjibie] int,[MKpath] varchar(10),[MkId] varchar(19),[MKName] varchar(12),[MKpid] varchar(5),[paixu] int)
insert [ModuleName]
select 1,3,'huanyoubao','add_che_info.jsp','增加车辆信息','forms',4 union all
select 2,3,'huanyoubao','guanli_che_info.jsp','管理车辆信息','forms',3 union all
select 3,3,'huanyoubao','forms','管理换油宝','base',2 union all
select 4,1,'huanyoubao','换油宝管理','base','1',null union all
select 5,2,'huanyoubao','chexing_cha.jsp','车型查找','base',1 union all
select 6,1,'hr','hrgl','人力资源','hr',2 union all
select 7,2,'hr','dangan','人事档案','hr',1
select u.caozuoyuan_gndm,m.moduleid,m.[MKName]
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
/*
caozuoyuan_gndm moduleid MKName
1,2,3,4,5,6 1 增加车辆信息
1,2,3,4,5,6 2 管理车辆信息
1,2,3,4,5,6 3 管理换油宝
1,2,3,4,5,6 4 base
1,2,3,4,5,6 5 车型查找
1,2,3,4,5,6 6 人力资源
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-23 14:29:23
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[user_caozuoyuan]
if object_id('[user_caozuoyuan]') is not null drop table [user_caozuoyuan]
go
create table [user_caozuoyuan]([Id] int,[caozuoyuan_xm] varchar(6),[caozuoyuan_password] int,[caozuoyuan_Gndm] varchar(11),[dept_mc] varchar(2),[GongSiMc] varchar(4))
insert [user_caozuoyuan]
select 18,'管理员',1234567,'1,2,3,4,5,6','01','总部'
--> 测试数据:[ModuleName]
if object_id('[ModuleName]') is not null drop table [ModuleName]
go
create table [ModuleName]([Moduleid] int,[MKjibie] int,[MKpath] varchar(10),[MkId] varchar(19),[MKName] varchar(12),[MKpid] varchar(5),[paixu] int)
insert [ModuleName]
select 1,3,'huanyoubao','add_che_info.jsp','增加车辆信息','forms',4 union all
select 2,3,'huanyoubao','guanli_che_info.jsp','管理车辆信息','forms',3 union all
select 3,3,'huanyoubao','forms','管理换油宝','base',2 union all
select 4,1,'huanyoubao','换油宝管理','base','1',null union all
select 5,2,'huanyoubao','chexing_cha.jsp','车型查找','base',1 union all
select 6,1,'hr','hrgl','人力资源','hr',2 union all
select 7,2,'hr','dangan','人事档案','hr',1
--------------开始查询--------------------------
SELECT a.*,Moduleid, MKName
FROM (
select
id,
a.[caozuoyuan_xm],
SUBSTRING([caozuoyuan_Gndm],number,CHARINDEX(',',[caozuoyuan_Gndm]+',',number)-number) as [caozuoyuan_Gndm]
from
[user_caozuoyuan] a,master..spt_values
where
number >=1 and number<=len([caozuoyuan_Gndm])
and type='p'
and substring(','+[caozuoyuan_Gndm],number,1)=',')a LEFT JOIN [ModuleName] b ON a.[caozuoyuan_Gndm]=b.[Moduleid]
----------------结果----------------------------
/*
id caozuoyuan_xm caozuoyuan_Gndm Moduleid MKName
----------- ------------- --------------- ----------- ------------
18 管理员 1 1 增加车辆信息
18 管理员 2 2 管理车辆信息
18 管理员 3 3 管理换油宝
18 管理员 4 4 base
18 管理员 5 5 车型查找
18 管理员 6 6 人力资源
*/
if object_id('[user_caozuoyuan]') is not null drop table [user_caozuoyuan]
go
create table [user_caozuoyuan]([Id] int,[caozuoyuan_xm] varchar(6),[caozuoyuan_password] int,[caozuoyuan_Gndm] varchar(11),[dept_mc] varchar(2),[GongSiMc] varchar(4))
insert [user_caozuoyuan]
select 18,'管理员',1234567,'1,2,3,4,5,6','01','总部'
if object_id('[ModuleName]') is not null drop table [ModuleName]
go
create table [ModuleName]([Moduleid] int,[MKjibie] int,[MKpath] varchar(10),[MkId] varchar(19),[MKName] varchar(12),[MKpid] varchar(5),[paixu] int)
insert [ModuleName]
select 1,3,'huanyoubao','add_che_info.jsp','增加车辆信息','forms',4 union all
select 2,3,'huanyoubao','guanli_che_info.jsp','管理车辆信息','forms',3 union all
select 3,3,'huanyoubao','forms','管理换油宝','base',2 union all
select 4,1,'huanyoubao','换油宝管理','base','1',null union all
select 5,2,'huanyoubao','chexing_cha.jsp','车型查找','base',1 union all
select 6,1,'hr','hrgl','人力资源','hr',2 union all
select 7,2,'hr','dangan','人事档案','hr',1
select u.caozuoyuan_gndm,m.moduleid
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
/*
caozuoyuan_gndm moduleid
1,2,3,4,5,6 1
1,2,3,4,5,6 2
1,2,3,4,5,6 3
1,2,3,4,5,6 4
1,2,3,4,5,6 5
1,2,3,4,5,6 6
*/
select u.caozuoyuan_gndm,m.moduleid
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-23 14:29:23
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[user_caozuoyuan]
if object_id('[user_caozuoyuan]') is not null drop table [user_caozuoyuan]
go
create table [user_caozuoyuan]([Id] int,[caozuoyuan_xm] varchar(6),[caozuoyuan_password] int,[caozuoyuan_Gndm] varchar(11),[dept_mc] varchar(2),[GongSiMc] varchar(4))
insert [user_caozuoyuan]
select 18,'管理员',1234567,'1,2,3,4,5,6','01','总部'
--> 测试数据:[ModuleName]
if object_id('[ModuleName]') is not null drop table [ModuleName]
go
create table [ModuleName]([Moduleid] int,[MKjibie] int,[MKpath] varchar(10),[MkId] varchar(19),[MKName] varchar(12),[MKpid] varchar(5),[paixu] int)
insert [ModuleName]
select 1,3,'huanyoubao','add_che_info.jsp','增加车辆信息','forms',4 union all
select 2,3,'huanyoubao','guanli_che_info.jsp','管理车辆信息','forms',3 union all
select 3,3,'huanyoubao','forms','管理换油宝','base',2 union all
select 4,1,'huanyoubao','换油宝管理','base','1',null union all
select 5,2,'huanyoubao','chexing_cha.jsp','车型查找','base',1 union all
select 6,1,'hr','hrgl','人力资源','hr',2 union all
select 7,2,'hr','dangan','人事档案','hr',1
--------------开始查询--------------------------
SELECT *
FROM (
select
id,
a.[caozuoyuan_xm],
SUBSTRING([caozuoyuan_Gndm],number,CHARINDEX(',',[caozuoyuan_Gndm]+',',number)-number) as [caozuoyuan_Gndm]
from
[user_caozuoyuan] a,master..spt_values
where
number >=1 and number<=len([caozuoyuan_Gndm])
and type='p'
and substring(','+[caozuoyuan_Gndm],number,1)=',')a LEFT JOIN [ModuleName] b ON a.[caozuoyuan_Gndm]=b.[Moduleid]
----------------结果----------------------------
/*
id caozuoyuan_xm caozuoyuan_Gndm Moduleid MKjibie MKpath MkId MKName MKpid paixu
----------- ------------- --------------- ----------- ----------- ---------- ------------------- ------------ ----- -----------
18 管理员 1 1 3 huanyoubao add_che_info.jsp 增加车辆信息 forms 4
18 管理员 2 2 3 huanyoubao guanli_che_info.jsp 管理车辆信息 forms 3
18 管理员 3 3 3 huanyoubao forms 管理换油宝 base 2
18 管理员 4 4 1 huanyoubao 换油宝管理 base 1 NULL
18 管理员 5 5 2 huanyoubao chexing_cha.jsp 车型查找 base 1
18 管理员 6 6 1 hr hrgl 人力资源 hr 2
*/
select u.caozuoyuan_gndm,m.moduleid
from user_caozuoyuan u
inner join ModuleName m
on charindex(','+cast(m.moduleid as varchar)+',',','+u.caozuoyuan_gndm+',') >0
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-23 14:18:12
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[user_caozuoyuan] varchar(6),[caozuoyuan_gndm] varchar(11))
insert [huang]
select 18,'管理员','1,2,3,4,5,6'
--------------开始查询--------------------------
select
id,
a.[user_caozuoyuan],
SUBSTRING([caozuoyuan_gndm],number,CHARINDEX(',',[caozuoyuan_gndm]+',',number)-number) as [caozuoyuan_gndm]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([caozuoyuan_gndm])
and type='p'
and substring(','+[caozuoyuan_gndm],number,1)=','
----------------结果----------------------------
/*
id user_caozuoyuan caozuoyuan_gndm
----------- --------------- ---------------
18 管理员 1
18 管理员 2
18 管理员 3
18 管理员 4
18 管理员 5
18 管理员 6
*/