有用的sql语句
?
--sql 1 将一张表的字段通过另一张表之间的对应关系进行更新。
update WarrantySaleItem set applicableName = a.applicableName
from Applicable a where a.ID = WarrantySaleItem.applicableName
?
?
--sql 2 通过一张表更新另一张表的数据。属于实用性的sql。
INSERT INTO dbo.Vendor(VendorCode,VendorNameCh,VendorNameEn,TaxRate)
SELECT VendorCode,VendorNameCh,VendorNameEn,TaxRate
FROM dbo.Vendor_Temp a
WHERE a.VendorCode not in
(SELECT VendorCode FROM dbo.Vendor)
UPDATE dbo.Vendor
SET VendorNameCh=a.VendorNameCh,
??????? VendorNameEn=a.VendorNameEn,
??????? TaxRate=a.TaxRate
FROM dbo.Vendor_Temp a
WHERE dbo.Vendor.VendorCode=a.VendorCode AND (
????????????? dbo.Vendor.VendorNameCh<>a.VendorNameCh OR
????????????? dbo.Vendor.VendorNameEn<>a.VendorNameEn OR
????????????? dbo.Vendor.TaxRate<>a.TaxRate)
?
--sql 3row_number 函数的使用Oracle中有rownumber，sqlservicer有这个函数，功能都差不多，使用方法如下：
select a.*,row_number() over(order by id)? from Customer as a
