我这样写sql语句怎么会出现问题

Question

create table #t1
(
  id int,
  code varchar(10),
  listdate  varchar(10),
  customname varchar(10),
  quantity decimal(18,4) default(0),
  price decimal(18,4) default(0),
  total decimal(18,4) default(0),
  receive decimal(18,4) default(0),
  other decimal(18,4) default(0),
  before decimal(18,4) default(0),
  amount decimal(18,4) default(0)
)
create table #t2
(
  id int,
  receive decimal(18,4) default(0),
  other decimal(18,4) default(0),
  before decimal(18,4) default(0)
)
insert #t1
select 1,'001','05-08-01','A001',20,2.3,46,null,null,null,null union
select 1,'001','05-08-01','A001',30,2,60,null,null,null,null union
select 1,'001','05-08-01','A001',10,1.2,12,null,null,null,null union
select 2,'002','05-08-05','A002',20,3.1,62,null,null,null,null union
select 2,'002','05-08-05','A002',30,2.2,66,null,null,null,null union
select 3,'003','02-08-08','A003',40,2,80,null,null,null,null union
select 4,'004','02-08-09','A004',10,2,20,null,null,null,null 
insert #t2
select 1,20,30,40 union
select 2,10,30,50 union
select 3,5,10,15--select * from #t1
--测试 
select id,code,listdate,customname,quantity=cast(quantity as int),price,total=cast(total as int),receive=cast(receive as int),other=cast(other as int),before=cast(before as int),amount=cast(amount as int)
from (
select [id]=convert(varchar,id),code,listdate,customname,quantity=convert(varchar,cast(quantity as int)),[price]=convert(varchar,cast(price as int)),
               total,[receive]=0,[other]=0,[before]=0,amount=0 from #t1
union
select '',code+'小计','','',sum(A.quantity),'',sum(total),sum(distinct isnull(B.receive,0)),
               sum(distinct isnull(B.other,0)),sum(distinct isnull(B.before,0)),
             sum(total)+sum(distinct isnull(B.other,0))-sum(distinct isnull(B.receive,0))-sum(distinct isnull(B.before,0))
from #t1 A
left join #t2 B on A.id=B.id
group by code 
        union
        select '','合计','','',sum(quantity),'',sum(total),
                       isnull((select sum(receive) from #t2),0),
                       isnull((select sum(other) from #t2),0),
                       isnull((select sum(before) from #t2),0),
                       sum(total)+isnull((select sum(other) from #t2),0)-isnull((select sum(receive) from #t2),0)
                       -isnull((select sum(before) from #t2),0)
        from #t1
    )t
order by codedrop  table #t1,#t2

Answer 1

這段代碼有問題
select id,code,listdate,customname,quantity,price,total,receive,other,before,amount
from (
select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]='',[other]='',[before]='',amount='' from #t1
union
select '',code+'小计','','',sum(A.quantity),'',sum(total),sum(distinct isnull(B.receive,0)),
               sum(distinct isnull(B.other,0)),sum(distinct isnull(B.before,0)),
             sum(total)+sum(distinct isnull(B.other,0))-sum(distinct isnull(B.receive,0))-sum(distinct isnull(B.before,0))
from #t1 A
left join #t2 B on A.id=B.id
group by code 
        union
        select '','合计','','',sum(quantity),'',sum(total),
                       isnull((select sum(receive) from #t2),0),
                       isnull((select sum(other) from #t2),0),
                       isnull((select sum(before) from #t2),0),
                       sum(total)+isnull((select sum(other) from #t2),0)-isnull((select sum(receive) from #t2),0)
                       -isnull((select sum(before) from #t2),0)
        from #t1
    )t
order by code-------------------------------------------------
select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]='',[other]='',[before]='',amount='' from #t1
union
select '',code+'小计','','',sum(A.quantity),'',sum(total),sum(distinct isnull(B.receive,0)),
               sum(distinct isnull(B.other,0)),sum(distinct isnull(B.before,0)),
             sum(total)+sum(distinct isnull(B.other,0))-sum(distinct isnull(B.receive,0))-sum(distinct isnull(B.before,0))
from #t1 A
left join #t2 B on A.id=B.id
group by code 你看用union連接的字段，前後數據類型不一樣
就比如說最後一個字段from #t1是amount=''是字符串
而from #t1是sum(total)+sum(distinct isnull(B.other,0))-sum(distinct isnull(B.receive,0))-sum(distinct isnull(B.before,0))是數字數據類型union連接的前後數據類型要一致

Answer 2

select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]='',[other]='',[before]='',amount='' from #t1
union
原来这一段改为：
select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]=0,[other]=0,[before]=0,amount=0  from #t1
union

Answer 3

[receive]='',[other]='',[before]='',amount=''-------------------------------------->
receive,other,before,amount

Answer 4

select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]='',[other]='',[before]='',amount='' from #t1
union
原来这一段改为：
select [id]=convert(varchar,id),code,listdate,customname,quantity,[price]=convert(varchar,price),
               total,[receive]=0,[other]=0,[before]=0,amount=0  from #t1
union