这个SQL语句怎么写？？？

Question

drop table #tmp
create table #tmp
(
product_no int,
batch_no smallint,
recipe_code varchar(10),
material_name  varchar(10),
act float,
batches int
)--product_no=1,生产2批
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(1,1,'recipe1','m1',101,2);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(1,1,'recipe1','m2',201,2);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(1,2,'recipe1','m1',102,2);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(1,2,'recipe1','m2',202,2);
--product_no=2,生产1批
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(2,1,'recipe1','m1',101,1);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(2,1,'recipe1','m2',201,1);
--product_no=3,生产3批
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(3,1,'recipe2','m1',101,3);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(3,2,'recipe2','m1',102,3);
insert into #tmp(product_no,batch_no,recipe_code,material_name,act,batches)values(3,3,'recipe2','m1',103,3);--(1)查询配方生产总数，共多少批? 这个sql语句错了，请问怎么写才正确???
select recipe_code,sum(act) as [total],count(*) as batches from #tmp group by recipe_code/*
recipe_code total batches
-------------------------------------
recipe1 908.0 6 -->错误，应该是只生产了3批
recipe2 306.0 3 -->对
*/--(2)查询配方生产总数，共多少批?
select recipe_code,material_name,sum(act) as [total],count(*) as batches  from #tmp group by recipe_code,material_name
/*
recipe_code total batches
------------------------------------
recipe1 m1 304.0 3 -->对
recipe2 m1 306.0 3 -->对
recipe1 m2 604.0 3 -->对
*/--(3)查询物料生产总数，共多少批?
select material_name,sum(act) as [total],count(*) as batches  from #tmp group by material_name
/*
material_name total batches
------------------------------------
m1 610.0 6 -->对
m2 604.0 3 -->对
*/

Answer 1

--1.
select recipe_code,sum(act) as [total],count(distinct product_no) as batches from #tmp group by recipe_code 

Answer 2

select recipe_code,sum(act) as [total],count(*) as batches from #tmp where material_name = 'm1' group by recipe_code 

Answer 3

--(1)查询配方生产总数，共多少批? 这个sql语句错了，请问怎么写才正确??? 
select recipe_code,sum(act) as [total],count(*) as batches from #tmp group by recipe_code /* 
recipe_code total batches 
------------------------------------- 
recipe1 908.0 6 -->错误，应该是只生产了3批 
recipe2 306.0 3 -->对 
*/ 
select recipe_code,sum(act) as [total], batches=（select count(distinct product_no) from #tmp） from #tmp group by recipe_code 

Answer 4

--1.
select recipe_code,sum(act) as [total],sum(distinct batches) as batches from #tmp group by recipe_code 

Answer 5

居然我前面写错了……
select recipe_code,[total]=sum([total]),batches=sum(batches) from
(select recipe_code,sum(act) as [total],min(batches) as batches from #tmp group by recipe_code,product_no) t
group by recipe_code
/*
recipe_code total                  batches
----------- ---------------------- -----------
recipe1     908                    3
recipe2     306                    3(2 row(s) affected)*/

Answer 6

-- 对于配方,产品,批次都不同才算一批.
select  recipe_code
       ,sum(act) as [total]
       ,count(distinct cast(product_no as varchar)+','+ cast(batch_no as varchar) ) as batches
  from  #tmp
 group  by recipe_code 

Answer 7

--或者
select  recipe_code,sum(total) as total,count(*) as batches
from
(
        select  recipe_code,product_no,batch_no,sum(act) as [total]
          from  #tmp
         group  by recipe_code,product_no,batch_no
)a
group by recipe_code