select a.username,b.rolename into # from tbl_A a inner join tbl_C c on a.userid=c.userid inner join tbl_B b on b.roleid=c.roleid select case when username=(select min(username) from # where username=a.username) then username else '' end as username, rolename from # a
select UserName = case when RoleName = (select min(RoleName) from ( select A.UserName , B.RoleName from A,B,C where A.UserID = C.UserID and C.RoleID = B.RoleID ) m where UserName=n.UserName) then UserName else '' end , RoleName from ( select A.UserName , B.RoleName from A,B,C where A.UserID = C.UserID and C.RoleID = B.RoleID ) n
select a.username,b.rolename from tbl_A a inner join tbl_C c on a.userid=c.userid inner join tbl_B b on b.roleid=c.roleid 对于数据库,数据很重要,提取/存放数据很重要, 但是没有像那样 很重视 “绣花式”的打印结果的!! 所以,我觉得 这样一个 select 就够了 楼主觉得呢?
create table tbl_A(userid int,username varchar(10)) insert tbl_A select 1 , 'A' insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10)) insert tbl_B select 1 , 'Role_A' insert tbl_B select 2 , 'Role_B' insert tbl_B select 3 , 'Role_C' insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int) insert tbl_C select 1 , 1 insert tbl_C select 1 , 3 insert tbl_C select 2 , 2 insert tbl_C select 2 , 4select a.username,b.rolename into # from tbl_A a inner join tbl_C c on a.userid=c.userid inner join tbl_B b on b.roleid=c.roleid order by a.username select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username, rolename from # a order by case when isnull(username,'')='' then 0 else 1 end drop table tbl_A,tbl_B,tbl_C,#/* username rolename ---------- ---------- A Role_A Role_C B Role_B Role_D(4 行受影响) */
create table tbl_a(UserID varchar(2), UserName varchar(2)) INSERT INTO tbl_A VALUES ('1','A') INSERT INTO tbl_A VALUES ('2','B')create table tbl_b(RoleID varchar(2), RoleName varchar(12)) INSERT INTO tbl_b VALUES ('1','Role_A') INSERT INTO tbl_b VALUES ('2','Role_B') INSERT INTO tbl_b VALUES ('3','Role_C') INSERT INTO tbl_b VALUES ('4','Role_D')create table tbl_C(UserId varchar(2), RoleID varchar(2)) INSERT INTO tbl_c VALUES ('1','1') INSERT INTO tbl_c VALUES ('1','3') INSERT INTO tbl_c VALUES ('2','2') INSERT INTO tbl_c VALUES ('2','4')select username=(case when c.RoleID=(select min(RoleID) from tbl_C where UserId=c.userid) then a.username else '' end) ,b.RoleName from tbl_c c,tbl_a a,tbl_b b where c.userid=a.userid and c.RoleID=b.RoleID order by a.usernamedrop table tbl_a,tbl_b,tbl_c
完全正确!! 谢谢 LiangCK 加分了 假如再加个Application呢? 就截取Role_后面的 应该怎么样? username rolename ApplicationName ---------- ---------- -------------- A Role_A A Role_C C B Role_B B Role_D D
注意,要把order by a.username去掉且要把各个语句要分批执行
create table tbl_a(UserID varchar(2), UserName varchar(2)) INSERT INTO tbl_A VALUES ('1','A') INSERT INTO tbl_A VALUES ('2','B') gocreate table tbl_b(RoleID varchar(2), RoleName varchar(12)) INSERT INTO tbl_b VALUES ('1','Role_A') INSERT INTO tbl_b VALUES ('2','Role_B') INSERT INTO tbl_b VALUES ('3','Role_C') INSERT INTO tbl_b VALUES ('4','Role_D') gocreate table tbl_C(UserId varchar(2), RoleID varchar(2)) INSERT INTO tbl_c VALUES ('1','1') INSERT INTO tbl_c VALUES ('1','3') INSERT INTO tbl_c VALUES ('2','2') INSERT INTO tbl_c VALUES ('2','4') goselect username=(case when c.RoleID=(select min(RoleID) from tbl_C where UserId=c.userid) then a.username else '' end) ,b.RoleName from tbl_c c,tbl_a a,tbl_b b where c.userid=a.userid and c.RoleID=b.RoleID godrop table tbl_a,tbl_b,tbl_c
create table tbl_A(userid int,username varchar(10)) insert tbl_A select 1 , 'A' insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10)) insert tbl_B select 1 , 'Role_A' insert tbl_B select 2 , 'Role_B' insert tbl_B select 3 , 'Role_C' insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int) insert tbl_C select 1 , 1 insert tbl_C select 1 , 3 insert tbl_C select 2 , 2 insert tbl_C select 2 , 4select a.username,b.rolename into # from tbl_A a inner join tbl_C c on a.userid=c.userid inner join tbl_B b on b.roleid=c.roleid order by a.username select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username, rolename,right(rolename,1) applicationname from # a order by case when isnull(username,'')='' then 0 else 1 end drop table tbl_A,tbl_B,tbl_C,#/* username rolename applicationname ---------- ---------- --------------- A Role_A A Role_C C B Role_B B Role_D D(4 行受影响) */
create table tbl_A(userid int,username varchar(10)) insert tbl_A select 1 , 'A' insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10)) insert tbl_B select 1 , 'Role_A' insert tbl_B select 2 , 'Role_B' insert tbl_B select 3 , 'Role_C' insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int) insert tbl_C select 1 , 1 insert tbl_C select 1 , 3 insert tbl_C select 2 , 2 insert tbl_C select 2 , 4select a.username,b.rolename into # from tbl_A a inner join tbl_C c on a.userid=c.userid inner join tbl_B b on b.roleid=c.roleid order by a.username select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username, rolename,stuff(rolename,1,charindex('Role_',rolename)+4,'') applicationname from # a order by case when isnull(username,'')='' then 0 else 1 end drop table tbl_A,tbl_B,tbl_C,# /* username rolename applicationname ---------- ---------- ---------------------------- A Role_A A Role_C C B Role_B B Role_D D(4 行受影响) */
from tbl_A a inner join tbl_C c
on a.userid=c.userid
inner join tbl_B b
on b.roleid=c.roleid
select case when username=(select min(username) from # where username=a.username) then username else '' end as username,
rolename
from # a
(
select A.UserName , B.RoleName from A,B,C where A.UserID = C.UserID and C.RoleID = B.RoleID
) m where UserName=n.UserName) then UserName else '' end , RoleName from
(
select A.UserName , B.RoleName from A,B,C where A.UserID = C.UserID and C.RoleID = B.RoleID
) n
from tbl_A a inner join tbl_C c
on a.userid=c.userid
inner join tbl_B b
on b.roleid=c.roleid
对于数据库,数据很重要,提取/存放数据很重要,
但是没有像那样 很重视 “绣花式”的打印结果的!!
所以,我觉得 这样一个 select 就够了 楼主觉得呢?
insert tbl_A select 1 , 'A'
insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10))
insert tbl_B select 1 , 'Role_A'
insert tbl_B select 2 , 'Role_B'
insert tbl_B select 3 , 'Role_C'
insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int)
insert tbl_C select 1 , 1
insert tbl_C select 1 , 3
insert tbl_C select 2 , 2
insert tbl_C select 2 , 4select a.username,b.rolename into #
from tbl_A a inner join tbl_C c
on a.userid=c.userid
inner join tbl_B b
on b.roleid=c.roleid
order by a.username
select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username,
rolename
from # a
order by case when isnull(username,'')='' then 0 else 1 end
drop table tbl_A,tbl_B,tbl_C,#/*
username rolename
---------- ----------
A Role_A
Role_C
B Role_B
Role_D(4 行受影响)
*/
INSERT INTO tbl_A VALUES ('1','A')
INSERT INTO tbl_A VALUES ('2','B')create table tbl_b(RoleID varchar(2), RoleName varchar(12))
INSERT INTO tbl_b VALUES ('1','Role_A')
INSERT INTO tbl_b VALUES ('2','Role_B')
INSERT INTO tbl_b VALUES ('3','Role_C')
INSERT INTO tbl_b VALUES ('4','Role_D')create table tbl_C(UserId varchar(2), RoleID varchar(2))
INSERT INTO tbl_c VALUES ('1','1')
INSERT INTO tbl_c VALUES ('1','3')
INSERT INTO tbl_c VALUES ('2','2')
INSERT INTO tbl_c VALUES ('2','4')select
username=(case when c.RoleID=(select min(RoleID) from tbl_C where UserId=c.userid) then a.username else '' end) ,b.RoleName
from tbl_c c,tbl_a a,tbl_b b
where c.userid=a.userid and c.RoleID=b.RoleID
order by a.usernamedrop table tbl_a,tbl_b,tbl_c
谢谢 LiangCK 加分了 假如再加个Application呢? 就截取Role_后面的 应该怎么样?
username rolename ApplicationName
---------- ---------- --------------
A Role_A A
Role_C C
B Role_B B
Role_D D
INSERT INTO tbl_A VALUES ('1','A')
INSERT INTO tbl_A VALUES ('2','B')
gocreate table tbl_b(RoleID varchar(2), RoleName varchar(12))
INSERT INTO tbl_b VALUES ('1','Role_A')
INSERT INTO tbl_b VALUES ('2','Role_B')
INSERT INTO tbl_b VALUES ('3','Role_C')
INSERT INTO tbl_b VALUES ('4','Role_D')
gocreate table tbl_C(UserId varchar(2), RoleID varchar(2))
INSERT INTO tbl_c VALUES ('1','1')
INSERT INTO tbl_c VALUES ('1','3')
INSERT INTO tbl_c VALUES ('2','2')
INSERT INTO tbl_c VALUES ('2','4')
goselect
username=(case when c.RoleID=(select min(RoleID) from tbl_C where UserId=c.userid) then a.username else '' end) ,b.RoleName
from tbl_c c,tbl_a a,tbl_b b
where c.userid=a.userid and c.RoleID=b.RoleID
godrop table tbl_a,tbl_b,tbl_c
insert tbl_A select 1 , 'A'
insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10))
insert tbl_B select 1 , 'Role_A'
insert tbl_B select 2 , 'Role_B'
insert tbl_B select 3 , 'Role_C'
insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int)
insert tbl_C select 1 , 1
insert tbl_C select 1 , 3
insert tbl_C select 2 , 2
insert tbl_C select 2 , 4select a.username,b.rolename into #
from tbl_A a inner join tbl_C c
on a.userid=c.userid
inner join tbl_B b
on b.roleid=c.roleid
order by a.username
select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username,
rolename,right(rolename,1) applicationname
from # a
order by case when isnull(username,'')='' then 0 else 1 end
drop table tbl_A,tbl_B,tbl_C,#/*
username rolename applicationname
---------- ---------- ---------------
A Role_A A
Role_C C
B Role_B B
Role_D D(4 行受影响)
*/
insert tbl_A select 1 , 'A'
insert tbl_A select 2 , 'B'create table tbl_B(roleid int,rolename varchar(10))
insert tbl_B select 1 , 'Role_A'
insert tbl_B select 2 , 'Role_B'
insert tbl_B select 3 , 'Role_C'
insert tbl_B select 4 , 'Role_D'create table tbl_C(userid int,roleid int)
insert tbl_C select 1 , 1
insert tbl_C select 1 , 3
insert tbl_C select 2 , 2
insert tbl_C select 2 , 4select a.username,b.rolename into #
from tbl_A a inner join tbl_C c
on a.userid=c.userid
inner join tbl_B b
on b.roleid=c.roleid
order by a.username
select case when rolename=(select min(rolename) from # where username=a.username) then username else '' end as username,
rolename,stuff(rolename,1,charindex('Role_',rolename)+4,'') applicationname
from # a
order by case when isnull(username,'')='' then 0 else 1 end
drop table tbl_A,tbl_B,tbl_C,#
/*
username rolename applicationname
---------- ---------- ----------------------------
A Role_A A
Role_C C
B Role_B B
Role_D D(4 行受影响)
*/