我基本上是在尝试转换这个sql语句,使其能够一天运行多次,而不会重复结果。我没有写代码,我尝试了一些IFNOT EXISTS语句,但在语法正确方面没有成功。请让我知道需要做些什么,以便仅在表中当前不存在结果的情况下插入结果。谢谢你的帮助。
INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)
SELECT PD.Id AS ProductId,
sao.Id AS SpecificationAttributeOptionId,
'1' AS AllowFiltering,
'0' AS ShowOnProductPage,
sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
select id,
name,
ymin as pcmin,
case when ymax < ymin then 99 else ymax end as pcmax,
case when ymax < ymin then 0 else ymin end as ccmin,
ymax as ccmax
from (
select id,
name,
convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
convert(int, SUBSTRING(name, loc+1, 2)) as ymax
from (
select id,
name,
loc
from (
select id,
name,
CHARINDEX('-', name) as loc
from PRODUCT
where CHARINDEX('-', name) > 0
) as tbl
where SUBSTRING(name, loc-3, 1) = ' '
and SUBSTRING(name, loc+3, 1) = ' '
) as rng
) as yrs
) as PdRng
on PdRng.id = PD.id
join (
select *
from SpecificationAttributeOption
where isnumeric(Name) > 0
and len(rtrim(Name)) = 4 AND SpecificationAttributeOption.SpecificationAttributeId = '7'
) as sao
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax
WITH T AS (
SELECT
PD.Id AS ProductId,
sao.Id AS SpecificationAttributeOptionId,
'1' AS AllowFiltering,
'0' AS ShowOnProductPage,
sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
select id,
name,
ymin as pcmin,
case when ymax < ymin then 99 else ymax end as pcmax,
case when ymax < ymin then 0 else ymin end as ccmin,
ymax as ccmax
from (
select id,
name,
convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
convert(int, SUBSTRING(name, loc+1, 2)) as ymax
from (
select id,
name,
loc
from (
select id,
name,
CHARINDEX('-', name) as loc
from PRODUCT
where CHARINDEX('-', name) > 0
) as tbl
where SUBSTRING(name, loc-3, 1) = ' '
and SUBSTRING(name, loc+3, 1) = ' '
) as rng
) as yrs
) as PdRng
on PdRng.id = PD.id
join (
select *
from SpecificationAttributeOption
where
isnumeric(Name) > 0
and len(rtrim(Name)) = 4
AND SpecificationAttributeOption.SpecificationAttributeId = '7'
) as sao
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax
)
INSERT INTO Product_SpecificationAttribute_Mapping(
ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder
)
SELECT
ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder
FROM T
WHERE
NOT EXISTS (
SELECT * FROM Product_SpecificationAttribute_Mapping M
WHERE
T.ProductId = M.ProductId
AND T.SpecificationAttributeOptionId = M.SpecificationAttributeOptionId
AND T.AllowFiltering = M.AllowFiltering
AND T.ShowOnProductPage = M.ShowOnProductPage
AND T.DisplayOrder = M.DisplayOrder
)