员工号 数量 日期
1001 2 2009-9-1 12:22:21
1002 3 2009-9-1 12:23:22
1001 4 2009-9-1 13:22:22
1002 2 2009-9-1 13:23:23
1001 2 2009-9-2 12:22:21
1002 3 2009-9-2 12:23:22
1001 4 2009-9-2 13:22:22
1002 2 2009-9-2 13:23:23
---------------------------------------------------
要变成 如下：
员工号 数量 日期
1001 6 2009-9-1
1002 5 2009-9-1
1001 6 2009-9-2
1002 5 2009-9-2
怎么写sql
------解决方案--------------------
- SQL code
select 员工号,sum(数量) as 数量,convert(varchar(10),日期,120) as 日期from tbgroup by 员工号,convert(varchar(10),日期,120)
------解决方案--------------------
- SQL code
-- -- create table ta (empyeeno varchar(10),qty int,date datetime)-- insert ta select '1001','2','2009/9/1' union all-- select '1002','3','2009/9/1' union all-- select '1001','4','2009/9/1' union all-- select '1002','2','2009/9/1' union all-- select '1001','2','2009/9/2' union all-- select '1002','3','2009/9/2' union all-- select '1001','4','2009/9/2' union all-- select '1002','2','2009/9/2'select * from taselect empyeeno,sum(qty),date from ta group by empyeeno,date
------解决方案--------------------
- SQL code
select 员工号,sum(数量) as 数量,convert(varchar(10),日期,120) as 日期from tbgroup by 员工号,日期