请教SQL语句,查询每个指定编号的列的总和

greenfang2018 2015-07-20 04:09:19
sid(编号) TT RR InsertTime(插入时间)
T101 11 20 2015/5/22 15:00
T101 12 30 2015/5/22 14:30
T101 13 40 2015/5/22 13:00
T101 13 20 2015/5/21 13:00

T102 25 30 2015/5/22 16:00
T102 18 20 2015/5/22 15:30
T102 52 10 2015/5/22 14:30
T102 13 20 2015/5/21 13:00

T103 17 40 2015/5/22 12:00
T103 18 10 2015/5/22 11:30
T103 20 20 2015/5/22 10:30
T103 13 20 2015/5/21 13:00

T104 21 10 2015/5/22 15:00
T104 22 20 2015/5/22 14:30
T104 23 30 2015/5/22 13:20
T104 13 20 2015/5/21 13:00

T105 25 10 2015/5/22 18:00
T105 26 20 2015/5/22 12:30
T105 27 30 2015/5/22 11:20
T105 13 20 2015/5/21 13:00

T106 32 31 2015/5/22 17:00
T106 33 26 2015/5/22 15:30
T106 34 37 2015/5/22 14:20
T106 13 20 2015/5/21 13:00

表名:tdata

我想得到以下结果(也就是得到编号T101 ,T102 , T103,T104的RR的2015/5/22日的商品数量总和记录,不要5月21日的记录):
sid RR
T101 90
T102 60
T103 70
T104 60


也就是得到指定编号T101 ,T102 , T103, T104 的RR的2015/5/22日商品数量总和,不要T105,T106的记录,请教SQL语句如何写,感谢各位!!!!!!!!!!
...全文
164 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
许晨旭 2015-07-20
  • 打赏
  • 举报
回复
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 行受影响)
greenfang2018 2015-07-20
  • 打赏
  • 举报
回复
谢谢,感谢,可以啦
Pact_Alice 2015-07-20
  • 打赏
  • 举报
回复
;WITH cet AS ( select 'T101' AS sid,11 AS TT ,20 AS RR ,' 2015/5/22 15:00'AS InsertTime 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 SumRR FROM cet WHERE [sid] IN ('T101','T102','T103','T104') AND InsertTime!='2015/5/21 13:00' GROUP BY SID
Tiger_Zhao 2015-07-20
  • 打赏
  • 举报
回复
更正:AND [SID] <= 'T104'
Tiger_Zhao 2015-07-20
  • 打赏
  • 举报
回复
    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]

如果编号格式固定,最后一个条件换成 AND [SID] < 'T104' 性能好一点

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧