参考下面代码根据需要替换一下既可''DISTINCT关键词只返回结果集合内不重复的数据行。例如,有时你可能需要找出Sales表内的公司,但是你又不想看见每个条目。于是你可以用DISTINCT对应每一公司名返回一行数据 ''qxlb 字段是此部门所有可以显示的模块列表代号集合 ssql="SELECT DISTINCT c.gnmkbh AS gnmkbh, c.cdxsmc AS cdxsmc, c.dywj AS dywj, c.cdzbh AS cdzbh,"_
&"d.* FROM (SELECT * FROM (SELECT qxlb FROM OA_QX_zw"_ &" WHERE ','+'" &zw&"'+',' LIKE '%,' + cast(zwbh AS varchar) + ',%') a INNER JOIN"_ &" (SELECT gnmkbh, cdxsmc, dywj, cdzbh FROM oa_qx_ktgnmklb where qxbz<>'X') b ON ',' + CAST(a.qxlb AS varchar(8000))"_ &" + ',' LIKE '%,' + b.GNMKBH + ',%') c INNER JOIN (SELECT name, number, sybz" & " FROM oa_qx_menumaster WHERE sybz <>'X') d ON c.cdzbh = d.number order by c.cdzbh" ''从模块 数据表中提取名称/名称代码/使用权限符 条件 sybz不等于x rs.open ssql,conn,1,1
看一下代码,有意思,就是速度很慢:--建表 create table testa(fielda int) goinsert testa select 1 union all select 10 union all select 100 union all select 1000 gocreate table testb(fieldb int) goinsert testb select 1 union all select 3 union all select 4 union all select 6 union all select 6 ---可以重复 union all select 8 union all select 120 union all select 500 union all select 3 union all select 1 union all select 990 union all select 10 go --建函数create function GetMyValue (@Num int) returns int as begin declare @t table (id int identity(1,1),fieldb int) insert @t(fieldb) select fieldb from testb declare @Count int select @Count=count(*) from testb declare @i int declare @sum table (fieldb int,ids varchar(1000)) insert @sum select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=1 while @i<=@Count begin insert @Sum select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t where s.ids not like ','+cast(t.id as varchar(10))+'%' and s.fieldb+t.fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=@i+1 end declare @Rec int select @Rec=@Num-max(fieldb) from @Sum return @Rec end--调用 select fielda,dbo.GetMyValue (fielda) as MyValue from testa
有错误,更正如下:--建函数create function GetMyValue (@Num int) returns int as begin declare @t table (id int identity(1,1),fieldb int) insert @t(fieldb) select fieldb from testb declare @Count int select @Count=count(*) from testb declare @i int declare @sum table (fieldb int,ids varchar(1000)) insert @sum select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=1 while @i<=@Count begin insert @Sum select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t where s.ids not like '%,'+cast(t.id as varchar(10))+'%' and s.fieldb+t.fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=@i+1 end declare @Rec int select @Rec=@Num-max(fieldb) from @Sum return @Rec end
有错误,更正如下:--建函数create function GetMyValue (@Num int) returns int as begin declare @t table (id int identity(1,1),fieldb int) insert @t(fieldb) select fieldb from testb declare @Count int select @Count=count(*) from testb declare @i int declare @sum table (fieldb int,ids varchar(1000)) insert @sum select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=1 while @i<=@Count begin insert @Sum select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t where s.ids not like '%,'+cast(t.id as varchar(10))+'%' and s.fieldb+t.fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) return 0 set @i=@i+1 end declare @Rec int select @Rec=@Num-max(fieldb) from @Sum return @Rec end
create function GetMyCharValue (@Num int) returns varchar(1000) as begin declare @t table (id int identity(1,1),fieldb int) insert @t(fieldb) select fieldb from testb declare @Count int select @Count=count(*) from testb declare @i int declare @Rec Varchar(1000) declare @sum table (fieldb int,ids varchar(1000)) insert @sum select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) begin select top 1 @Rec='0 '+right(ids,len(ids)-1) from @sum where fieldb=@Num return @Rec end set @i=1 while @i<=@Count begin insert @Sum select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t where s.ids not like '%,'+cast(t.id as varchar(10))+'%' and s.fieldb+t.fieldb<=@Num if exists (select 1 from @sum where fieldb=@Num) begin select top 1 @Rec='0 '+right(ids,len(ids)-1) from @sum where fieldb=@Num return @Rec end set @i=@i+1 end select top 1 @Rec=cast(@Num-fieldb as varchar(10))+' '+right(ids,len(ids)-1) from @sum a where fieldb=(select max(fieldb) from @sum) return @Rec end
''qxlb 字段是此部门所有可以显示的模块列表代号集合
ssql="SELECT DISTINCT c.gnmkbh AS gnmkbh, c.cdxsmc AS cdxsmc, c.dywj AS dywj, c.cdzbh AS cdzbh,"_
&"d.* FROM (SELECT * FROM (SELECT qxlb FROM OA_QX_zw"_
&" WHERE ','+'" &zw&"'+',' LIKE '%,' + cast(zwbh AS varchar) + ',%') a INNER JOIN"_
&" (SELECT gnmkbh, cdxsmc, dywj, cdzbh FROM oa_qx_ktgnmklb where qxbz<>'X') b ON ',' + CAST(a.qxlb AS varchar(8000))"_
&" + ',' LIKE '%,' + b.GNMKBH + ',%') c INNER JOIN (SELECT name, number, sybz" & " FROM oa_qx_menumaster WHERE sybz <>'X') d ON c.cdzbh = d.number order by c.cdzbh"
''从模块 数据表中提取名称/名称代码/使用权限符 条件 sybz不等于x
rs.open ssql,conn,1,1
create table testa(fielda int)
goinsert testa
select 1
union all
select 10
union all
select 100
union all
select 1000
gocreate table testb(fieldb int)
goinsert testb
select 1
union all
select 3
union all
select 4
union all
select 6
union all
select 6 ---可以重复
union all
select 8
union all
select 120
union all
select 500
union all
select 3
union all
select 1
union all
select 990
union all
select 10
go
--建函数create function GetMyValue (@Num int)
returns int
as
begin
declare @t table (id int identity(1,1),fieldb int)
insert @t(fieldb)
select fieldb from testb
declare @Count int
select @Count=count(*) from testb
declare @i int
declare @sum table (fieldb int,ids varchar(1000))
insert @sum
select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0 set @i=1
while @i<=@Count
begin
insert @Sum
select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t
where s.ids not like ','+cast(t.id as varchar(10))+'%'
and s.fieldb+t.fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0
set @i=@i+1
end
declare @Rec int
select @Rec=@Num-max(fieldb) from @Sum
return @Rec
end--调用
select fielda,dbo.GetMyValue (fielda) as MyValue
from testa
returns int
as
begin
declare @t table (id int identity(1,1),fieldb int)
insert @t(fieldb)
select fieldb from testb
declare @Count int
select @Count=count(*) from testb
declare @i int
declare @sum table (fieldb int,ids varchar(1000))
insert @sum
select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0 set @i=1
while @i<=@Count
begin
insert @Sum
select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t
where s.ids not like '%,'+cast(t.id as varchar(10))+'%'
and s.fieldb+t.fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0
set @i=@i+1
end
declare @Rec int
select @Rec=@Num-max(fieldb) from @Sum
return @Rec
end
returns int
as
begin
declare @t table (id int identity(1,1),fieldb int)
insert @t(fieldb)
select fieldb from testb
declare @Count int
select @Count=count(*) from testb
declare @i int
declare @sum table (fieldb int,ids varchar(1000))
insert @sum
select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0 set @i=1
while @i<=@Count
begin
insert @Sum
select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t
where s.ids not like '%,'+cast(t.id as varchar(10))+'%'
and s.fieldb+t.fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
return 0
set @i=@i+1
end
declare @Rec int
select @Rec=@Num-max(fieldb) from @Sum
return @Rec
end
returns varchar(1000)
as
begin
declare @t table (id int identity(1,1),fieldb int)
insert @t(fieldb)
select fieldb from testb
declare @Count int
select @Count=count(*) from testb
declare @i int
declare @Rec Varchar(1000)
declare @sum table (fieldb int,ids varchar(1000))
insert @sum
select fieldb,','+cast(id as varchar(10)) from @t where fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
begin
select top 1 @Rec='0 '+right(ids,len(ids)-1) from @sum where fieldb=@Num
return @Rec
end
set @i=1
while @i<=@Count
begin
insert @Sum
select s.fieldb+t.fieldb,s.ids+','+cast(t.id as varchar(10)) from @sum s,@t t
where s.ids not like '%,'+cast(t.id as varchar(10))+'%'
and s.fieldb+t.fieldb<=@Num
if exists (select 1 from @sum where fieldb=@Num)
begin
select top 1 @Rec='0 '+right(ids,len(ids)-1) from @sum where fieldb=@Num
return @Rec
end
set @i=@i+1
end
select top 1 @Rec=cast(@Num-fieldb as varchar(10))+' '+right(ids,len(ids)-1) from @sum a where fieldb=(select max(fieldb) from @sum)
return @Rec
end