WITH cet AS (
select 'P201506080005'PromotionCode,'1092000'CustomerCode, NULL TYPE, NULL DESC0, NULL SalesVolume, 15.0000 发票金额, 160.000000 销售金额, 10.0000 销售数量 UNION ALL
select 'P201506080005', 1092000 ,'买数量送数量 Ture 买数量送百分比数量,买数量送金额 Ture 买数量送数量', '买数量送百分比数量',0.0500, 15.0000, 160.000000, 10.0000 UNION ALL
select 'P201506080005', 1092000 ,'买数量送数量 Ture 买数量送百分比数量,买数量送金额 Ture 买数量送数量', '买数量送金额', 1.0000, 15.0000 ,160.000000 ,10.0000 UNION ALL
select 'P201506080005', 1092000,'买数量送数量 Ture 买数量送百分比数量,买数量送金额 Ture 买数量送数量', '买数量送数量', 1.0000, 15.0000 ,160.000000 ,10.0000 UNION ALL
select 'P201506080005', 1092000,'销售金额 Ture 发票金额,发票金额 Ture 销售金额', '发票金额', 12.0000, 15.0000, 160.000000, 10.0000 UNION ALL
select 'P201506080005', 1092000 ,'销售金额 Ture 发票金额,发票金额 Ture 销售金额', '销售金额', 1.0000, 15.0000 ,160.000000 ,10.0000
)
SELECT * FROM cet
想通过查询得到以下效果，新增一列，列值为Ture或者是False ,如果desc0值为发票金额，对应列salesvolume 的值为12，
列发票金额的值为15 那么15-12>=0 就得到值为ture
------解决思路----------------------
SELECT *,CASE WHEN DESC0='发票金额' THEN CASE WHEN [发票金额]-SalesVolume>=0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END ELSE CAST(0 AS bit) END AS DESC1 FROM cet
------解决思路----------------------
一定要等于15,12的话
SELECT *,CASE WHEN DESC0='发票金额' THEN
CASE WHEN [发票金额]=15 AND SalesVolume=12
THEN 'true' ELSE 'false' END
ELSE 'false' END AS DESC1 FROM cet
------解决思路----------------------
SELECT DESC0, SalesVolume, 发票金额, 销售金额, 销售数量, -- 只列相关字段，自己换成*
CASE WHEN (CASE WHEN DESC0 = '发票金额' THEN 发票金额
WHEN DESC0 = '销售金额' THEN 销售金额
WHEN DESC0 = '销售数量' THEN 销售数量
END
) > SalesVolume THEN 'TRUE'
END DESC1
FROM cet
DESC0 SalesVolume 发票金额 销售金额 销售数量 DESC1
------------------ ------------- ------------- ------------- ------------- -----
NULL NULL 15.0000 160.000000 10.0000 NULL
买数量送百分比数量 0.0500 15.0000 160.000000 10.0000 NULL
买数量送金额 1.0000 15.0000 160.000000 10.0000 NULL
买数量送数量 1.0000 15.0000 160.000000 10.0000 NULL
发票金额 12.0000 15.0000 160.000000 10.0000 TRUE
销售金额 1.0000 15.0000 160.000000 10.0000 TRUE