SQLserver面试

--ROW_NUMBER()实现分页功能

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC

面试题：怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案一、
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

一、行转列

select姓名,

sum(case课程when‘语文‘then分数else0end)语文,

sum(case课程when‘数学‘then分数else0end)数学,

sum(case课程when‘物理‘then分数else0end)物理

fromtb

group by 姓名

姓名 语文 数学 物理

---------- ----------- ----------- -----------

李四 74 84 94

张三 74 83 93

二、列转行

select*from

(

select姓名,课程=‘语文‘,分数=语文fromtb

unionall

select姓名,课程=‘数学‘,分数=数学fromtb

unionall

select姓名,课程=‘物理‘,分数=物理fromtb

) t

orderby姓名,case课程when‘语文‘then1when‘数学‘then2when‘物理‘then3end

SQLserver面试

原文：http://www.cnblogs.com/Rikybk/p/3564164.html