记忆一些SQL语句

旧城等待， 2021-11-02 10:08 422阅读 0赞

[记忆一些SQL语句][SQL]

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

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

posted on 2010-09-10 14:54 [Jacky Xu][] 阅读( ...) 评论( ...) [编辑][Link 1] 收藏

转载于:https://www.cnblogs.com/JackyXu1981/archive/2010/09/10/1823292.html

[SQL]: https://www.cnblogs.com/JackyXu1981/archive/2010/09/10/1823292.html
[EndTime]: mailto:=@EndTime
[Jacky Xu]: https://www.cnblogs.com/JackyXu1981/
[Link 1]: https://i.cnblogs.com/EditPosts.aspx?postid=1823292