select 年级,班别,组别,a.姓名 组长,b.姓名 组员 from (select * from 用户表 where 是否组长='是') a join (select * from 用户表 where 是否组长='否') b on a.组别=b.组别
--改下 select a.年级,a.班别,a.组别,a.姓名 组长,b.姓名 组员 from (select * from 用户表 where 是否组长='是') a join (select * from 用户表 where 是否组长='否') b on a.班别=b.班别 and a.组别=b.组别
用顿号隔开: select a.年级,a.班别,a.组别,a.姓名+'、'+b.姓名 as [组长、组员] from (select * from 用户表 where 是否组长='是') a join (select * from 用户表 where 是否组长='否') b on a.班别=b.班别 and a.组别=b.组别
--给你一个函数 CREATE FUNCTION dbo.f_str(@nianji varchar(10),@bianbie varchar(10) ,@zubie varchar(10)) RETURNS varchar(1000) AS BEGIN DECLARE @r varchar(1000) SET @r = '' SELECT @r = @r + ',' + 姓名 FROM tb WHERE 年级=@nianji and 班别=@bianbie and 组别=@zubie and 是否组长<>1 RETURN STUFF(@r, 1, 1, '') END GO select a.年级,a.班别,a.组别,(select 姓名 from tb where 年级=a.年级 and 班别=a.班别 and 组别 =a.组别 and a.是否组长=1) 组长,dbo.f_str(a.年级,a.班别,a.组别) 组员 from tb a group by a.年级,a.班别,a.组别 order by a.年级,a.班别,a.组别
select a.年级,a.班别,a.组别,a.姓名 组长 ,组员= (select stuff((select ','+组长 from 用户表 where 组别 = a.组别 and 是否组长 = '否'),1,1,'')) from 用户表 a where 是否组长 = '是'
--给你一个函数 CREATE FUNCTION dbo.f_str(@nianji varchar(10),@bianbie varchar(10) ,@zubie varchar(10)) RETURNS varchar(1000) AS BEGIN DECLARE @r varchar(1000) SET @r = '' SELECT @r = @r + '、' + 姓名 FROM tb WHERE 年级=@nianji and 班别=@bianbie and 组别=@zubie and 是否组长<>1 RETURN STUFF(@r, 1, 1, '') END GO select a.年级,a.班别,a.组别,(select 姓名 from tb where 年级=a.年级 and 班别=a.班别 and 组别 =a.组别 and a.是否组长=1) 组长,dbo.f_str(a.年级,a.班别,a.组别) 组员 from tb a group by a.年级,a.班别,a.组别 order by a.年级,a.班别,a.组别
select a.年级,a.班别,a.组别,a.姓名+'、'+b.姓名 as [组长、组员] from (select * from 用户表 where 是否组长='是') a join (select * from 用户表 where 是否组长='否') b on a.年级=b.年级 and a.班别=b.班别 and a.组别=b.组别
select a.年级,a.班别a.组别,b.姓名 as 组长,a.姓名 as 组员 from 表 a,表 b where a.组别=b.组别 and a.年级=b.年级 and a.班别=b.班别 and a. 是否组长='否' and b.是否组长='是'
from (select * from 用户表 where 是否组长='是') a
join (select * from 用户表 where 是否组长='否') b
on a.组别=b.组别
select a.年级,a.班别,a.组别,a.姓名 组长,b.姓名 组员
from (select * from 用户表 where 是否组长='是') a
join (select * from 用户表 where 是否组长='否') b
on a.班别=b.班别 and a.组别=b.组别
select a.年级,a.班别,a.组别,a.姓名+'、'+b.姓名 as [组长、组员]
from (select * from 用户表 where 是否组长='是') a
join (select * from 用户表 where 是否组长='否') b
on a.班别=b.班别 and a.组别=b.组别
CREATE FUNCTION dbo.f_str(@nianji varchar(10),@bianbie varchar(10) ,@zubie varchar(10))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @r varchar(1000)
SET @r = ''
SELECT @r = @r + ',' + 姓名 FROM tb WHERE 年级=@nianji and 班别=@bianbie and 组别=@zubie and 是否组长<>1
RETURN STUFF(@r, 1, 1, '')
END
GO select a.年级,a.班别,a.组别,(select 姓名 from tb where 年级=a.年级 and 班别=a.班别 and 组别 =a.组别 and a.是否组长=1) 组长,dbo.f_str(a.年级,a.班别,a.组别) 组员 from tb a group by a.年级,a.班别,a.组别
order by a.年级,a.班别,a.组别
select a.年级,a.班别,a.组别,a.姓名 组长
,组员= (select stuff((select ','+组长 from 用户表 where 组别 = a.组别 and 是否组长 = '否'),1,1,''))
from
用户表 a
where 是否组长 = '是'
CREATE FUNCTION dbo.f_str(@nianji varchar(10),@bianbie varchar(10) ,@zubie varchar(10))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @r varchar(1000)
SET @r = ''
SELECT @r = @r + '、' + 姓名 FROM tb WHERE 年级=@nianji and 班别=@bianbie and 组别=@zubie and 是否组长<>1
RETURN STUFF(@r, 1, 1, '')
END
GO select a.年级,a.班别,a.组别,(select 姓名 from tb where 年级=a.年级 and 班别=a.班别 and 组别 =a.组别 and a.是否组长=1) 组长,dbo.f_str(a.年级,a.班别,a.组别) 组员 from tb a group by a.年级,a.班别,a.组别
order by a.年级,a.班别,a.组别
from (select * from 用户表 where 是否组长='是') a
join (select * from 用户表 where 是否组长='否') b
on a.年级=b.年级 and a.班别=b.班别 and a.组别=b.组别
from 表 a,表 b
where a.组别=b.组别
and a.年级=b.年级
and a.班别=b.班别
and a. 是否组长='否'
and b.是否组长='是'