sql server 2005 中：由三张表中的数据得到一张表，该表的某列对应两个表达式怎么办
SELECT dbo.sell_main.sell_id AS 销售单号, dbo.sp.sp_name AS 商品名称, dbo.sell_son.num AS 购买数量, dbo.sp.sp_dj AS 商品单价, dbo.sell_main.yg_id AS 收银员工号,
dbo.sell_main.sell_date AS 日期 into #temp
FROM dbo.sell_main INNER JOIN
dbo.sell_son ON dbo.sell_main.sp_id = dbo.sell_son.sp_id INNER JOIN
dbo.sp ON dbo.sell_son.sp_id = dbo.sp.sp_id
declare @xf money,@dj money,@num int,@zk int
declare @sql money
begin
SELECT @num=dbo.sell_son.num , @dj=dbo.sp.sp_dj ,@zk=dbo.sell_son.zk
FROM dbo.sell_main INNER JOIN
dbo.sell_son ON dbo.sell_main.sp_id = dbo.sell_son.sp_id INNER JOIN
dbo.sp ON dbo.sell_son.sp_id = dbo.sp.sp_id
select case
when zk=0 then '@xf=@dj*@num'
when zk>0 then '@xf=@dj*@num*@zk/10'
select @sql='alter table #temp add'+ @xf +'money '
end
exec(@sql)
[解决办法]
try
SELECT dbo.sell_main.sell_id AS 销售单号, dbo.sp.sp_name AS 商品名称, dbo.sell_son.num AS 购买数量, dbo.sp.sp_dj AS 商品单价, dbo.sell_main.yg_id AS 收银员工号, dbo.sell_main.sell_date AS 日期, case when dbo.sell_son.zk=0 then dbo.sell_son.num*dbo.sp.sp_dj when dbo.sell_son.zk>0 then dbo.sell_son.num*dbo.sp.sp_dj*dbo.sell_son.zk/10 end as xfinto #tempFROM dbo.sell_main INNER JOIN dbo.sell_son ON dbo.sell_main.sp_id = dbo.sell_son.sp_id INNER JOIN dbo.sp ON dbo.sell_son.sp_id = dbo.sp.sp_id
