【sql】sql语句小积累

 

1.截取字段某段值作为新值显示：
select SUBSTRING(VehicleCode, 9, 3)  as VehicleCode , SUBSTRING(StoveCode, 9, 3)  as StoveCode from ZhuoyiMesDB.dbo.t_POM_Order_VehicleAndStove_T where 1=1 order by  StoveCode ASC

2.去重查询
select distinct lane from ZhuoyiMesDB.dbo. uv_SITMesDb_POM_VehicleAndStove  where 1=1 and lane != '';

3.表中更新数据
UPDATE [dbo].[uv_SITMesDb_POM_VehicleAndStove] SET [Lane] = N'4通路', [StoveNumber] = N'ZY08-LS-401', [VehicleCode] = NULL, [StoveType] = NULL, [UpdatedOn] = NULL, [UpdatedBy] = NULL, [Flag] = NULL WHERE [Lane] = N'4通路' AND [StoveNumber] = N'ZY08-LS-401' AND [VehicleCode] IS NULL AND [StoveType] IS NULL AND [UpdatedOn] IS NULL AND [UpdatedBy] IS NULL AND [Flag] IS NULL;


4.防止中文乱码
SELECT StoveNumber FROM [dbo].[uv_SITMesDb_POM_VehicleAndStove] where lane=N'4通路'

5.更新数据
UPDATE [dbo].[t_POM_Order_VehicleAndStove_T] SET VehicleCode = N'ZY08-LS-1A9',UpdatedOn=GETDATE(), UpdatedBy = N'1001' WHERE  Stovecode= N'ZY08-LS-102' 

6.新增数据
insert into ZhuoyiMesDB.dbo.t_TANK_Large_Small_StoveNumber_T (Dxh,Xxh,StoveNumber, CreatedBy, CreatedOn) values ('2#DXH','2#XXH','ZY08-LS-104','1001',getdate())