表1为用户的基本信息表
ID
name
age
sex表2为用户的发言标题
UserID
Topic现在想用一条Sql语句,生成如下结构的数据ID name age sex Topic1 Topic2 Topic3
1 张三 18 男 食堂问题 宿舍问题 教室问题
2..........
3............
ID
name
age
sex表2为用户的发言标题
UserID
Topic现在想用一条Sql语句,生成如下结构的数据ID name age sex Topic1 Topic2 Topic3
1 张三 18 男 食堂问题 宿舍问题 教室问题
2..........
3............
a.*,
Topic1=(select top 1 b.Topic from 表2 b where b.UserID=a.ID),
Topic2=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 1 Topic from 表2 where UserID=b.UserID)),
Topic3=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 2 Topic from 表2 where UserID=b.UserID))
from
表1 a
试试
a.*,
Topic1=(select top 1 b.Topic from 表2 b where b.UserID=a.ID),
Topic2=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 1 Topic from 表2 where UserID=b.UserID)),
Topic3=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 2 Topic from 表2 where UserID=b.UserID))
from
表1 a
严重同意
select a.*, max(case fIndex when 1 then Topic end) as Topic1, max(case fIndex when 2 then Topic end) as Topic2, max(case fIndex when 3 then Topic end) as Topic3
from tl1 a left join (
select UserId, Topic, (select count(*) from tl2 where UserId = c.UserId and Topic < c.Topic) + 1 as fIndex
from tl2 c) b on a.Id = b.UserId
group by a.ID, a.name, a.age, a.sex
if object_id('tl1') is not null drop table tl1
if object_id('tl2') is not null drop table tl2
select 1 as id, '张三' as name, 18 as age, '男' as sex
into tl1
union select 2, '李四', 19, '女'select 1 as UserId, '食堂问题' as Topic
into tl2
union select 1, '宿舍问题'
union select 1, '教室问题'
union select 1, '四个问题'
union select 2, '第一问题'
union select 2, '第二问题'
select * from tl1
/*
id name age sex
1 张三 18 男
2 李四 19 女
*/
select * from tl2
/*
UserId Topic
1 教室问题
1 食堂问题
1 四个问题
1 宿舍问题
2 第二问题
2 第一问题
*/
go
select a.*, max(case fIndex when 1 then Topic end) as Topic1, max(case fIndex when 2 then Topic end) as Topic2, max(case fIndex when 3 then Topic end) as Topic3
from tl1 a left join (
select UserId, Topic, (select count(*) from tl2 where UserId = c.UserId and Topic < c.Topic) + 1 as fIndex
from tl2 c) b on a.Id = b.UserId
group by a.ID, a.name, a.age, a.sex
/*
id name age sex Topic1 Topic2 Topic3
1 张三 18 男 教室问题 食堂问题 四个问题
2 李四 19 女 第二问题 第一问题 NULL
*/drop table tl1
drop table tl2
select
a.*,
Topic1=(select top 1 b.Topic from 表2 b where b.UserID=a.ID),
Topic2=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 1 Topic from 表2 where UserID=b.UserID)),
Topic3=(select top 1 b.Topic from 表2 b where b.UserID=a.ID and b.Topic not in(select top 2 Topic from 表2 where UserID=b.UserID))
from
表1 a