程序人生 —— SQL Server函数小记 -

标题: 程序人生 —— SQL Server函数小记 [打印本页]

作者: hongniu 时间: 2015-6-25 15:16
标题: 程序人生 —— SQL Server函数小记
1、多行拼接成一列(FOR XML PATH)

DECLARE @t_Temp TABLE
(
id VARCHAR(100)
)
INSERT INTO @t_Temp VALUES ('ID1')
INSERT INTO @t_Temp VALUES ('ID2')
INSERT INTO @t_Temp VALUES ('ID3')
INSERT INTO @t_Temp VALUES ('ID4')
INSERT INTO @t_Temp VALUES ('ID5')

SELECT id + ','
FROM @t_Temp
FOR XML PATH('')

2、截取并插入新字符串(STUFF)

SELECT STUFF('入库货位1,出库货位1,入库货位2,出库货位2,', 1, 2, '123')

3、数据分页(ROW_NUMBER())

DECLARE @t_Temp TABLE
(
id VARCHAR(100)
)
INSERT INTO @t_Temp VALUES ('ID1')
INSERT INTO @t_Temp VALUES ('ID2')
INSERT INTO @t_Temp VALUES ('ID3')
INSERT INTO @t_Temp VALUES ('ID4')
INSERT INTO @t_Temp VALUES ('ID5')

SElECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) AS ROW,*
FROM @t_Temp) T WHERE T.ROW BETWEEN 1 AND 2

待续...

欢迎光临 (http://www.51hei.com/bbs/)