34,576
社区成员
发帖
与我相关
我的任务
分享
if object_id('[action]') is not null
drop table [action]
go
create table [action](id int, actionname varchar(20), [action] varchar(20))
----------- ----------------- -----------------
insert [action] select 1 , '安装论坛', 'anchuangluntan'
insert [action] select 2 , '管理版主', 'guanlibanzhu'
insert [action] select 3 , '管理帖子', 'guanlitiezi'
go
if object_id('actiongroup') is not null
drop table actiongroup
go
create table actiongroup(id int, [action] varchar(20), groupid varchar(20))
----------- ----------------- -----------------
insert actiongroup select 1 , 'anchuangluntan', 1
insert actiongroup select 2 , 'guanlibanzhu' , 1
insert actiongroup select 3 , 'guanlitiezi' , 1
insert actiongroup select 4 , 'guanlibanzhu' , 2
insert actiongroup select 5 , 'guanlitiezi' , 2
insert actiongroup select 6 , 'guanlitiezi' , 3
go
select * from action
select * from actiongroup
--表连接
select ag.id,a.actionname,ag.action
from action a,actiongroup ag
where a.action = ag.action
and ag.groupid = 1
--子查询
select ag.id,actionname=(select actionname from action where action=ag.action),ag.action
from actiongroup ag where ag.groupid = 1
/**
id actionname action
----------- -------------------- --------------------
1 安装论坛 anchuangluntan
2 管理版主 guanlibanzhu
3 管理帖子 guanlitiezi
(所影响的行数为 3 行)
id actionname action
----------- -------------------- --------------------
1 安装论坛 anchuangluntan
2 管理版主 guanlibanzhu
3 管理帖子 guanlitiezi
(所影响的行数为 3 行)
**/
-->生成测试数据
if object_id('[action]') is not null
drop table [action]
go
create table [action](id int, actionname varchar(20), [action] varchar(20))
----------- ----------------- -----------------
insert [action] select 1 , '安装论坛', 'anchuangluntan'
insert [action] select 2 , '管理版主', 'guanlibanzhu'
insert [action] select 3 , '管理帖子', 'guanlitiezi'
go
if object_id('actiongroup') is not null
drop table actiongroup
go
create table actiongroup(id int, [action] varchar(20), groupid varchar(20))
----------- ----------------- -----------------
insert actiongroup select 1 , 'anchuangluntan', 1
insert actiongroup select 2 , 'guanlibanzhu' , 1
insert actiongroup select 3 , 'guanlitiezi' , 1
insert actiongroup select 4 , 'guanlibanzhu' , 2
insert actiongroup select 5 , 'guanlitiezi' , 2
insert actiongroup select 6 , 'guanlitiezi' , 3
go
declare @id int
set @id=3 --输入groupid
select a.* from [action] a,actiongroup b where a.[action]=b.[action] and b.groupid=@id
-->生成测试数据
if object_id('[action]') is not null
drop table [action]
go
create table [action](id int, actionname varchar(20), [action] varchar(20))
----------- ----------------- -----------------
insert [action] select 1 , '安装论坛', 'anchuangluntan'
insert [action] select 2 , '管理版主', 'guanlibanzhu'
insert [action] select 3 , '管理帖子', 'guanlitiezi'
go
if object_id('actiongroup') is not null
drop table actiongroup
go
create table actiongroup(id int, [action] varchar(20), groupid varchar(20))
----------- ----------------- -----------------
insert actiongroup select 1 , 'anzhuangluntan', 1
insert actiongroup select 2 , 'guanlibanzhu' , 1
insert actiongroup select 3 , 'guanlitiezi' , 1
insert actiongroup select 4 , 'guanlibanzhu' , 2
insert actiongroup select 5 , 'guanlitiezi' , 2
insert actiongroup select 6 , 'guanlitiezi' , 3
go
select a.*,b.id actioncolumnid
from [action] a
join actiongroup b
on a.id=b.id
where a.id >=1
/*
id actionname action actioncolumnid
----------- -------------------- -------------------- --------------
1 安装论坛 anchuangluntan 1
2 管理版主 guanlibanzhu 2
3 管理帖子 guanlitiezi 3
(3 行受影响)
*/
select a.*,b.id actioncolumnid
from [action] a
join actiongroup b
on a.id=b.id
where a.id >=2
/*
id actionname action actioncolumnid
----------- -------------------- -------------------- --------------
2 管理版主 guanlibanzhu 2
3 管理帖子 guanlitiezi 3
(2 行受影响)
*/
select a.*,b.id actioncolumnid
from [action] a
join actiongroup b
on a.id=b.id
where a.id >=3
/*
id actionname action actioncolumnid
----------- -------------------- -------------------- --------------
3 管理帖子 guanlitiezi 3
(1 行受影响)
*/
这样?-->生成测试数据
if object_id('[action]') is not null
drop table [action]
go
create table [action](id int, actionname varchar(20), [action] varchar(20))
----------- ----------------- -----------------
insert [action] select 1 , '安装论坛', 'anchuangluntan'
insert [action] select 2 , '管理版主', 'guanlibanzhu'
insert [action] select 3 , '管理帖子', 'guanlitiezi'
go
if object_id('actiongroup') is not null
drop table actiongroup
go
create table actiongroup(id int, [action] varchar(20), groupid varchar(20))
----------- ----------------- -----------------
insert actiongroup select 1 , 'anzhuangluntan', 1
insert actiongroup select 2 , 'guanlibanzhu' , 1
insert actiongroup select 3 , 'guanlitiezi' , 1
insert actiongroup select 4 , 'guanlibanzhu' , 2
insert actiongroup select 5 , 'guanlitiezi' , 2
insert actiongroup select 6 , 'guanlitiezi' , 3
go
-->本例用自定义函数解决
create function get_str(@id int)
returns varchar(200)
as
begin
declare @s varchar(200)
select @s=isnull(@s+',','')+actionname from [action] where @id<=id
return @s
end
go
-->开始查询
select *,dbo.get_str(groupid) actionname from actiongroup
-->结果
/*
id action groupid actionname
----------- -------------------- -------------------- -----------------------------
1 anzhuangluntan 1 安装论坛,管理版主,管理帖子
2 guanlibanzhu 1 安装论坛,管理版主,管理帖子
3 guanlitiezi 1 安装论坛,管理版主,管理帖子
4 guanlibanzhu 2 管理版主,管理帖子
5 guanlitiezi 2 管理版主,管理帖子
6 guanlitiezi 3 管理帖子
(6 行受影响)
*/
-->删除自定义函数、测试表
drop function dbo.get_str
drop table actiongroup,[action]
select *,
case when groupid = 1 then '管理论坛,管理版主,管理帖子'
when groupid = 2 then '管理版主,管理帖子'
else '管理帖子' end
from actiongroup: