SELECT
TName,TType,
MIN(TStartTime) AS TStartTime,
MAX(TEndTime) AS TEndTime,
SUM(TCost) AS TCost
FROM tb
GROUP BY TName,TType
TName,TType,
MIN(TStartTime) AS TStartTime,
MAX(TEndTime) AS TEndTime,
SUM(TCost) AS TCost
FROM tb
GROUP BY TName,TType
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (TName VARCHAR(10),TType VARCHAR(10),TStartTime DATETIME,TEndTime DATETIME,TCost INT)
INSERT INTO @T
SELECT '多功能椅子','多功能产品','2009-01-21 08:30:21.000','2009-01-21 11:30:21.000',35670 UNION ALL
SELECT '多功能椅子','多功能产品','2009-01-22 09:00:21.000','2009-01-22 11:55:21.000',34200 UNION ALL
SELECT '多功能椅子','多功能产品','2009-01-22 13:30:21.000','2009-01-22 22:20:21.000',45000--SQL查询如下:SELECT
TName,TType,
MIN(TStartTime) AS TStartTime,
MAX(TEndTime) AS TEndTime,
SUM(TCost) AS TCost
FROM @T
GROUP BY TName,TType,CONVERT(VARCHAR(8),TStartTime,112)
/*
TName TType TStartTime TEndTime TCost
---------- ---------- ----------------------- ----------------------- -----------
多功能椅子 多功能产品 2009-01-21 08:30:21.000 2009-01-21 11:30:21.000 35670
多功能椅子 多功能产品 2009-01-22 09:00:21.000 2009-01-22 22:20:21.000 79200(2 行受影响)
*/
insert @t select '多功能椅子','多功能产品', '2009-01-21 08:30:21.000', '2009-01-21 11:30:21.000', 35670
insert @t select '多功能椅子','多功能产品', '2009-01-22 09:00:21.000', '2009-01-22 11:55:21.000', 34200
insert @t select '多功能椅子','多功能产品', '2009-01-22 13:30:21.000', '2009-01-22 22:20:21.000', 45000 select
TName,
TType,
TStartTime=max(TStartTime),
TEndTime=max(TEndTime),
TCost=sum(TCost)
from @t
group by convert(varchar(10),TStartTime,120),convert(varchar(10),TEndTime,120),Tname,TTypeTName TType TStartTime TEndTime TCost
---------- ---------- ----------------------- ----------------------- -----------
多功能椅子 多功能产品 2009-01-21 08:30:21.000 2009-01-21 11:30:21.000 35670
多功能椅子 多功能产品 2009-01-22 13:30:21.000 2009-01-22 22:20:21.000 79200(2 行受影响)
declare @t table(TName varchar(10),TType varchar(10),TStartTime datetime,TEndTime datetime, TCost int)
insert @t select '多功能椅子','多功能产品', '2009-01-21 08:30:21.000', '2009-01-21 11:30:21.000', 35670
insert @t select '多功能椅子','多功能产品', '2009-01-22 09:00:21.000', '2009-01-22 11:55:21.000', 34200
insert @t select '多功能椅子','多功能产品', '2009-01-22 13:30:21.000', '2009-01-22 22:20:21.000', 45000 select
TName,
TType,
TStartTime=min(TStartTime),
TEndTime=max(TEndTime),
TCost=sum(TCost)
from @t
group by convert(varchar(10),TStartTime,120),convert(varchar(10),TEndTime,120),Tname,TType
/*
TName TType TStartTime TEndTime TCost
---------- ---------- ----------------------- ----------------------- -----------
多功能椅子 多功能产品 2009-01-21 08:30:21.000 2009-01-21 11:30:21.000 35670
多功能椅子 多功能产品 2009-01-22 09:00:21.000 2009-01-22 22:20:21.000 79200(2 行受影响)
*/
select select TName,TType,min(TStartTime),max(TEndTime),sum(TCost) from tb
group by TName,TType,convert(varchar(10),TStartTime,112)
group by TName,TType,convert(varchar(10),TStartTime,120)
order by TName,TType,convert(varchar(10),TStartTime,120)
group by TName,TType,convert(varchar(10),TStartTime,120)
order by TName,TType,convert(varchar(10),TStartTime,120)另楼主的数据最后一行有错误,估计是4500
SELECT
'多功能椅子' as TName,max(TType) as TType,
max(TStartTime) AS TStartTime,
max(TEndTime) AS TEndTime,
sum(TCost) AS TCost
FROM @T
WHERE TName='多功能椅子'
GROUP BY TType,CONVERT(VARCHAR(10),TStartTime,120)
SELECT
'多功能椅子' as TName,max(TType) as TType,
CONVERT(VARCHAR(10),max(TStartTime),120) AS Time,
sum(TCost) AS TCost
FROM @T
WHERE TName='多功能椅子'
GROUP BY TType,CONVERT(VARCHAR(10),TStartTime,120)
TName,
TType,
TStartTime=min(TStartTime),
TEndTime=max(TEndTime),
TCost=sum(TCost)
from @t
group by convert(varchar(10),TStartTime,120),convert(varchar(10),TEndTime,120),Tname,TType