几条sql语句

1、行、列转换

--行转列 
create table 成绩(姓名 varchar(10),科目 varchar(10),分数 int) 
insert into 成绩 values('张三','语文',74) 
insert into 成绩 values('张三','数学',83) 
insert into 成绩 values('张三','物理',93) 
insert into 成绩 values('李四','语文',77) 
insert into 成绩 values('李四','数学',84) 
insert into 成绩 values('李四','物理',94)
insert into 成绩 values('王五','语文',81) 
insert into 成绩 values('王五','数学',86) 
insert into 成绩 values('王五','物理',91)
--方法1
select
姓名,
max(case 科目 when '语文' then 分数 else 0 end) as 语文,
max(case 科目 when '数学' then 分数 else 0 end) as 数学,
max(case 科目 when '物理' then 分数 else 0 end) as 物理
from 成绩 group by 姓名
--方法2（sql20005）
select*from 成绩 pivot(max(分数) for 科目 in (语文,数学,物理)) a

--列转行 
create table 成绩(姓名 varchar(10),语文 int,数学 int,物理 int) 
insert into 成绩 values('张三',74,83,93)  
insert into 成绩 values('李四',77,84,94)  
insert into 成绩 values('王五',81,86,91)  
--方法1
select 姓名,'语文' as 科目,语文 as 分数 from 成绩
union
select 姓名,'数学' as 科目,数学 as 分数 from 成绩
union
select 姓名,'物理' as 科目,物理 as 分数 from 成绩
--方法2
select 姓名,科目,分数 from 成绩 unpivot(分数 for 科目 in(语文,数学,物理)) t

 

2、分组排序

--（用上行转列的表，各科按成绩排名,sql2005）
select row_number() over(partition by 科目 order by 分数 desc) as 排名,姓名,科目,分数 from 成绩

 

3、递归查询
树形结构查找某个节点的上下级（sql2005）

create table 部门(id int,名称 varchar(30),上级id int)
go
insert into 部门  values(1,'总部',0)
insert into 部门  values(2,'研发部',1)
insert into 部门  values(3,'软件部',2)
insert into 部门  values(4,'企业软件部',3)
insert into 部门  values(5,'CRM开发部',4)
 
go
with 下级部门 as
(
    select*from 部门 where id=2
    union all
    select t1.* from 部门 t1 inner join 下级部门 t2 on t1.上级id=t2.id
)
select*from 下级部门
go
with 上级部门 as
(
    select*from 部门 where id=4
    union all
    select t1.* from 部门 t1 inner join 上级部门 t2 on t1.id=t2.上级id
)
select*from 上级部门

 

4、多行拼接到一列
(在用户以及用户所属角色的列表)

create table 用户 (id int,名称 varchar(30))
go
create table 角色(id int,名称 varchar(30))
go
create table 用户_角色(用户id int,角色id int)
go
insert into 用户 values(1,'A')
insert into 用户 values(2,'B')
insert into 角色 values(1,'管理员')
insert into 角色 values(2,'主管')
insert into 用户_角色 values(1,1)
insert into 用户_角色 values(1,2)
insert into 用户_角色 values(2,2)
go

select *,
stuff((select ','+名称 from 角色 a left join 用户_角色 b on a.id=b.角色id where b.用户id=u.id for xml path('')),1,1,'') as 角色
from 用户 u