作者:岁月如风晓 | 来源:互联网 | 2023-06-07 12:09
begin
MERGE INTO [dbo].[PropertyPriceTrend] us1
USING
(
SELECT '${item.propertyEffectiveId}' PropertyEffectiveId,
'${item.createTimeStr}' createTime,
'${item.priceType}' priceType,
'${item.beforePrice}' beforePrice,
'${item.updatePrice}' updatePrice,
'${item.DeleteFlag}' DeleteFlag,
'${item.CreateUser}' CreateUser,
'${item.createDateStr}' CreateDate,
'${item.UpdateUser}' UpdateUser,
'${item.updateDateStr}' UpdateDate
) us2
ON (
us1.PropertyEffectiveId=us2.PropertyEffectiveId
AND DATEDIFF ([second], us1.createTime, us2.createTime) = 0
AND us1.priceType = us2.priceType
AND us1.DeleteFlag = us2.DeleteFlag
)
WHEN MATCHED THEN
UPDATE SET us1.beforePrice = us2.beforePrice, us1.updatePrice = us2.updatePrice
WHEN NOT MATCHED THEN
INSERT (PropertyEffectiveId, createTime, priceType, beforePrice, updatePrice, DeleteFlag, CreateUser, CreateDate, UpdateUser, UpdateDate)
VALUES(us2.PropertyEffectiveId, CONVERT(datetime,us2.createTime), us2.priceType, us2.beforePrice, us2.updatePrice, us2.DeleteFlag, us2.CreateUser, CONVERT(datetime,us2.CreateDate), us2.UpdateUser, CONVERT(datetime,us2.UpdateDate))
;end;
由于数据库表[dbo].[PropertyPriceTrend]中createTime是datetime类型
传参:集合对象包含了时间,将时间转换yyyy-MM-dd HH:mm:ss时间字符串进行中间处理
sqlserver与mysql还是有很大区别的!