数据分组求和解决思路_Sql Server

数据分组求和
举例现在有表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 codecreate 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