调用后台统计数据 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

比如

Select 产品代码,产品型号规格,颜色,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色

改为

(select a.*,b.计量单位 from (Select 产品代码,产品型号规格,颜色,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色) as a left join 销售订单明细 as b on a.产品代码=b.产品代码 and a.产品型号规格=b.产品型号规格 and a.颜色=b.颜色 )

销售发货按同样的方法改

太复杂，有简单的方法吗？

SQL代码中的0是什么意思？

不参与分组就只能这样的用法。

销售表没有发货数量，所以使用0表示

能否将21楼的代码后部分写全，我试了一下，没有成功。

把你试的代码发上来

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.C

'cmd.CommandText = "S elect *, 订单数量 - 发货数量 As 欠货数量 from (s elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,sum(发货数量) As 发货数量 from (S elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色 union all S elect 产品代码,产品型号规格,颜色,0 As 订单数量,sum(发货数量) As 发货数量 from 销售发货明细 group by 产品代码,产品型号规格,颜色) As a group by 产品代码,产品型号规格,颜色) as b where 订单数量 - 发货数量 <> 0 "

cmd.CommandText = "S elect *, 订单数量 - 发货数量 As 欠货数量 from (s elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,sum(发货数量) As 发货数量 from (S elect a.*,b.单位 from (S elect 产品代码,产品型号规格,颜色,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色) As a left join 销售订单明细 As b on a.产品代码=b.产品代码 And a.产品型号规格=b.产品型号规格 And a.颜色=b.颜色 union all S elect a.*,b.单位 from (S elect 产品代码,产品型号规格,颜色,sum(发货数量) As 发货数量,0 As 发货数量 from 销售发货明细 group by 产品代码,产品型号规格,颜色) As a left join 销售发货明细 As b on a.产品代码=b.产品代码 And a.产品型号规格=b.产品型号规格 And a.颜色=b.颜色 ) as b where 订单数量 - 发货数量 <> 0 "

dt = cmd.ExecuteReader()

Tables("销售发货_销售发货统计表").DataSource = dt

Tables("销售发货_销售发货统计表").AutoSizeCols() '设置自动列宽

cmd.CommandText = "S elect *, 订单数量 - 发货数量 As 欠货数量 from (s elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,sum(发货数量) As 发货数量 from (S elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色 union all S elect 产品代码,产品型号规格,颜色,0 As 订单数量,sum(发货数量) As 发货数量 from 销售发货明细 group by 产品代码,产品型号规格,颜色) As a group by 产品代码,产品型号规格,颜色) as b where 订单数量 - 发货数量 <> 0 "

红色sql替换为21楼的用法，然后把21楼销售订单明细改为销售发货明细，替换下面蓝色的sql

是这样吗？提示As附近有语法错误。

cmd.CommandText = "S elect *, 订单数量 - 发货数量 As 欠货数量 from (s elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,sum(发货数量) As 发货数量 from (S elect 产品代码,产品型号规格,颜色,单位,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色 union all (s elect a.*,b.单位 from (S elect 产品代码,产品型号规格,颜色,sum(订单数量) As 订单数量,0 As 发货数量 from 销售发货明细 group by 产品代码,产品型号规格,颜色) as a left join 销售发货明细 as b on a.产品代码=b.产品代码 and a.产品型号规格=b.产品型号规格 and a.颜色=b.颜色 ) as b where 订单数量 - 发货数量 <> 0 "

cmd.CommandText = "S elect *, 订单数量 - 发货数量 As 欠货数量 from ((select a.*,b.计量单位 from (Select 产品代码,产品型号规格,颜色,sum(订单数量) As 订单数量,0 As 发货数量 from 销售订单明细 group by 产品代码,产品型号规格,颜色) as a left join 销售订单明细 as b on a.产品代码=b.产品代码 and a.产品型号规格=b.产品型号规格 and a.颜色=b.颜色 ) union all (select a.*,b.计量单位 from (Select 产品代码,产品型号规格,颜色,. As 订单数量,sum(发货数量) from 销售发货明细 group by 产品代码,产品型号规格,颜色) as a left join 销售订单明细 as b on a.产品代码=b.产品代码 and a.产品型号规格=b.产品型号规格 and a.颜色=b.颜色 )) as b where 订单数量 - 发货数量 <> 0 "

主题：调用后台统计数据