菜鸟请教高手SQL语句

Question

最近单位里的刷卡系统出了点问题，导致很多员工4月份（2017-04-01-2017-04-30 23:59:59）的饭卡内的钱被重复扣款了多笔，从后台查询统计重复扣款的记录后进行退还（重复扣款主要是在一分钟内发生的记录，消费金额一致，设备号相同，前后记录时间相差在一分钟之内），因为人数涉及众多，手工统计效率实在太低，故恳请高手给出SQL查询语句，得以快速实现查询统计。
表一：TOLL_CARD_DETAIL
(DepartID    varchar(50),--部门代码
EmployeeID    varchar(50),--人员编号
EmployeeName    varchar(50),--人员姓名
CheckDate    smalldatetime,--消费日期
CheckTime    varchar(8),--消费时间
CheckDateTime            datetime--完整的消费日期时间,
eatMoney       decimal(10, 2),--消费金额
Toll_MachineID    varchar(10),--设备编号
ChangingDescript  varchar(10))--发生原因表二：EMPLOYEE 
(DepartID    varchar(50), --部门代码
EmployeeID    varchar(50),--人员编号
EmployeeName    varchar(50),--人员姓名
EmployeeCard    varchar(10),--持卡卡号
CardBalance   varchar(20),--加密的卡内余额（以字符形式体现）insert into TOLL_CARD_DETAIL 
select  'a001002004001',    '87207024',    '季支付',    '2014-04-09 00:00:00.000',    '17:22:58',    '2014-04-09 17:23:08.000',    '5.00',    '008','正常消费'
union all select  'a001002004001',    '87207024',    '季支付',    '2014-04-09 00:00:00.000',    '17:23:08',    '2014-04-09 17:23:08.000',    '5.00',    '008','正常消费'
union all select 'a002010002001',    '88109976',    '高爱萍',    '2014-04-09 00:00:00.000',    '17:54:55',    '2014-04-09 17:54:55'.000',    '10.70',    '002','正常消费'
union all select 'a002010002001',    '88109976',    '高爱萍',    '2014-04-11 00:00:00.000',    '17:55:06',    '2014-04-11 17:55:06.000',    '9.70',    '012','正常消费'
union all select 'a002010002001',    '88109976',    '高爱萍',    '2014-04-11 00:00:00.000',    '17:54:55',    '2014-04-11 17:54:55'.000',    '9.70',    '012','正常消费'
union all select 'a002010002001',    '88109976',    '高爱萍',    '2014-04-09 00:00:00.000',    '17:55:06',    '2014-04-09 17:55:06.000',    '10.70',    '002','正常消费'
union all select 'a003006001001',    'S-PROCU-03',    '张维冰',    '2014-04-09 00:00:00.000',    '18:54:52',    '2014-04-09 18:54:52.000',    '3.50','008','正常消费'
union all select 'a002010002001',    '88109976',    '高爱萍',    '2014-04-10 00:00:00.000',    '17:04:24',    '2014-04-10 17:04:14.000',    '1.70',    '002','正常消费'
要求结果输出：（1）重复4月份所有刷卡扣款的记录（重复扣款的记录显示一笔，时间先后不论）
DepartID    EmployeeID    EmployeeName    CheckDate    CheckTime    CheckDateTime      eatMoney   Toll_MachineID        
 'a001002004001',    '87207024',    '季支付',    '2014-04-09 00:00:00.000',    '17:22:58',    '2014-04-09 17:23:08.000',    '5.00',    '008'
 'a002010002001',    '88109976',    '高爱萍',    '2014-04-09 00:00:00.000',    '17:55:06',    '2014-04-09 17:55:06.000',    '10.70',    '002'
'a002010002001',    '88109976',    '高爱萍',    '2014-04-11 00:00:00.000',    '17:55:06',    '2014-04-11 17:55:06.000',    '9.70',    '012'
（2）统计4月份每人错误扣款金额合计数,要将错误金额退还到个人卡上
要求输出：
DepartID                    EmployeeID    EmployeeName    Err_PayMent
a001002004001     87207024       季支付                            5.00
a002010002001     88109976       高爱萍                           20.40恳请高手不吝赐教  

Answer 1

;WITH tempa AS(
Select *,ROW_NUMBER()OVER(ORDER BY DepartID) AS num from #TOLL_CARD_DETAIL
)
SELECT  b.*
FROM    tempa a
        JOIN tempa b ON a.EmployeeName = b.EmployeeName
                        AND b.Toll_MachineID = a.Toll_MachineID
AND a.eatMoney = b.eatMoney
                        AND ABS(DATEDIFF(MINUTE, a.CheckDateTime,
                                         b.CheckDateTime)) <= 1
                        AND b.num > a.num

Answer 2

;WITH tempa AS(
Select *,ROW_NUMBER()OVER(ORDER BY DepartID) AS num from #TOLL_CARD_DETAIL
)
SELECT  t.DepartID ,
        t.EmployeeID ,
        t.EmployeeName ,
        SUM(eatMoney) AS Err_PayMent
FROM    ( SELECT    b.*
          FROM      tempa a
                    JOIN tempa b ON a.EmployeeName = b.EmployeeName
                                    AND b.Toll_MachineID = a.Toll_MachineID
                                    AND a.eatMoney = b.eatMoney
                                    AND ABS(DATEDIFF(MINUTE, a.CheckDateTime,
                                                     b.CheckDateTime)) <= 1
                                    AND b.num > a.num
        ) t
GROUP BY t.DepartID ,
        t.EmployeeID ,
        t.EmployeeName

Answer 3

表一：EMPLOYEE 
(DepartID    varchar(50), --部门代码
EmployeeID    varchar(50),--人员编号
EmployeeName    varchar(50),--人员姓名
EmployeeCard    varchar(10),--持卡卡号
CardBalance   decimal(10, 2)卡内余额表二：TOLL_CARD_DETAIL
(DepartID    varchar(50),--部门代码
EmployeeID    varchar(50),--人员编号
EmployeeName    varchar(50),--人员姓名
CheckDate    smalldatetime,--消费日期
CheckTime    varchar(8),--消费时间
CheckDateTime            datetime--完整的消费日期时间,
ChargeAcc    decimal(10, 2),--充值金额
eatMoney       decimal(10, 2),--消费金额
Toll_MachineID    varchar(10),--设备编号
ChangingDescript  varchar(10))--发生原因
CardBalance  decimal(10, 2) --当前余额--测试数据
insert into EMPLOYEE
select  'a001002004001',    '87207024',    '季支付',   '3581008190','153.85' UNION ALL
select  'a002010002001',    '88109976',    '高爱萍',    '35830028145',    '177.78'insert into TOLL_CARD_DETAIL 
select  'a001002004001',    '87207024',    '季支付',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '5.00',    '008','正常消费'  UNION ALL
select  'a001002004001',    '87207024',    '季支付',    '2017-05-06 00:00:00.000',    '11:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '7.50',    '008','正常消费'   UNION ALL
select  'a001002004001',    '87207024',    '季支付',    '2017-05-05 00:00:00.000',    '10:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '8.00',    '008','正常消费'   UNION ALL
select  'a001002004001',    '87207024',    '季支付',    '2017-05-01 00:00:00.000',    '00:00:01',    '2017-05-01 00:00:01.000',  '70.00'  '0.00',   NULL,'系统自动充值'   UNION ALL
select  'a001002004001',    '88109976',    '高爱萍',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '5.00',    '002','正常消费'  UNION ALL
select  'a001002004001',    '88109976',    '高爱萍',    '2017-05-06 00:00:00.000',    '11:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '7.50',    '003','正常消费'   UNION ALL
select  'a001002004001',    '88109976',    '高爱萍',    '2017-05-05 00:00:00.000',    '10:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '8.00',    '012','正常消费'   UNION ALL
 select  'a001002004001',    '88109976',    '高爱萍',    '2017-05-01 00:00:00.000',    '00:00:01',    '2017-05-01 00:00:02.000',  '85.00'  '0.00',   NULL,'系统自动充值' 要求根据EMPLOYEE表中的所有人的CardBalance   字段更新表二中所有人员的CardBalance  
DepartID    EmployeeID    EmployeeName    CheckDate    CheckTime    CheckDateTime      eatMoney   Toll_MachineID ChangingDescript  CardBalance
 'a001002004001',    '87207024',    '季支付',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '5.00',    '008','正常消费',158.85
'a001002004001',    '87207024',    '季支付',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '7.50',    '008','正常消费',166.35
'a001002004001',    '87207024',    '季支付',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '5.00',    '008','正常消费',174.35
 'a001002004001',    '87207024',    '季支付',    '2017-05-01 00:00:00.000',    '00:00:01',    '2017-05-06 17:22:58.000',  '70.00'  '0.00',   NULL,'系统自动充值' ,96.35
 'a001002004001',    '88109976',    '高爱萍',    '2017-05-06 00:00:00.000',    '17:22:58',    '2017-05-06 17:22:58.000',  '0.00'  '5.00',    '002','正常消费',182.78  
 'a001002004001',    '88109976',    '高爱萍',    '2017-05-06 00:00:00.000',    '11:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '7.50',    '003','正常消费',190.28   
 'a001002004001',    '88109976',    '高爱萍',    '2017-05-05 00:00:00.000',    '10:23:50',    '2017-05-06 17:22:58.000',  '0.00'  '8.00',    '012','正常消费' ,198.28
 'a001002004001',    '88109976',    '高爱萍',    '2017-05-01 00:00:00.000',    '00:00:01',    '2017-05-01 00:00:02.000',  '85.00'  '0.00',   NULL,'系统自动充值',113.28 
请高手赐教SQL语句

Answer 4

读你最后的数据例子不理解，我按照我的例子做了修改：
加入EMPLOYEE表的书balance是初始值，TOLL_CARD_DETAIL中的balance不应该按照你写的顺序，应该是时间顺序交易，然后balance进行变化。另外看你的例子，eatmoney发生是balance增加，那么balance的含义就是欠款（一般这种应该叫Credit，CreditCard就是取这个意思）不管怎么，你看看下面的结果和你想象的是否一样create table EMPLOYEE 
(DepartID    varchar(500), --部门代码
EmployeeID    varchar(500),--人员编号
EmployeeName    nvarchar(500),--人员姓名
EmployeeCard    varchar(100),--持卡卡号
CardBalance   decimal(10, 2))--卡内余额--表二：DROP TABLE TOLL_CARD_DETAIL
create table TOLL_CARD_DETAIL
(DepartID    varchar(500),--部门代码
EmployeeID    varchar(500),--人员编号
EmployeeName    nvarchar(500),--人员姓名
CheckDate    smalldatetime,--消费日期
CheckTime    varchar(80),--消费时间
CheckDateTime            datetime,--完整的消费日期时间,
ChargeAcc    decimal(10, 2),--充值金额
eatMoney       decimal(10, 2),--消费金额
Toll_MachineID    varchar(100),--设备编号
ChangingDescript  nvarchar(100),--发生原因
CardBalance  decimal(10, 2)) --当前余额insert into EMPLOYEE(DepartID,EmployeeID,EmployeeName,EmployeeCard,CardBalance)
select  'a001002004001',    '87207024',    N'季支付',   '3581008190','153.85' UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '35830028145',    '177.78'insert into TOLL_CARD_DETAIL (DepartID,EmployeeID,EmployeeName,CheckDate,CheckTime,CheckDateTime,ChargeAcc,eatMoney,Toll_MachineID,ChangingDescript)
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-04 00:00:00.000',    '17:22:58',    '2017-05-04 17:22:58.000',  '0.00' , '5.00',    '008',N'正常消费'  UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-05 00:00:00.000',    '11:23:50',    '2017-05-05 11:23:50.000',  '0.00' , '7.50',    '008',N'正常消费'   UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-06 00:00:00.000',    '10:23:50',    '2017-05-06 10:23:50.000',  '0.00' , '8.00',    '008',N'正常消费'   UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-07 00:00:00.000',    '00:00:01',    '2017-05-07 00:00:01.000',  '70.00' , '0.00',   NULL,N'系统自动充值'   UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-01 00:00:00.000',    '17:22:58',    '2017-05-01 17:22:58.000',  '0.00' , '5.00',    '002',N'正常消费'  UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-02 00:00:00.000',    '11:23:50',    '2017-05-02 11:23:50.000',  '0.00' , '7.50',    '003',N'正常消费'   UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-03 00:00:00.000',    '10:23:50',    '2017-05-03 10:23:50.000',  '0.00' , '8.00',    '012',N'正常消费'   UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-04 00:00:00.000',    '00:02:01',    '2017-05-04 00:00:02.000',  '85.00' , '0.00',   NULL,N'系统自动充值' 下面是计算每笔交易后新的Balance SELECT  a.*,e.CardBalance+ISNULL(pp.LastMoney,0)
 FROM  EMPLOYEE AS e
 left JOIN  TOLL_CARD_DETAIL AS a ON a.DepartID=e.DepartID AND a.EmployeeID=e.EmployeeID
 OUTER APPLY (SELECT SUM(p.eatMoney)-SUM(p.ChargeAcc) AS LastMoney 
 FROM  TOLL_CARD_DETAIL AS p WHERE  a.DepartID=p.DepartID AND a.EmployeeID=p.EmployeeID AND DATEDIFF(SECOND,p.CheckDateTime,a.CheckDateTime)>=0) pp
DepartID EmployeeID EmployeeName CheckDate CheckTime CheckDateTime ChargeAcc eatMoney Toll_MachineID ChangingDescript CardBalance NewBalance
a001002004001 87207024 季支付 2017-05-04 00:00:00 17:22:58 2017-05-04 17:22:58.000 0.00 5.00 008 正常消费 NULL 158.85
a001002004001 87207024 季支付 2017-05-05 00:00:00 11:23:50 2017-05-05 11:23:50.000 0.00 7.50 008 正常消费 NULL 166.35
a001002004001 87207024 季支付 2017-05-06 00:00:00 10:23:50 2017-05-06 10:23:50.000 0.00 8.00 008 正常消费 NULL 174.35
a001002004001 87207024 季支付 2017-05-07 00:00:00 00:00:01 2017-05-07 00:00:01.000 70.00 0.00 NULL 系统自动充值 NULL 104.35
a001002004001 88109976 高爱萍 2017-05-01 00:00:00 17:22:58 2017-05-01 17:22:58.000 0.00 5.00 002 正常消费 NULL 182.78
a001002004001 88109976 高爱萍 2017-05-02 00:00:00 11:23:50 2017-05-02 11:23:50.000 0.00 7.50 003 正常消费 NULL 190.28
a001002004001 88109976 高爱萍 2017-05-03 00:00:00 10:23:50 2017-05-03 10:23:50.000 0.00 8.00 012 正常消费 NULL 198.28
a001002004001 88109976 高爱萍 2017-05-04 00:00:00 00:02:01 2017-05-04 00:00:02.000 85.00 0.00 NULL 系统自动充值 NULL 113.28

Answer 5

update 语句 UPDATE a SET a.CardBalance=e.CardBalance+ISNULL(pp.LastMoney,0) FROM TOLL_CARD_DETAIL AS a INNER JOIN EMPLOYEE AS e ON a.DepartID=e.DepartID AND a.EmployeeID=e.EmployeeID
 OUTER APPLY (SELECT SUM(p.eatMoney)-SUM(p.ChargeAcc) AS LastMoney 
 FROM  TOLL_CARD_DETAIL AS p WHERE  a.DepartID=p.DepartID AND a.EmployeeID=p.EmployeeID AND DATEDIFF(SECOND,p.CheckDateTime,a.CheckDateTime)>=0) pp

Answer 6

@ch21st @sinat_28984567 
两位高手，帮我看下 http://chuantu.biz/t5/80/1494135957x1822613217.png

Answer 7

什么意思？你是说有重复刷卡记录，你应该先去掉重复记录。否则肯定影响结果，或者做个标记，其实我觉得在往这个表写数据时就应该考虑到这点不知道你用什么版本数据库，如果是SQL Server 2012+就省事多了
举个例子：TRUNCATE TABLE TOLL_CARD_DETAIL
insert into TOLL_CARD_DETAIL (DepartID,EmployeeID,EmployeeName,CheckDate,CheckTime,CheckDateTime,ChargeAcc,eatMoney,Toll_MachineID,ChangingDescript)
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-04 00:00:00.000',    '17:22:58',    '2017-05-04 17:22:58.000',  '0.00' , '5.00',    '008',N'正常消费'  UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-05 00:00:00.000',    '11:23:50',    '2017-05-05 11:23:50.000',  '0.00' , '7.50',    '008',N'正常消费'   UNION ALL
--The following three lines are duplicate data
SELECT  'a001002004001',    '87207024',    N'季支付',    '2017-05-06 00:00:00.000',    '10:23:50',    '2017-05-06 10:23:50.000',  '0.00' , '8.00',    '008',N'正常消费'   UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-06 00:00:00.000',    '10:23:52',    '2017-05-06 10:23:52.000',  '0.00' , '8.00',    '008',N'正常消费'   UNION ALL
select  'a001002004001',    '87207024',    N'季支付',    '2017-05-06 00:00:00.000',    '10:24:05',    '2017-05-06 10:24:05.000',  '0.00' , '8.00',    '008',N'正常消费'   UNION ALL
SELECT  'a001002004001',    '87207024',    N'季支付',    '2017-05-07 00:00:00.000',    '00:00:01',    '2017-05-07 00:00:01.000',  '70.00' , '0.00',   NULL,N'系统自动充值'   UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-01 00:00:00.000',    '17:22:58',    '2017-05-01 17:22:58.000',  '0.00' , '5.00',    '002',N'正常消费'  UNION ALL
--The following two lines are duplicate data
SELECT  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-02 00:00:00.000',    '11:23:50',    '2017-05-02 11:23:50.000',  '0.00' , '7.50',    '003',N'正常消费'   UNION ALL
SELECT  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-02 00:00:00.000',    '11:23:52',    '2017-05-02 11:23:52.000',  '0.00' , '7.50',    '003',N'正常消费'   UNION ALL
SELECT  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-03 00:00:00.000',    '10:23:50',    '2017-05-03 10:23:50.000',  '0.00' , '8.00',    '012',N'正常消费'   UNION ALL
select  'a001002004001',    '88109976',    N'高爱萍',    '2017-05-04 00:00:00.000',    '00:02:01',    '2017-05-04 00:00:02.000',  '85.00' , '0.00',   NULL,N'系统自动充值' 
--假设两笔相同消费记录之间少于30秒算重复
SELECT *,CASE WHEN DATEDIFF(SECOND, LAG(t.CheckDateTime)OVER(PARTITION BY t.DepartID,t.EmployeeID,t.Toll_MachineID,t.ChargeAcc,t.eatMoney ORDER BY t.CheckDateTime),t.CheckDateTime)<30 THEN 1 ELSE 0 END AS IsDuplicate FROM TOLL_CARD_DETAIL AS t
IsDuplicate=1 就是重复的记录应该删除过考虑再外的DepartID EmployeeID EmployeeName CheckDate CheckTime CheckDateTime ChargeAcc eatMoney Toll_MachineID ChangingDescript CardBalance IsDuplicate
a001002004001 87207024 季支付 2017-05-07 00:00:00 00:00:01 2017-05-07 00:00:01.000 70.00 0.00 NULL 系统自动充值 NULL 0
a001002004001 87207024 季支付 2017-05-04 00:00:00 17:22:58 2017-05-04 17:22:58.000 0.00 5.00 008 正常消费 NULL 0
a001002004001 87207024 季支付 2017-05-05 00:00:00 11:23:50 2017-05-05 11:23:50.000 0.00 7.50 008 正常消费 NULL 0
a001002004001 87207024 季支付 2017-05-06 00:00:00 10:23:50 2017-05-06 10:23:50.000 0.00 8.00 008 正常消费 NULL 0
a001002004001 87207024 季支付 2017-05-06 00:00:00 10:23:52 2017-05-06 10:23:52.000 0.00 8.00 008 正常消费 NULL 1
a001002004001 87207024 季支付 2017-05-06 00:00:00 10:24:05 2017-05-06 10:24:05.000 0.00 8.00 008 正常消费 NULL 1
a001002004001 88109976 高爱萍 2017-05-04 00:00:00 00:02:01 2017-05-04 00:00:02.000 85.00 0.00 NULL 系统自动充值 NULL 0
a001002004001 88109976 高爱萍 2017-05-01 00:00:00 17:22:58 2017-05-01 17:22:58.000 0.00 5.00 002 正常消费 NULL 0
a001002004001 88109976 高爱萍 2017-05-02 00:00:00 11:23:50 2017-05-02 11:23:50.000 0.00 7.50 003 正常消费 NULL 0
a001002004001 88109976 高爱萍 2017-05-02 00:00:00 11:23:52 2017-05-02 11:23:52.000 0.00 7.50 003 正常消费 NULL 1
a001002004001 88109976 高爱萍 2017-05-03 00:00:00 10:23:50 2017-05-03 10:23:50.000 0.00 8.00 012 正常消费 NULL 0