34,576
社区成员
发帖
与我相关
我的任务
分享
select * into #t1 from (
select 'P1'PromotionCode,'R1'ElementRelationCode,0IsConform,'When (T1=1 or T2=1)then R1'formula,'买数量送数量'DESC0 union all
select'P1','T1',1,'When(T1=1 or T2=1)then R1', '销售数量' union all
select'P1','T2',1,'When(T1=1 or T2=1)then R1','采购金额' union all
select'P2','R1',1,'When(T1=0 or T2=1)then R1','买数量送数量' union all
select'P2','T1',0,'When(T1=0 or T2=1)then R1', '销售数量' union all
select'P2','T2',1,'When(T1=0 or T2=1)then R1','采购金额' union all
select'P3','R1',1,'When(T1=1 or T2=1)then R1','买数量送数量' union all
select'P3','T1',0,'When(T1=1 or T2=1)then R1', '销售数量' union all
select'P3','T2',0,'When(T1=1 or T2=1)then R1','采购金额'
) a
select * into #t2 from(
select * from (select PromotionCode,ElementRelationCode,IsConform,replace(replace(formula,'R1','''Yes'''),'When','When PromotionCode='''+PromotionCode+''' and ')formula from #t1 where ElementRelationCode<>'R1') p pivot(max(IsConform) for ElementRelationCode in ([T1],[T2])) t
)a
declare @sql nvarchar(max),@col nvarchar(max)
select @col=ISNULL(@col+' ','case ')+formula from #t2
set @sql='
select a.*,b.P as DESC2 from #t1 a
inner join (select PromotionCode,'+@col+' else ''No'' end P from #t2) b on a.PromotionCode=b.PromotionCode'
print @sql
exec(@sql)
drop table #t1
drop table #t2
;WITH cet AS (
select 'P1'PromotionCode,'R1'ElementRelationCode,0IsConform,'When(T1=1 or T2=1)then R1'formula,N'买数量送数量'DESC0 union all
select'P1','T1',1,'When(T1=1 or T2=1)then R1', N'销售数量' union all
select'P1','T2',1,'When(T1=1 or T2=1)then R1',N'采购金额' union all
select'P2','R1',1,'When(T1=1 and T2=1)then R1',N'买数量送数量' union all
select'P2','T1',0,'When(T1=1 and T2=1)then R1', N'销售数量' union all
select'P2','T2',1,'When(T1=1 and T2=1)then R1',N'采购金额' union all
select'P3','R1',1,'When(T1=1 or T2=1)then R1',N'买数量送数量' union all
select'P3','T1',0,'When(T1=1 or T2=1)then R1', N'销售数量' union all
select'P3','T2',0,'When(T1=1 or T2=1)then R1',N'采购金额'
)
SELECT * INTO #a FROM cet
DECLARE @s VARCHAR(max)
SELECT @s=STUFF((
SELECT 'or (PromotionCode= '''+PromotionCode +''' and '+'('+replace(replace(replace(replace(replace(REPLACE(replace(REPLACE(formula,'When',''),'then R1',''),'=1',' and IsConform=1'),'=0',' and IsConform=0'),'T2',' ElementRelationCode=''t2'''),'T1','ElementRelationCode=''t1'''),' or ',') or ('),' and ',') and (')+')'
+') '
FROM #a GROUP BY PromotionCode, formula
FOR XML PATH('')),1,2,'')
SET @s='
SELECT a1.*,CASE WHEN aa.isExist IS NULL THEN ''No'' ELSE ''Yes'' END AS desc2 FROM #a AS a1
OUTER APPLY (SELECT TOP 1 1 AS isExist, * FROM #a AS a2 WHERE a2.PromotionCode=a1.PromotionCode AND a2.IsConform=1
AND ('+ @s + ')) aa'
SELECT @s
EXEC(@s)
P2的条件改为 and,那么P2就变成不满足条件,返回的是No
PromotionCode ElementRelationCode IsConform formula DESC0 desc2
P1 R1 0 When(T1=1 or T2=1)then R1 买数量送数量 Yes
P1 T1 1 When(T1=1 or T2=1)then R1 销售数量 Yes
P1 T2 1 When(T1=1 or T2=1)then R1 采购金额 Yes
P2 R1 1 When(T1=1 and T2=1)then R1 买数量送数量 No
P2 T1 0 When(T1=1 and T2=1)then R1 销售数量 No
P2 T2 1 When(T1=1 and T2=1)then R1 采购金额 No
P3 R1 1 When(T1=1 or T2=1)then R1 买数量送数量 No
P3 T1 0 When(T1=1 or T2=1)then R1 销售数量 No
P3 T2 0 When(T1=1 or T2=1)then R1 采购金额 No
DECLARE @s VARCHAR(max)
SELECT @s=STUFF((
SELECT 'or (PromotionCode= '''+PromotionCode +''' and '+'('+replace(replace(replace(replace(REPLACE(replace(REPLACE(formula,'When',''),'then R1',''),'=1',' and IsConform=1'),'=0',' and IsConform=0'),'T2',' ElementRelationCode=''t2'''),'T1','ElementRelationCode=''t1'''),' or ',') or (')+')'
+') '
FROM #a GROUP BY PromotionCode, formula
FOR XML PATH('')),1,2,'')
SET @s='
SELECT *,CASE WHEN aa.isExist IS NULL THEN ''No'' ELSE ''Yes'' END AS desc2 FROM #a AS a1
OUTER APPLY (SELECT TOP 1 1 AS isExist, * FROM #a AS a2 WHERE a2.PromotionCode=a1.PromotionCode AND a2.IsConform=1
AND ('+ @s + ')) aa'
SELECT @s
EXEC(@s)
SELECT *,
CASE
WHEN aa.isExist IS NULL THEN 'No'
ELSE 'Yes'
END AS desc2
FROM #a AS a1
OUTER APPLY (
SELECT TOP 1 1 AS isExist,
*
FROM #a AS a2
WHERE a2.PromotionCode = a1.PromotionCode
AND (
(
PromotionCode = 'P1'
AND (
(ElementRelationCode = 't1' AND IsConform = 1)
OR (ElementRelationCode = 't2' AND IsConform = 1)
)
)
OR (
PromotionCode = 'P2'
AND (
(ElementRelationCode = 't1' AND IsConform = 1)
OR (ElementRelationCode = 't2' AND IsConform = 1)
)
)
OR (
PromotionCode = 'P3'
AND (
(ElementRelationCode = 't1' AND IsConform = 1)
OR (ElementRelationCode = 't2' AND IsConform = 1)
)
)
)
) aa
DECLARE @s VARCHAR(max)
SELECT @s=STUFF((
SELECT 'or (PromotionCode= '''+PromotionCode +''' and '+'('+replace(replace(replace(replace(REPLACE(replace(REPLACE(formula,'When',''),'then R1',''),'=1',' and IsConform=1'),'=0',' and IsConform=0'),'T2',' ElementRelationCode=''t2'''),'T1','ElementRelationCode=''t1'''),' or ',') or (')+')'
+') '
FROM #a GROUP BY PromotionCode, formula
FOR XML PATH('')),1,2,'')
SET @s='
SELECT *,CASE WHEN aa.isExist IS NULL THEN ''No'' ELSE ''Yes'' END AS desc2 FROM #a AS a1
OUTER APPLY (SELECT TOP 1 1 AS isExist, * FROM #a AS a2 WHERE a2.PromotionCode=a1.PromotionCode AND a2.IsConform=1
AND ('+ @s + ')) aa'
SELECT @s
EXEC(@s)
构造的语句是:
SELECT *,CASE WHEN aa.isExist IS NULL THEN 'No' ELSE 'Yes' END AS desc2 FROM #a AS a1
OUTER APPLY (SELECT TOP 1 1 AS isExist, * FROM #a AS a2 WHERE a2.PromotionCode=a1.PromotionCode AND a2.IsConform=1
AND ( (PromotionCode= 'P1' and ((ElementRelationCode='t1' and IsConform=1) or ( ElementRelationCode='t2' and IsConform=1))) or (PromotionCode= 'P2' and ((ElementRelationCode='t1' and IsConform=1) or ( ElementRelationCode='t2' and IsConform=1))) or (PromotionCode= 'P3' and ((ElementRelationCode='t1' and IsConform=1) or ( ElementRelationCode='t2' and IsConform=1))) )) aa