(select t4.NAME as'销售员', t1.u_dz as '送货地址', t1.u_je as '提成率', 0 as '张数' from [dbo].[@ASAP_YHFS_TCJE] T0 inner join [dbo].[@ASAP_YHFS_TCJE_H] T1 on t0.u_bh=t1.u_bh inner join ocrd t2 on t1.u_dz=t2.u_xq inner join ordr t3 on t3.cardcode=t2.cardcode inner join [dbo].[@SBO_PZ_BT_SJ] T4 ON T4.CODE=T3.U_FHR where (t3.docdate>='2008-08-01'AND t3.docdate <='2008-08-30') group by T1.U_JE,T1.U_SX,t3.u_fhr,t4.NAME ,t1.u_dz,t3.docnum) 运行结果:
段涛 江北 2.000000 0
陈智 华阳 5.000000 0
张玉平 龙泉 10.000000 0
(select 0 as '销售员',0 as'送货地址', 0 as '提成率',count(T3.U_FHR) as'张数' from ordr t3 inner join [dbo].[@SBO_PZ_BT_SJ] T4 ON T4.CODE=T3.U_FHR group by T3.U_FHR) 运行结果:
0 0 0 1
0 0 0 1
0 0 0 1
两个程序都没有问题 但是当我union all连接时 提示"将 nvarchar 值 '段涛' 转换为数据类型为 int 的列时发生语法错误。" 请教如何修改之?
段涛 江北 2.000000 0
陈智 华阳 5.000000 0
张玉平 龙泉 10.000000 0
(select 0 as '销售员',0 as'送货地址', 0 as '提成率',count(T3.U_FHR) as'张数' from ordr t3 inner join [dbo].[@SBO_PZ_BT_SJ] T4 ON T4.CODE=T3.U_FHR group by T3.U_FHR) 运行结果:
0 0 0 1
0 0 0 1
0 0 0 1
两个程序都没有问题 但是当我union all连接时 提示"将 nvarchar 值 '段涛' 转换为数据类型为 int 的列时发生语法错误。" 请教如何修改之?
呵呵,注意一下类型就可以了。
(select t4.NAME as'销售员', t1.u_dz as '送货地址', t1.u_je as '提成率', 0 as '张数' from [dbo].[@ASAP_YHFS_TCJE] T0 inner join [dbo].[@ASAP_YHFS_TCJE_H] T1 on t0.u_bh=t1.u_bh inner join ocrd t2 on t1.u_dz=t2.u_xq inner join ordr t3 on t3.cardcode=t2.cardcode inner join [dbo].[@SBO_PZ_BT_SJ] T4 ON T4.CODE=T3.U_FHR where (t3.docdate>='2008-08-01'AND t3.docdate <='2008-08-30') group by T1.U_JE,T1.U_SX,t3.u_fhr,t4.NAME ,t1.u_dz,t3.docnum)
union all
(select '0' as '销售员','0' as'送货地址', 0 as '提成率',count(T3.U_FHR) as'张数' from ordr t3 inner join [dbo].[@SBO_PZ_BT_SJ] T4 ON T4.CODE=T3.U_FHR group by T3.U_FHR)