数据分组求和解决思路

数据分组求和
举例 现在有表drug表

有字段 drug_id ,batch_code,invoice_number, number,create_date
现在我想把表中数据的查询结果以前三列相等为条件 对相同列的number进行加和 
返回一条数据

比如

001 p001 f123 10  
001 p001 f123 20  
001 p001 f123 30  
001 p001 f123 40  
001 p002 f123 10

返回
001 p001 f123 100
001 p002 f123 10

[解决办法]
select drug_id ,batch_code,invoice_number, sum(number) from drug group by drug_id ,batch_code,invoice_number
[解决办法]

SQL code

declare @drug table (    drug_id varchar(3),    batch_code varchar(4),    invoice_number varchar(4),    number int,    create_date datetime)insert into @drugselect '001','p001','f123',10,null union allselect '001','p001','f123',20,null union allselect '001','p001','f123',30,null union allselect '001','p001','f123',40,null union allselect '001','p002','f123',10,nullselect     drug_id,batch_code,invoice_number,number=sum(number) from @drug group by drug_id,batch_code,invoice_number/*drug_id batch_code invoice_number number------- ---------- -------------- -----------001     p001       f123           100001     p002       f123           10*/
[解决办法]
select drug_id ,batch_code,invoice_number, sum(number) from drug group by drug_id ,batch_code,invoice_number
[解决办法]
create table tbl  
(
   drug_id varchar(3),
   batch_code varchar(4),
   invoice_number varchar(4),
   number int,
   create_date datetime
)
insert tbl  
select '001','p001','f123',10,null union all
select '001','p001','f123',20,null union all
select '001','p001','f123',30,null union all
select '001','p001','f123',40,null union all
select '001','p002','f123',10,null

select  
   drug_id,batch_code,invoice_number,number=sum(number)  
from tbl  
group by drug_id,batch_code,invoice_number
/*
结果表：
drug_id batch_code invoice_number number
001     p001       f123           100
001     p002       f123           10
*/

[解决办法]
select drug_id ,batch_code,invoice_number, sum(number) number
from drug
group by drug_id ,batch_code,invoice_number
[解决办法]
SQL code
create table drug(drug_id varchar(10), batch_code varchar(10), invoice_number varchar(10), number int, create_date datetime)goinsert into drugselect '001','p001','f123',10,null union allselect '001','p001','f123',20,null union allselect '001','p001','f123',30,null union allselect '001','p001','f123',40,null union allselect '001','p002','f123',10,nullgoselect drug_id,batch_code,invoice_number,number=SUM(number) from drug group by drug_id,batch_code,invoice_numbergodrop table drug