34,575
社区成员
发帖
与我相关
我的任务
分享
declare @tdata table
(
sid varchar(5),
TT INT,
RR INT,
InsertTime DATETIME
)
INSERT INTO @tdata
SELECT 'T101',11,20,'2015/5/22 15:00' UNION ALL
SELECT 'T101',12,30,'2015/5/22 14:30' UNION ALL
SELECT 'T101',13,40,'2015/5/22 13:00' UNION ALL
SELECT 'T101',13,20,'2015/5/21 13:00' UNION ALL
SELECT 'T102',25,30,'2015/5/22 16:00' UNION ALL
SELECT 'T102',18,20,'2015/5/22 15:30' UNION ALL
SELECT 'T102',52,10,'2015/5/22 14:30' UNION ALL
SELECT 'T102',13,20,'2015/5/21 13:00' UNION ALL
SELECT 'T103',17,40,'2015/5/22 12:00' UNION ALL
SELECT 'T103',18,10,'2015/5/22 11:30' UNION ALL
SELECT 'T103',20,20,'2015/5/22 10:30' UNION ALL
SELECT 'T103',13,20,'2015/5/21 13:00' UNION ALL
SELECT 'T104',21,10,'2015/5/22 15:00' UNION ALL
SELECT 'T104',22,20,'2015/5/22 14:30' UNION ALL
SELECT 'T104',23,30,'2015/5/22 13:20' UNION ALL
SELECT 'T104',13,20,'2015/5/21 13:00' UNION ALL
SELECT 'T105',25,10,'2015/5/22 18:00' UNION ALL
SELECT 'T105',26,20,'2015/5/22 12:30' UNION ALL
SELECT 'T105',27,30,'2015/5/22 11:20' UNION ALL
SELECT 'T105',13,20,'2015/5/21 13:00' UNION ALL
SELECT 'T106',32,31,'2015/5/22 17:00' UNION ALL
SELECT 'T106',33,26,'2015/5/22 15:30' UNION ALL
SELECT 'T106',34,37,'2015/5/22 14:20' UNION ALL
SELECT 'T106',13,20,'2015/5/21 13:00'
SELECT
sid,
sum(RR) AS RR
FROM @tdata
WHERE sid NOT IN ('T105','T106') AND CONVERT(varchar(100), InsertTime, 111)<>'2015/05/21'
GROUP BY sid
sid RR
----- -----------
T101 90
T102 60
T103 70
T104 60
(4 行受影响)
SELECT [SID], SUM(RR) RR
FROM tdata
WHERE InsertTime >= '2015-05-22'
AND InsertTime < '2015-05-23'
AND [SID] IN ('T101','T102','T103','T104')
GROUP BY [SID]