Sql技巧1 -Sqlserver 2008

1. 查询为每个供应商的整数ID生成一个10位数字的字符串表示（不足事，前面补0）

SELECT supplierid,RIGHT(REPLICATE('0',9) + CAST(supplierod AS varchar(10)),10) as strsupploerid

FROM Production.Suppliers;

2. 取当前日期的日期部分跟时间部分

select convert(char(8),CURRENT_TIMESTAMP,112);

select convert(char(12),CURRENT_TIMESTAMP,114);

3. 取当年的第一天跟当月的最后一天

select dateadd(month,datediff(month,'20010101',CURRENT_TIMESTAMP),'20010101')

select dateadd(month,DATEDIFF(month,'19991231',CURRENT_TIMESTAMP),'19991231')

4. 巧用交叉链接CROSS JOIN生成1到1000的整数序列

use tempdb;

if object_id('dbo.Digits','U') IS NOT NULL DROP TABLE dbo.Digits;

CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);

INSERT INTO dbo.Digits(digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

select d3.digit * 100 + d2.digit * 10 + d1.digit + 1 as n from dbo.Dights as d1

cross join dbo.dights as d2

cross join dbo.dights as d3

order by n;

转载于:https://www.cnblogs.com/00000/archive/2011/07/07/2100239.html