--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