SQL笔记及技巧

SELECT *

FROM DeliveryMaster where 

not exists(select * from DeliveryDetail  

where DeliveryDetail.DeliveryMKey=DeliveryMaster.DeliveryMKey )

--exists 记录在另一表中存在

--not exists 记录在另一表中不存在

SELECT *

FROM DeliveryMaster where 

DeliveryMKey not in(select DeliveryMKey from DeliveryDetail  

where DeliveryDetail.DeliveryMKey=DeliveryMaster.DeliveryMKey )

SELECT *

FROM DeliveryMaster where 

DeliveryMKey  in(select DeliveryMKey from DeliveryDetail  

where DeliveryDetail.DeliveryMKey=DeliveryMaster.DeliveryMKey )

--in 指定字段值在另一表中存在

--not in 指定字段值在另一表中不存在

declare @a int;set @a=100;

select @a

--定义变量

SELECT  distinct   DeliveryMaster.*

FROM         DeliveryMaster left  JOIN

                      DeliveryDetail ON DeliveryDetail.DeliveryMKey = DeliveryMaster.DeliveryMKey

--distinct 屏蔽重记录,right join left join 有关系

 if object_id('tempdb..#Tempds') is not null

Begin  

drop table #Tempds ;

End  

--建临时表

select * into #Tempds  from (

SELECT 

[EcnDKey],[EcnMKey],[MaterialBomKey],[ItemID],[ProductType],[Denominator],[WastageCalType],[Conversion],[Priority],

[Reference],[Usage],[Wastage],[UsageWithWastage],[SeqNo],[Description],[Remarks],[ECNState],[Modify_By],[ModifyDate],

[NewDenominator],[NewWastageCalType],[NewUsage],[NewWastage],[UsageUnit]

FROM [EcnDetail] where ([EcnMKey]=@EcnMKey )and ([ECNState]=1 or  [ECNState]=2) )AS TA;

--插入记录

INSERT INTO #Tempds(

[EcnMKey],[MaterialBomKey],[ItemID],[ProductType],[Denominator],[WastageCalType],[Conversion],[Priority],

[Reference],[Usage],[Wastage],[UsageWithWastage],[SeqNo],[Description],[Remarks],[ECNState],[Modify_By],[ModifyDate],

[NewDenominator],[NewWastageCalType],[NewUsage],[NewWastage],[UsageUnit]

)

SELECT 

@EcnMKey AS [EcnMKey],[ID]AS [MaterialBomKey],[ItemID],[ProductType],[Denominator],[WastageCalType],[Conversion],[Priority],

[Reference],[Usage],[Wastage],[UsageWithWastage],[SeqNo],[Description],[Comment] AS [Remarks],3 AS [ECNState],[UpdateBy]AS[Modify_By],[UpdateDate]AS[ModifyDate],

-1 as [NewDenominator],1 as [NewWastageCalType], -1 as [NewUsage],-1 as [NewWastage],[UsageUnit] 

FROM [MaterialBom] 

WHERE  [MasterItem]=@MasterItem " + Keys+ @";

--查询

SELECT * FROM #Tempds where 1=1 " + Likes + " ORDER BY ItemID DESC

--判断临时表是否存在,如果存在就执行删除临时操作,然后再建立临时表,然后把查询出来的数据添加到临时表内,组成一个表

declare @a varchar(500);set @a='''A01-V000PVC-1009'',''A01-00000000-''';

select @a

Declare @Command  Varchar(200)

set @Command ='SELECT ItemID, New_ItemID, New_Description, State, Update_By, Update_date

               FROM  Item_Code_table where New_ItemID not in('+@a+') ORDER BY ItemID'

select @Command

Exec(@Command)

--当条件NOT IN 及 IN 要使用到字符串时, 先应把sql 语句转成字符串类型,后再执行,如上 

Declare @TableTemp table( col1 int ,col2 Varchar(30),col3 Varchar(30),col4 Varchar(200))

insert @TableTemp (col1,col2,col3,col4) select State,ItemID, New_ItemID, New_Description from [v_NewItemCodeVsOld] where New_Description<>''

select * from @TableTemp where col1=4

-- 可以在数据库里定义一个表格变量,后把适合条件的记录存放到变量里,变量表格也可以与正常表同理使用,方便了数据的处理,语句结束变量自动清空,不像临时表

SELECT 序号=(SELECT COUNT(ItemID) FROM [v_NewItemCodeVsOld] AS LiMing

       WHERE LiMing.ItemID<v_NewItemCodeVsOld.ItemID),

       ItemID, New_ItemID,New_Description

FROM [v_NewItemCodeVsOld] where New_Description<>'' ORDER BY 1;

--增加一列自增序列号,(变量表不能做聚合涵数运行,如LEFT JOIN ,REGHT JOIN 等,而临时表就可以)