SQL语句综合积累

●基本语句
1.delete from table
2.update table set colname = '001' where colname='1'
3.insert into M_Item_Type(ID,Code,CreateDate) values('{E90RT730-6YD9}','3','2007-02-28 12:12:12.200')

●新建表
create  table T_Defect(
    ID                      varchar(40)      NOT NULL,
    DefectID              varchar(40)      NOT NULL,
    Quantity              numeric(3, 0)  default 0,
    CreateDate           datetime          NULL,
    CONSTRAINT PK9 PRIMARY KEY NONCLUSTERED (ID),
    CONSTRAINT RefM_Defect22 FOREIGN KEY (DefectID)
    REFERENCES M_Defect(ID)
)
go

●新增字段
alter  table M_Item_Stand add WPT_3 numeric(2, 0) default 0
go

●修改字段名
exec  sp_rename  'M_Item_Spec.[Met_Lot]', 'Met_Lot_In', 'COLUMN'
go

●内连接（返回两表公共部分）
select * from T_Final_Check join M_User
on T_Final_Check.WorkID = M_User.ID，或

select * from T_Final_Check inner join M_User
on T_Final_Check.WorkID = M_User.ID

●左外连接（以左表为主，返回左表中所有行）
select * from T_Final_Check left join M_User
on T_Final_Check.WorkID = M_User.ID，或

select * from T_Final_Check left outer join M_User
on T_Final_Check.WorkID = M_User.ID

●交叉连接（返回两表所有行的全部组合）
select * from T_Final_Check  cross join  M_User，或
select * from T_Final_Check , M_User

●自连接（把表中某些行与该表中另一些行连接起来，左右比较）
select In_Stand.AL as AL_IN, Out_Stand.AL as AL_OUT  from
(select * from M_Spec_Stand where StandType=1) as In_Stand,
(select * from M_Spec_Stand where StandType=2) as Out_Stand

●联合查询（合并有相同字段的两个表，上下比较）
select AL from M_Spec_Stand where StandType=1 union
select AL from M_Spec_Stand where StandType=2

●子查询·存在
select TMP2.駐車場CD,M30駐車場.駐車場名称
from M30駐車場 join
( Select 駐車場CD  From T10駐車位置 GROUP BY 駐車場CD) AS TMP2
on M30駐車場.駐車場CD = TMP2.駐車場CD
where len(TMP2.駐車場CD) <> 0 and  TMP2.駐車場CD in
          ( select distinct T10駐車位置.駐車場CD FROM T10駐車位置, M60車両,M04所属
            where  M60車両.車両管理番号 = T10駐車位置.車両管理番号
            and M60車両.課CD = M04所属.課CD
            and M04所属.所属区分 ='2' )

●子查询·不存在
select * from W_WarehouseDetail
where LotNo not in
(select  LotNo from W_StockInDetailInfo
where  IsExecute = 1)

●条件判断及排序
select ID,LotNo,
case when jude='A'  then 'A等品'
when jude='B'  then 'B等品'
when jude='C'  then 'C等品'
when jude='D'  then '报废'
else '样品'  end as jude
from T_CheckResultDetail

select
case when RG_MIN=0 then '≤'+ ltrim(str(RG_MAX,10,1)) else ltrim(str(RG_MIN,10,1)) + '-' + ltrim(str(RG_MAX,10,1)) end as RG_Stand
From M_Item_Stand
order by right(Lotno,2),left(Lotno,6),substring(Lotno,7,8)

●分组查询及统计
---两台机器不良项目分布统计
select M_Defect.Name,isnull(tt1.M1,'0') as M1,isnull(tt2.M2,'0') as M2
from M_Defect left outer join 
(select sum(Quantity) as M1,DefectID from T_Defect where right(ProductLot,2)='01' and CHECKDATE between '2007-05-01' and '2007-05-05' group by DefectID) as tt1
on M_Defect.ID = tt1.DefectID left outer join 
(select sum(Quantity) as M2,DefectID from T_Defect where right(ProductLot,2)='02' and CHECKDATE between '2007-05-01' and '2007-05-05' group by DefectID) as tt2
on M_Defect.ID = tt2.DefectID

---不良项目发生频次统计
select M_Defect.name, M_Item.SIZE_INCH,count(T_Defect.ProductLot)as badNum from T_Defect
left outer join M_Defect
on T_Defect.DefectID = M_Defect.ID
left outer join T_PROCESS_CHECK
on T_Defect.ProductLot = T_PROCESS_CHECK.LOTNO and T_PROCESS_CHECK.ISNEW = 'Y'
left outer join M_Item
on T_PROCESS_CHECK.ItemId = M_Item.ID
group by M_Item.SIZE_INCH,M_Defect.name

●多层嵌套的语句
SELECT * FROM
(SELECT 業者名,車検,点検,自家整備,業者CD,外注依頼日 FROM
(select T50外注.業者名,T50外注.業者CD,ISNULL(T1.車検,'0') AS 車検,ISNULL(T2.点検,'0') AS 点検,ISNULL(T3.自家整備,'0') AS 自家整備,外注依頼日,削除FLG
from T50外注
left outer join
(select count(*) AS 車検,業者名 from T50外注 where 整備区分CD='2' group by 業者名) as T1
on T50外注.業者名 = T1.業者名
left outer join
(select count(*) AS 点検,業者名 from T50外注 where 整備区分CD='1' group by 業者名) as T2
on T50外注.業者名 = T2.業者名
left outer join
(select count(*) AS 自家整備,業者名 from T50外注 where 整備区分CD='3' group by 業者名) as T3
on T50外注.業者名 = T3.業者名) AS T
WHERE T.削除FLG ='0' and T.外注依頼日 <= '20060101'
group by 業者名,業者CD,車検,点検,自家整備,外注依頼日,削除FLG) AS M
ORDER BY M.業者CD

 

●多层集计的语句（三张视图组合统计）
select one.SIZE_INCH,one.spec,one.CheckNum,
(one.ANum-isnull(two.CNum,0)-isnull(two.DNum,0)-isnull(three.CNum,0)-isnull(three.DNum,0)) as ANum,
(one.CNum+isnull(two.CNum,0)+isnull(three.CNum,0)) as CNum,
(one.DNum+isnull(two.DNum,0)+isnull(three.DNum,0)) as DNum
from
(select M_Item.SIZE_INCH, M_Item.Spec, count(T_PROCESS_CHECK.LOTNO) as CheckNum, count(A.jude) as ANum, count(C.jude) as CNum, count(D.jude) as DNum
from T_PROCESS_CHECK
left outer join M_Item on T_PROCESS_CHECK.ItemId = M_Item.ID
left outer join (select jude,id from T_PROCESS_CHECK where jude='A' or jude='B' or jude='E' or jude='F') as A
on T_PROCESS_CHECK.id = A.id
left outer join (select jude,id from T_PROCESS_CHECK where jude='C') as C
on T_PROCESS_CHECK.id = C.id
left outer join (select jude,id from T_PROCESS_CHECK where jude='D') as D
on T_PROCESS_CHECK.id = D.id
where T_PROCESS_CHECK.ISNEW = 'Y' and checkDate between '2007-5-10' and '2007-6-24'
group by M_Item.SIZE_INCH, M_Item.Spec ) as one
left outer join
(select M_Item.SIZE_INCH, M_Item.Spec, count(T_FINAL_CHECK.LOT) as CheckNum, count(C.jude) as CNum, count(D.jude) as DNum
From T_FINAL_CHECK
left outer join M_Item on T_FINAL_CHECK.ItemId = M_Item.ID
left outer join (select jude,id from T_FINAL_CHECK where jude='C') as C on T_FINAL_CHECK.id = C.id
left outer join (select jude,id from T_FINAL_CHECK where jude='D') as D on T_FINAL_CHECK.id = D.id
where course=1 and checkDate between '2007-5-10' and '2007-6-24'
or course=2 and checkDate between '2007-5-10' and '2007-6-24'
or course=3 and checkDate between '2007-5-10' and '2007-6-24'
or course=5 and checkDate between '2007-5-10' and '2007-6-24'
group by M_Item.SIZE_INCH, M_Item.Spec ) as two
on one.Spec = two.spec and one.SIZE_INCH = two.SIZE_INCH
left outer join
(select M_Item.SIZE_INCH, M_Item.Spec, count(T_FINAL_CHECK.LOT) as CheckNum, count(C.jude) as CNum, count(D.jude) as DNum
From T_FINAL_CHECK left outer join M_Item on T_FINAL_CHECK.ItemId = M_Item.ID
left outer join (select jude,id from T_FINAL_CHECK where jude='C') as C on T_FINAL_CHECK.id = C.id
left outer join (select jude,id from T_FINAL_CHECK where jude='D') as D on T_FINAL_CHECK.id = D.id
where course=4 and checkDate between '2007-5-10' and '2007-6-24'
group by M_Item.SIZE_INCH, M_Item.Spec ) as three
on one.Spec = three.spec and one.SIZE_INCH = three.SIZE_INCH
order by one.SIZE_INCH