1, Select 50 unit unique records

select distinct  top 50 SalesId from salesline

2, Select total amount > 1500 records

select salesid,sum(amount) from salesline group by salesid having sum(amount)>1500

3, Define temporary table

create table #SalesCustom (UserName varchar(30), CustName varchar(30), EmpName varchar (30), Location varchar (30))

4, Insert data and join in

insert into #SalesCustom (UserName,CustName, EmpName, Location)

select CustName, salesid, EmpName, Location from salestable

inner join CustTable on salestable.custnumber= custtable.custnumber

inner join EmpTable on salestable.EmpId = EmpTable.EmpId

inner join Address on Address.Id = EmpTable.AddressId

5, sp_MSforeachtable: Check maximal RecId  in db on all tables (most tables has a RecId field)

create table #TT (tablename varchar(30), maxrecid int, minrecid int)
exec sp_MSforeachtable
      'insert into #TT select ''?'', max(recid),min(recid) from ? with(nolock)
select * from #TT order by maxecId desc
drop table #TT

6, update and datediff function

update #Issuehistory set #Issuehistory.Resolution = datediff(day,CreatedDate,CompletedDate),#Issuehistory.afield ='XXX' from #Issuehistory

7, exclusion weekday function

USE [DB_Name]
GO
/****** Object:  UserDefinedFunction [dbo].[DatePartWeekend]    Script Date: 09/10/2010 15:21:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  function   [dbo].[DatePartWeekend](@BegTime   datetime,   @EndTime   datetime)
returns   int
as
begin
declare   @re   int
set   @re=0
while   @BegTime <=@EndTime
begin
select   @re=@re+1   where   (datepart(weekday,   @BegTime)+@@datefirst-1)%7   between   1   and   5
set   @BegTime=dateadd(day,   1,   @BegTime)
end

return   @re
end

8, 行列转换

Create table TT(part varchar(20), xu int )

插入

part    　 xu

a　　　　null

a　　　　null

a　　　　null

a　　　　null

b　　　　null

b　　　　null

b　　　　null

c　　　　null

执行：

select part,
xu=row_number() over(partition by part order by getdate())
from TT

结果：

a          1
a          2
a          3
a          4
b          1
b          2
b          3
c          1