有五个表格,列如下
1. Customer|Qtr|Product|Qty2. Customer|Qtr|Product|Price13. Customer|Qtr|Product|Price24. Qtr|Product|ASP15. Qtr|Product|ASP2现在要得到表格如下,金额的计算逻辑是这样的:如果表2的客户名、季度、产品都一致则用Price1来计算,否则考虑表3,如果还是不对则考虑表4,若表四的季度、和产品一致则用ASP1,否则同样的逻辑来计算表5Customer|Qtr|Qty|Amt
1. Customer|Qtr|Product|Qty2. Customer|Qtr|Product|Price13. Customer|Qtr|Product|Price24. Qtr|Product|ASP15. Qtr|Product|ASP2现在要得到表格如下,金额的计算逻辑是这样的:如果表2的客户名、季度、产品都一致则用Price1来计算,否则考虑表3,如果还是不对则考虑表4,若表四的季度、和产品一致则用ASP1,否则同样的逻辑来计算表5Customer|Qtr|Qty|Amt
update #t set Amt = a.Qty * b.ASP2 from #t a,table5 b where a.Qtr = b.Qtr and a.Product= b.Product
update #t set Amt = a.Qty * b.ASP1 from #t a,table4 b where a.Qtr = b.Qtr and a.Product= b.Product
update #t set Amt = a.Qty * b.Price2 from #t a,table3 b where a.Qtr = b.Qtr and a.Product= b.Product and a.Qtr = b.Qtr
update #t set Amt = a.Qty * b.Price1 from #t a,table2 b where a.Qtr = b.Qtr and a.Product= b.Product and a.Qtr = b.Qtrselect * from #t
update #t set Amt = a.Qty * b.ASP2 from #t a,table5 b where a.Qtr = b.Qtr and a.Product= b.Product
update #t set Amt = a.Qty * b.ASP1 from #t a,table4 b where a.Qtr = b.Qtr and a.Product= b.Product
update #t set Amt = a.Qty * b.Price2 from #t a,table3 b where a.Qtr = b.Qtr and a.Product= b.Product and a.Customer = b.Customer
update #t set Amt = a.Qty * b.Price1 from #t a,table2 b where a.Qtr = b.Qtr and a.Product= b.Product and a.Customer = b.Customerselect * from #t
COALESCE((t1.price1*t.Qty),(t2.price2*t.Qty),(t3.asp1*t.Qty),(t4.asp2*t.Qty)) as Amt
from 表1 t
left join 表2 t1 on t.Customer=t1.Customer and t.Qtr =t1.Qtr and t.Product =t1.Product
left join 表3 t2 on t.Customer=t2.Customer and t.Qtr =t2.Qtr and t.Product =t2.Product
left join 表4 t3 on t.Qtr =t3.Qtr and t.Product =t3.Product
left join 表5 t4 on t.Qtr =t4.Qtr and t.Product =t4.Product
返回其参数中第一个非空表达式。语法
COALESCE ( expression [ ,...n ] ) 参数
expression任何类型的表达式。n表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。返回类型
将相同的值作为 expression 返回。注释
如果所有自变量均为 NULL,则 COALESCE 返回 NULL 值。COALESCE(expression1,...n) 与此 CASE 函数等价:CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
where a.Customer=b.Customer and a.Qtr=b.Qtr and a.Product=b.Product insert #t
select a.Customer,a.Qtr,a.Product,a.Qty,Amt = b.Price2 into #t from table1 a,table3 b
where a.Customer=b.Customer and a.Qtr=b.Qtr and a.Product=b.Product
and not exists (
select 1 from #t where Customer=a.Customer and Qtr=a.Qtr and Product=a.Product
)insert #t
select a.Customer,a.Qtr,a.Product,a.Qty,Amt = b.ASP1 into #t from table1 a,table4 b
where a.Qtr=b.Qtr and a.Product=b.Product
and not exists (
select 1 from #t where Customer=a.Customer and Qtr=a.Qtr and Product=a.Product
)insert #t
select a.Customer,a.Qtr,a.Product,a.Qty,Amt = b.ASP2 into #t from table1 a,table5 b
where a.Qtr=b.Qtr and a.Product=b.Product
and not exists (
select 1 from #t where Customer=a.Customer and Qtr=a.Qtr and Product=a.Product
)--如果需要都找不到的数据
insert #t
select a.Customer,a.Qtr,a.Product,a.Qty,Amt = 0 into #t from table1 a
where not exists (
select 1 from #t where Customer=a.Customer and Qtr=a.Qtr and Product=a.Product
)select * from #tdrop table #t
---------------
可否说具体些吗,举个例子。。类似这种情况可以用case when 功能可以完成实现