34,593
社区成员
发帖
与我相关
我的任务
分享
/*==============================================================*/
/* Table: permission_area 区域 */
/*==============================================================*/
create table permission_area (
area_id int identity(1,1), --区域ID
area_code varchar(32) null, --区域编码
area_name varchar(128) null, --区域名
area_parentid int null, --父区域
area_sort int null, --排序
area_level int null, --级别
area_createdate datetime null, --创建时间
area_remark varchar(max) null, --备注
constraint PK_PERMISSION_AREA primary key (area_id)
)
go
/*==============================================================*/
/* Table: permission_user 用户 */
/*==============================================================*/
create table permission_user (
user_id int identity(1,1), --用户ID
role_id int null, --角色ID
user_code varchar(32) null, --用户编码
user_name varchar(64) null, --用户名
user_Password varchar(120) null, --用户密码
user_createdate datetime null, --创建时间
user_remark varchar(max) null, --备注
constraint PK_PERMISSION_USER primary key (user_id)
)
go
/*==============================================================*/
/* Table: permission_userArea_Relation 用户区域关系 */
/*==============================================================*/
create table permission_userArea_Relation (
userarea_id int identity(1,1), --ID
user_id int null, --用户ID
area_id int null, --区域ID
userarea_createdate datetime null, --创建时间
userarea_remark varchar(max) null, --备注
constraint PK_PERMISSION_USERAREA_RELATIO primary key (userarea_id)
)
go
--这样的表你们试试.
原来的查询
select
用户账号=a.user_name,
所属区域=isnull(c.area_name,'未分配')
from Permission_user a
left JOIN Permission_userArea_Relation b on a.[user_id]=b.[user_id]
left JOIN permission_area c on b.area_id=c.area_id
select [user_name],
stuff((select distinct ','+isnull(area_name,'未分配') from Permission_userArea_Relation b
join permission_area c on b.area_id=c.area_id where a.[user_id]=b.[user_id] for xml path('')),1,1,'') [name]
from Permission_user a
group by [user_name],[user_id]
select
a.user_name,
isnull(c.area_name,'未分配') as area_name
into #tmp_Permission_user
from Permission_user a
left JOIN Permission_userArea_Relation b on a.[user_id]=b.[user_id]
left JOIN permission_area c on b.area_id=c.area_id
select user_name as 用户账号,
所属区域=stuff((select ','+area_name from #tmp_Permission_user where t.user_name=user_name for xml path('')), 1, 1, '')
from #tmp_Permission_user t
group by user_name
DROP TABLE [permission_area]
CREATE TABLE [permission_area] ( [area_id] [int] IDENTITY (1, 1) NOT NULL , [area_code] [varchar] (32) NULL , [area_name] [varchar] (128) NULL , [area_parentid] [int] NULL , [area_sort] [int] NULL , [area_level] [int] NULL , [area_createdate] [datetime] NULL , [area_remark] [text] NULL )
ALTER TABLE [permission_area] WITH NOCHECK ADD CONSTRAINT [PK_permission_area] PRIMARY KEY NONCLUSTERED ( [area_id] )
SET IDENTITY_INSERT [permission_area] ON
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 35 , '' , 'b' , 77 , 0 , 0 , '2011-08-08 19:19:57.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 36 , '' , 'c' , 0 , 0 , 0 , '2011-08-08 15:51:47.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 70 , '' , 'asdf' , 0 , 0 , 0 , '2011-08-08 17:54:37.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 72 , '' , 'aaaaaa' , 77 , 0 , 0 , '2011-08-08 18:22:56.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 73 , '' , 'bbbbbbbbb' , 72 , 0 , 0 , '2011-08-08 17:51:46.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 77 , '' , 'cccccc' , 0 , 0 , 0 , '2011-08-08 17:46:16.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 78 , '' , 'xcxcxc' , 0 , 0 , 0 , '2011-08-08 17:57:34.000' , '' )
SET IDENTITY_INSERT [permission_area] OFF
DROP TABLE [permission_area]
CREATE TABLE [permission_area] ( [area_id] [int] IDENTITY (1, 1) NOT NULL , [area_code] [varchar] (32) NULL , [area_name] [varchar] (128) NULL , [area_parentid] [int] NULL , [area_sort] [int] NULL , [area_level] [int] NULL , [area_createdate] [datetime] NULL , [area_remark] [text] NULL )
ALTER TABLE [permission_area] WITH NOCHECK ADD CONSTRAINT [PK_permission_area] PRIMARY KEY NONCLUSTERED ( [area_id] )
SET IDENTITY_INSERT [permission_area] ON
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 35 , '' , 'b' , 77 , 0 , 0 , '2011-08-08 19:19:57.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 36 , '' , 'c' , 0 , 0 , 0 , '2011-08-08 15:51:47.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 70 , '' , 'asdf' , 0 , 0 , 0 , '2011-08-08 17:54:37.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 72 , '' , 'aaaaaa' , 77 , 0 , 0 , '2011-08-08 18:22:56.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 73 , '' , 'bbbbbbbbb' , 72 , 0 , 0 , '2011-08-08 17:51:46.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 77 , '' , 'cccccc' , 0 , 0 , 0 , '2011-08-08 17:46:16.000' , '' )
INSERT [permission_area] ( [area_id] , [area_code] , [area_name] , [area_parentid] , [area_sort] , [area_level] , [area_createdate] , [area_remark] ) VALUES ( 78 , '' , 'xcxcxc' , 0 , 0 , 0 , '2011-08-08 17:57:34.000' , '' )
SET IDENTITY_INSERT [permission_area] OFF
DROP TABLE [Permission_userArea_Relation]
CREATE TABLE [Permission_userArea_Relation] ( [userarea_id] [int] IDENTITY (1, 1) NOT NULL , [user_id] [int] NULL , [area_id] [int] NULL , [userarea_createdate] [datetime] NULL , [userarea_remark] [text] NULL )
ALTER TABLE [Permission_userArea_Relation] WITH NOCHECK ADD CONSTRAINT [PK_Permission_userArea_Relation] PRIMARY KEY NONCLUSTERED ( [userarea_id] )
SET IDENTITY_INSERT [Permission_userArea_Relation] ON
INSERT [Permission_userArea_Relation] ( [userarea_id] , [user_id] , [area_id] ) VALUES ( 1 , 1 , 35 )
INSERT [Permission_userArea_Relation] ( [userarea_id] , [user_id] , [area_id] ) VALUES ( 2 , 1 , 72 )
SET IDENTITY_INSERT [Permission_userArea_Relation] OFF
select
用户账号=a.user_name,
所属区域=stuff((select ','+isnull(area_name,'') from permission_area
where area_id=b.area_id for xml path('')),1,1,'')
from Permission_user a
left JOIN Permission_userArea_Relation b on a.[user_id]=b.[user_id]
group by a.user_name,b.area_id
select [user_name],
stuff((select distinct isnull(area_name,'未分配') from Permission_userArea_Relation b
join permission_area c on b.area_id=c.area_id where a.[user_id]=b.[user_id] for xml path('')),1,1,'') [name]
from Permission_user a
group by [user_name],[user_id]
合并列值
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
select [user_name],
stuff((select distinct isnull(area_name,'未分配') from Permission_userArea_Relation b
join permission_area c on b.area_id=c.area_id where a.[user_id]=b.[user_id] for xml path('')),1,1,'') [name]
from Permission_user a
group by [user_name]