表
route , name, telephone, Acctime, number flag
088 x 111 2009-9-1 66 1
088 x 111 2009-10-1 99 1
088 x 111 2009-10-3 88 1
088 x 111 2009-10-4 88 0
089 y 123 2009-1-1 56 1
089 y 123 2009-1-2 33 1我想得到088 x 111 2009-9-1(最早日期) 253(66+88+99) 1
088 x 111 2009-10-4 88 0
089 y 123 2009-1-1 89 1我觉得很难 大家帮帮 周末愉快
route , name, telephone, Acctime, number flag
088 x 111 2009-9-1 66 1
088 x 111 2009-10-1 99 1
088 x 111 2009-10-3 88 1
088 x 111 2009-10-4 88 0
089 y 123 2009-1-1 56 1
089 y 123 2009-1-2 33 1我想得到088 x 111 2009-9-1(最早日期) 253(66+88+99) 1
088 x 111 2009-10-4 88 0
089 y 123 2009-1-1 89 1我觉得很难 大家帮帮 周末愉快
[route] ,
name,
Acctime=MIN(Acctime),
number=SUM(number),
flag
from tb
group by [route] , name,flag
select route , name, telephone , min(Acctime)as Acctime,sum(number) as number,flag
from tb
group by route , name, telephone ,flag
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( route varchar(10), name varchar(10) , telephone int,Acctime datetime,number int , flag int)
go
insert tb SELECT '088' , 'x' , 111, '2009-9-1' , 66 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-1', 99 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-3', 88 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-4', 88 , 0 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-1' , 56 , 1 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-2' , 33 , 1
go
select
[route] ,
name,
Acctime=convert(varchar(10),MIN(Acctime),120),
number=SUM(number),
flag
from tb
group by [route] , name,flag
order by name,SUM(number) desc
route name Acctime number flag
---------- ---------- ---------- ----------- -----------
088 x 2009-09-01 253 1
088 x 2009-10-04 88 0
089 y 2009-01-01 89 1
Drop table [tb]
Go
Create table [tb]([route] int,[name] nvarchar(1),[telephone] int,[Acctime] Datetime,[number] int,[flag] int)
Insert tb
Select 088,'x',111,'2009-9-1',66,1 union all
Select 088,'x',111,'2009-10-1',99,1 union all
Select 088,'x',111,'2009-10-3',88,1 union all
Select 088,'x',111,'2009-10-4',88,0 union all
Select 089,'y',123,'2009-1-1',56,1 union all
Select 089,'y',123,'2009-1-2',33,1
Go
--Select * from tb-->SQL查询如下:
select
route,
telephone,
name,
acctime=min(acctime),
number=sum(number),
flag
from tb
group by [route] ,telephone, name,flag
order by 1,2,3,4/*
route telephone name acctime number flag
----------- ----------- ---- ----------------------- ----------- -----------
88 111 x 2009-09-01 00:00:00.000 253 1
88 111 x 2009-10-04 00:00:00.000 88 0
89 123 y 2009-01-01 00:00:00.000 89 1(3 行受影响)
*/
order by 1,2,3,4
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( route varchar(10), name varchar(10) , telephone int,Acctime datetime,number int , flag int)
go
insert tb SELECT '088' , 'x' , 111, '2009-9-1' , 66 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-1', 99 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-3', 88 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-4', 88 , 0 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-1' , 56 , 1 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-2' , 33 , 1
goselect route , name, telephone , min(Acctime)as Acctime,sum(number) as number,flag
from tb
group by route , name, telephone ,flag route name telephone Acctime number flag
---------- ---------- ----------- ----------------------- ----------- -----------
088 x 111 2009-10-04 00:00:00.000 88 0
088 x 111 2009-09-01 00:00:00.000 253 1
089 y 123 2009-01-01 00:00:00.000 89 1(3 行受影响)
DROP TABLE tb
GO
CREATE TABLE tb( route varchar(10), name varchar(10) , telephone int,Acctime datetime,number int , flag int)
go
insert tb SELECT '088' , 'x' , 111, '2009-9-1' , 66 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-1', 99 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-3', 88 , 1 UNION ALL SELECT
'088' , 'x' , 111, '2009-10-4', 88 , 0 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-1' , 56 , 1 UNION ALL SELECT
'089' , 'y' , 123, '2009-1-2' , 33 , 1
select * from tbselect route,name,telephone,min(Acctime),sum(number) from tb group by flag,name,route,telephone这样可以得到结果,你试试,flag放在前面他就首先按照flag分组
insert into @t
select'088','x','111','2009-9-1 ',66 ,1
union all select'088','x','111','2009-10-1', 99, 1
union all select'088','x','111','2009-10-3', 88, 1
union all select'088','x','111','2009-10-4', 88, 0
union all select'089','y','123','2009-1-1 ',56 ,1
union all select'089','y','123','2009-1-2 ',33 ,1 select route
,name = max(name)
,telephone = max(telephone)
,Acctime = min(Acctime)
,sum = sum(number)
,flag
from @t
group by route,flag
order by route,flag descroute name telephone Acctime sum flag
---------- ---------- ---------- -------------------------- ----------- -----------
088 x 111 2009-09-01 00:00:00.000 253 1
088 x 111 2009-10-04 00:00:00.000 88 0
089 y 123 2009-01-01 00:00:00.000 89 1