如何根据username把3张表的内容读到一个表中呢?
要求新表的username不重复。sql2005 A表
id username group scope
201 李明 2 0402
202 李明 2 0405
203 李明 2 0406
204 admin 2 1002B表:
id username group
1 李明 2
2 admin 0
C表:
id username year
1 李明 2010.02.25
2 admin 2005.02.15
要求新表的username不重复。sql2005 A表
id username group scope
201 李明 2 0402
202 李明 2 0405
203 李明 2 0406
204 admin 2 1002B表:
id username group
1 李明 2
2 admin 0
C表:
id username year
1 李明 2010.02.25
2 admin 2005.02.15
204 admin 0 1002
a.username=b.username
and b.username=c.username
and not exists
(
select 1 from a as t where a.username=t.username and t.id>a.id
)
其他表会重复吗?
username group scope year
李明 2 0402,0405,0406 2010.02.05
adnin 0 1002 2010.02.15
(
id int,
username varchar(20),
[group] int,
scope varchar(10)
)
insert into #A select 201,'李明',2,'0402'
union all select 202,'李明',2,'0405'
union all select 203,'李明',2,'0406'
union all select 204,'admin',0,'1002'create table #B
(
id int,
username varchar(20),
[group] int
)
insert into #B select 1,'李明',2
union all select 2,'admin',0create table #C
(
id int,
username varchar(20),
[year] datetime
)
insert into #C select 1,'李明','2010.02.25'
union all select 1,'admin','2005.02.15'select A.*,C.[year]
from
(
select * from #A A where not exists(select * from #A where [group]=A.[group] and id<A.id)
) A
join #C C
on A.username=C.username
id username group scope year
----------- -------------------- ----------- ---------- -----------------------
201 李明 2 0402 2010-02-25 00:00:00.000
204 admin 0 1002 2005-02-15 00:00:00.000(2 行受影响)
但是,B表和C表一定有username这一项的。
(
id int,
username varchar(20),
[group] int,
scope varchar(10)
)
insert into A select 201,'李明',2,'0402'
union all select 202,'李明',2,'0405'
union all select 203,'李明',2,'0406'
union all select 204,'admin',0,'1002'create table B
(
id int,
username varchar(20),
[group] int
)
insert into B select 1,'李明',2
union all select 2,'admin',0create table C
(
id int,
username varchar(20),
[year] datetime
)
insert into C select 1,'李明','2010.02.25'
union all select 1,'admin','2005.02.15'create function dbo.getscope(@username varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+scope from A where username=@username
return stuff(@sql,1,1,'')
end
select A.username,A.[group],A.scope,C.[year]
from
(
select distinct username,[group],dbo.getscope(username) scope from A
) A
join #C C
on A.username=C.usernameusername group scope year
-------------------- ----------- -------------------------------------------------- -----------------------
admin 0 1002 2005-02-15 00:00:00.000
李明 2 0402,0405,0406 2010-02-25 00:00:00.000(2 行受影响)