求一条语句，对我来说很难！

[DelphiStudy]

表名：物资
字段：单位名称 采购数量 采购金额 计划号 内外订
铅笔厂 1000 1000 3-8-16 内
铅笔厂 1000 1000 3-8-21 外
铅笔厂 1500 2000 3-8-52 外
铅笔厂 1000 1000 3-8-12 内

钢笔厂 .............................
钢笔厂 ............................

统计出各单位的计划项数，总的采购数量，总的采购金额，内外订分开的统计.
要这样的形式：以铅笔厂为例
单位名 合计划数 合采购数 合采购金 内订计划数 内订采购数 内订采购金 外订计划数
铅笔厂 4 4500 5000 2 2000 2000 2

[DelphiStudy]

接上表： 外订采购数 外订采购金
25000 3000

用触发器也可以的。

[pengdali]

select 单位名称,count(*) 合计划数,sum(采购数量) 合采购数,sum(采购金额) 合采购金,
sum(case when 内外订='内' then 1 else 0 end) 内订计划数,
sum(case when 内外订='内' then 采购数量 else 0 end) 内订采购数,
sum(case when 内外订='内' then 采购金额 else 0 end) 内订采购金,
sum(case when 内外订='外' then 1 else 0 end) 外订计划数,
sum(case when 内外订='外' then 采购数量 else 0 end) 外订采购数,
sum(case when 内外订='外' then 采购金额 else 0 end) 外订采购金
group by 单位名称

[liuyun2003]

select 单位名，count(*) as 合计划数,sum(采购数量) as 合采购数量，count(case when 内外订=‘内’then 1 else 0 end ) as 内订计划数,sum(case when 内外订=‘内’then 采购数量else 0 end ) as 内订采购数，sum(case when 内外订=‘内’then 采购金额else 0 end ) as 内订采购金，count(case when 内外订=‘外’then 1 else 0 end ) as 外订计划数,sum(case when 内外订=‘外’then 采购数量else 0 end ) as 外订采购数，sum(case when 内外订=‘外’then 采购金额else 0 end ) as 外订采购金 from 物资 group by 单位名称

[chpp_2000]

select 单位名称,count(*) as 合采购数, sum(采购数量) as ,sum(采购金额) as 合采购数,
case 内外订 when '内' then count(*) else 0 end as 内订采购数,
case 内外订 when '内' then sum(采购数量) else 0 end as 内订采购数,
case 内外订 when '内' then sum(采购金额) else 0 end as 内订采购金,

case 内外订 when '外' then count(*) else 0 end as 外订采购数,
case 内外订 when '外' then sum(采购数量) else 0 end as 外订采购数,
case 内外订 when '外' then sum(采购金额) else 0 end as 外订采购金
FROM 物资
GROUP BY 单位名称

[happydreamer]

select 单位名称,count(*) as 合计划数,sum(采购数量) as 合采购数,sum(采购金额) as
合采购金, count(case when 内外订='内' then 1 else null end ) as 内订计划数,
sum(case when 内外订='内' then 采购金额 else 0 end ) as 内订采购数 ,
count(case when 内外订='外' then 1 else null end ) as 外订计划数,
sum(case when 内外订='外' then 采购金额 else 0 end ) as 外订采购数
from 物资
group by 单位名称

[happydreamer]

select 单位名称,count(*) as 合计划数,sum(采购数量) as 合采购数,sum(采购金额) as
合采购金, count(case when 内外订='内' then 1 else null end ) as 内订计划数,
sum(case when 内外订='内' then 采购金额 else 0 end ) as 内订采购数 ,
count(case when 内外订='外' then 1 else null end ) as 外订计划数,
sum(case when 内外订='外' then 采购金额 else 0 end ) as 外订采购数
from 物资
group by 单位名称

[happydreamer]

select 单位名称,count(*) as 合计划数,sum(采购数量) as 合采购数,sum(采购金额) as
合采购金, count(case when 内外订='内' then 1 else null end ) as 内订计划数,
sum(case when 内外订='内' then 采购金额 else 0 end ) as 内订采购数 ,
count(case when 内外订='外' then 1 else null end ) as 外订计划数,
sum(case when 内外订='外' then 采购金额 else 0 end ) as 外订采购数
from 物资
group by 单位名称

[DelphiStudy]

好的，谢谢大虾们！结贴！

[DelphiStudy]

服了！

[会飞的鱼在沈阳]

select 单位名,count(计划号),sum( 采购数量),sum(采购金额),
(select count(内外订) as 内订计划数,sum( 采购数量) as 内订采购数,
sum(采购金额) as 内订采购金 from 物资 where 内外订=内)
,(select count(内外订) as 外订计划数 where 内外订=外)
from 物资 group by 单位名

[hjb111]

select 单位名,sum(计划号) 合计划数，sum(采购数量) 采购数量,sum(合采购金) 合采购金 from 物资 gropu by 单位名 right join (select sum(计划号) 内订计划数,sum(采购数量) 内订采购数 where 内外订='内'group by 单位名) a ,(select sum(计划号) 外订计划数,sum(采购数量) 外订采购数 where 内外订='外'group by 单位名) b

[pengdali]

insert 汇总 (合计划数,合采购数,合采购金,内订计划数,内订采购数,内订采购金,外订计划数)
select 单位名称,count(*) 合计划数,sum(采购数量) 合采购数,sum(采购金额) 合采购金,
sum(case when 内外订='内' then 1 else 0 end) 内订计划数,
sum(case when 内外订='内' then 采购数量 else 0 end) 内订采购数,
sum(case when 内外订='内' then 采购金额 else 0 end) 内订采购金,
sum(case when 内外订='外' then 1 else 0 end) 外订计划数,
sum(case when 内外订='外' then 采购数量 else 0 end) 外订采购数,
sum(case when 内外订='外' then 采购金额 else 0 end) 外订采购金
group by 单位名称