用 SQL 如何实现这样的效果？(30分)

源表：
产品ID 销售渠道 数量
a1 出口 1000
a1 内销 2000
a2 出口 5000
a3 出口 3000
a3 内销 2000

结果:
总合计: 13000
出口 9000
内销 4000
用SQL如何来实现？

select Count(*) As 总合计 from tablename
select count(*) as 出口 from tablename where 销售渠道 = '出口'
select count(*) as 内销 from tablename where 销售渠道 = '内销'

思路及语句如下：
str：＝‘ （select sum（数量） AS 数量和 from table ） ’＋
‘ union （select sum（数量）AS 出口 from table where 销售渠道＝出口） ’＋
‘union（select sum（数量） AS 内销 from table where 销售渠道＝内销）’；
query。close;
query。sql.clear；
query。sql。add(str)；
query.open；

你试试，应该没有问题吧！ [][]

我马上试一试!!

来晚了
不过楼上用count 好像有问题吧
并且需要用 union 啊！
不用链接我估计QQ2001也知道的！ [][]

[][]

select '合计',sum(数量) As 总合计 from tablename
union
select '出口',sum(数量) as 出口 from tablename where 销售渠道 = '出口'
union
select '内销',sum(数量) as 内销 from tablename where 销售渠道 = '内销'

sorry,更正一下我的问题!!
源表：
产品ID 销售渠道 数量
a1 出口 1000
a1 内销 2000
a2 出口 5000
a3 出口 3000
a3 内销 2000

结果:
aa1 3000
aa2 5000
aa3 5000
总合计: 13000
出口 9000
内销 4000
用SQL如何来实现？

你更正，我也更正！ [][]

这是笨办法，但最简单：
select sum(数量) As aa1 from tablename where ID＝a1
union
select sum(数量) As aa2 from tablename where ID＝a2
union
select sum(数量) As aa3 from tablename where ID＝a3
union
select sum(数量) as 总合计 from tablename
uinon
select sum(数量) as 出口 from tablename where 销售渠道 = '出口'
union
select sum(数量) as 内销 from tablename where 销售渠道 = '内销'

select 产品ID,SUM(数量) from tablename group by 产品ID
union
select '合计',sum(数量) from tablename
union
select '出口',sum(数量) from tablename where 销售渠道 = '出口'
union
select '内销',sum(数量) from tablename where 销售渠道 = '内销'

楼上的更省时省事！
推荐！ [8D][8D][8D][8D]

select 'a'+產品ID,sum(數量) from tablename group by 產品ID
union
select '合計',sum(數量) from tablename
union
select '出口',sum(數量) from tablename where 銷售渠道='出口'
union
select '內銷',sum(數量) from tablename where 銷售渠道='內銷'
不知成不成，可試試。

to cbdiy:
怎么老是说不匹配，我试了union 前后的select 语句都没有错字段都一样!!

什么數據庫？

select '合计',sum(数量) 数量 from tablename
union
select '出口',sum(数量) 数量 from tablename where 销售渠道 = '出口'
union
select '内销',sum(数量) 数量 from tablename where 销售渠道 = '内销'

select 销售渠道, sum(数量)
from tablename
group by 销售渠道 with Roolup

union 前后select 的结果都是对的，我用了union的提示是'type mismatch in expression'
请各位帮我看一下代码（急用）：
aa2:='select a.cpmc,';
aa2:=aa2+' sum(a.by_cl),sum(a.bn_cl),sum(a.sn_cl),(sum(a.bn_cl)/sum(a.sn_cl)-1)*100,';
aa2:=aa2+' sum(a.by_cz),sum(a.bn_cz),sum(a.sn_cz),(sum(a.bn_cz)/sum(a.sn_cz)-1)*100,';
aa2:=aa2+' sum(a.by_xl),sum(a.bn_xl),sum(a.sn_xl),(sum(a.bn_xl)/sum(a.sn_xl)-1)*100,';
aa2:=aa2+' sum(a.by_xe),sum(a.bn_xe),sum(a.sn_xe),(sum(a.bn_xe)/sum(a.sn_xe)-1)*100,';
aa2:=aa2+' sum(a.by_hl),sum(a.bn_hl),sum(a.sn_hl),(sum(a.bn_hl)/sum(a.sn_hl)-1)*100,';
aa2:=aa2+' sum(a.by_ccl),sum(a.bn_ccl),sum(a.sn_ccl),(sum(a.bn_ccl)/sum(a.sn_ccl)-1)*100,';
aa2:=aa2+' sum(a.by_cce),sum(a.bn_cce),sum(a.sn_cce),(sum(a.bn_cce)/sum(a.sn_cce)-1)*100,';
aa2:=aa2+' sum(a.by_qt),sum(a.bn_qt),sum(a.sn_qt),(sum(a.bn_qt)/sum(a.sn_qt)-1)*100';
aa2:=aa2+' from temp a';
aa2:=aa2+' group by a.cpmc';

aa2:=aa2+' union';

aa2:=aa2+' select '+'''总合计'''+'as b.cpmc,';
aa2:=aa2+' sum(b.by_cl),sum(b.bn_cl),sum(b.sn_cl),(sum(b.bn_cl)/sum(b.sn_cl)-1)*100,';
aa2:=aa2+' sum(b.by_cz),sum(b.bn_cz),sum(b.sn_cz),(sum(b.bn_cz)/sum(b.sn_cz)-1)*100,';
aa2:=aa2+' sum(b.by_xl),sum(b.bn_xl),sum(b.sn_xl),(sum(b.bn_xl)/sum(b.sn_xl)-1)*100,';
aa2:=aa2+' sum(b.by_xe),sum(b.bn_xe),sum(b.sn_xe),(sum(b.bn_xe)/sum(b.sn_xe)-1)*100,';
aa2:=aa2+' sum(b.by_hl),sum(b.bn_hl),sum(b.sn_hl),(sum(b.bn_hl)/sum(b.sn_hl)-1)*100,';
aa2:=aa2+' sum(b.by_ccl),sum(b.bn_ccl),sum(b.sn_ccl),(sum(b.bn_ccl)/sum(b.sn_ccl)-1)*100,';
aa2:=aa2+' sum(b.by_cce),sum(b.bn_cce),sum(b.sn_cce),(sum(b.bn_cce)/sum(b.sn_cce)-1)*100,';
aa2:=aa2+' sum(b.by_qt),sum(b.bn_qt),sum(b.sn_qt),(sum(b.bn_qt)/sum(b.sn_qt)-1)*100';
aa2:=aa2+' from temp b';

aa2:=aa2+' union';

aa2:=aa2+' select xsqd as c.cpmc,';
aa2:=aa2+' sum(c.by_cl),sum(c.bn_cl),sum(c.sn_cl),(sum(c.bn_cl)/sum(c.sn_cl)-1)*100,';
aa2:=aa2+' sum(c.by_cz),sum(c.bn_cz),sum(c.sn_cz),(sum(c.bn_cz)/sum(c.sn_cz)-1)*100,';
aa2:=aa2+' sum(c.by_xl),sum(c.bn_xl),sum(c.sn_xl),(sum(c.bn_xl)/sum(c.sn_xl)-1)*100,';
aa2:=aa2+' sum(c.by_xe),sum(c.bn_xe),sum(c.sn_xe),(sum(c.bn_xe)/sum(c.sn_xe)-1)*100,';
aa2:=aa2+' sum(c.by_hl),sum(c.bn_hl),sum(c.sn_hl),(sum(c.bn_hl)/sum(c.sn_hl)-1)*100,';
aa2:=aa2+' sum(c.by_ccl),sum(c.bn_ccl),sum(c.sn_ccl),(sum(c.bn_ccl)/sum(c.sn_ccl)-1)*100,';
aa2:=aa2+' sum(c.by_cce),sum(c.bn_cce),sum(c.sn_cce),(sum(c.bn_cce)/sum(c.sn_cce)-1)*100,';
aa2:=aa2+' sum(c.by_qt),sum(c.bn_qt),sum(c.sn_qt),(sum(c.bn_qt)/sum(c.sn_qt)-1)*100';
aa2:=aa2+' from temp c';
aa2:=aa2+' group by xsqd';

to zhaixiaobo:
dbf数据库

把aa2:='select a.cpmc,'改為：
aa2:='select cast(a.cpmc as varhchar(10)),';
試試。
可能是查詢的cpmc類型不為字符，是純數字組成，
造成與下列對應的'總合計'類型不符。

select 銷售渠道,sum(數量) from 源表 group by 銷售渠道

写死【銷售渠道】、【产品ID】会有问题的。

select 产品ID, sum(數量) from 源表 group by 产品ID
select 銷售渠道, sum(數量) from 源表 group by 銷售渠道