set nocount on
create table tb([ID] int,[NAME] varchar(10),SEX varchar(10))
insert into tb select 1,'ss','男'
insert into tb select 1,'xx','男'
insert into tb select 2,'dd','女'
go
create table tt([ID] int,BIRTH varchar(20))
insert into tt select 1,'2004-01-01'
insert into tt select 2,'2005-01-01'
go
create view aa
as
select a.[ID],b.[NAME],b.[SEX],a.[BIRTH] from tt a,tb b where a.[ID]=b.[ID]
go
可以将名字和性别都分别放到一字段里,查出来啊
create table tb([ID] int,[NAME] varchar(10),SEX varchar(10))
insert into tb select 1,'ss','男'
insert into tb select 1,'xx','男'
insert into tb select 2,'dd','女'
insert into tb select 1,'ds','男'
insert into tb select 1,'ee','男'
insert into tb select 2,'dd','女'
insert into tb select 2,'eu','男'
insert into tb select 3,'ew','男'
go
create table tt([ID] int,BIRTH varchar(20))
insert into tt select 1,'2004-01-01'
insert into tt select 2,'2005-01-01'
insert into tt select 3,'2004-01-01'
insert into tt select 4,'2005-01-01'
go
select * from tb group by id,name,sex
select * from tt
select id,dbo.aaa(id) as name,dbo.bbb(id) as sex,birth from tt group by id,birth
drop table tb
drop table tt
create function aaa(@id int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+name from tb where id=@id
set @sql=stuff(@sql,1,1,'')
return @sql
end
create function bbb(@id int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+sex from tb where id=@id
set @sql=stuff(@sql,1,1,'')
return @sql
end