SQL Group By和Update表别名 查找数据冲突

做了一个系统，开始的时候设计的有问题。

司机表中有ID ，工号等字段，但是工号原则上不能重复，ID是主键。

有一次发现有问题，可能是工号重复了，这可能是当初手动插入数据的时候弄得。

现在要找到重复的那个数据。

下面的语句即可

 
select *  from   (SELECT count(*) as c,gh FROM tb_driver group by gh ) as b    where c>1

红色的子查询，是把重复的列的个数也显示出来 字段为c。

注意子查询要重命名表，表明为b

这样哪一行有重复就看到了，手动删掉，再也没问题了。

-----------------------------下面是另外的问题

从一个表中，用某种条件查找到该表的上一条数据，然后把上一条数据的某个字段，复制给当前这一条数据的某个字段。

这需要用表别名。

updateT

setT.Key1 = 'xxxx'

fromTableA T

这是Update 的另外写法。

UPDATE  tar_tb set [previousClass] = 
   (
   select  top 1 toClass from tb_driverReportInput s where s.train=tar_tb.train and  s.toDateTime<tar_tb.fromDateTime
   order by  fromDateTime desc
   )
   from [tb_driverReportInput] tar_tb

UPDATE  tar_tb set Downlong = 
   (
    case when  tar_tb.fromClass='乘务员' then 
          datediff(MINUTE, (select  top 1 toDateTime from tb_driverReportInput s where s.train=tar_tb.train and  s.toDateTime<=tar_tb.fromDateTime order by  fromDateTime desc),tar_tb.fromDateTime)
  else 0
end
   )
   from [tb_driverReportInput] tar_tb

下面语句的功能是把某条交接班的实际耗油量复制给他的 明细表中最后一条的记录------------------------------------------------------------------------------------- 

update T
set T.actualOil = 
(select case  when ((SELECT max(startTime) FROM [hnoil].[dbo].[tb_driverReportDetails] where reportid=Zh)=T.startTime)
 then (select actualOil from tb_driverReportInput  where id=Zh)
 else 0 
 end 
 )
 from 
 (select startTime,[tb_driverReportDetails].actualOil,reportid as Zh  from [tb_driverReportDetails] left join  tb_driverReportInput on [tb_driverReportDetails].reportid=tb_driverReportInput.id) T
 注意：上面的Zh是必须的。

-----------------下面的语句是 查询和 case 语句连用------------------------------

select *,
 case when emptyWeight=0 and heavyWeight=0 
 then 0 
 else  0.5*(dcCount+dxCount+dzCount)+mileageValue  
 end as statisticsValue
  from 
 (
 select [tb_driverReportDetails].*,
 str(shuntingdz)+','+str(dzCount) as dzMinutesCount,
 str(shuntingdx)+','+str(dxCount) as dxMinutesCount,
 str(shuntingdc)+','+str(dcCount) as dcMinutesCount, 
 
 (select mineStationName from tb_mineStation where id=tb_stationMileage.startStationId) +'-->' +
 (select mineStationName from tb_mineStation where id=tb_stationMileage.endStationId) as mileageStation,
 (select benwuMileage from tb_stationMileage where  [mileageStationId]=tb_stationMileage.id) as distance,
 (select score from tb_stationMileage where  [mileageStationId]=tb_stationMileage.id) as mileageValue,
 tb_driverReportInput.train as trainNum , 
 tb_driverReportInput.submitDate,
(tb_driverReportDetails.actualOil-tb_driverReportDetails.comprehensiveOil) as saveOil,
  (select gh+','+drivername from tb_driver where tb_driver.gh=tb_driverReportInput.driverGh) as drivername,
  (select gh+','+drivername from tb_driver where tb_driver.gh=tb_driverReportInput.assistantDriverGh) as assistantDrivername
  FROM 
 [hnoil].[dbo].[tb_driverReportDetails] left join tb_stationMileage on mileageStationId= tb_stationMileage.id
 left join tb_driverReportInput on tb_driverReportInput.id=[tb_driverReportDetails].reportid
 ) as bb