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
