做了一个系统,开始的时候设计的有问题。
司机表中有ID ,工号等字段,但是工号原则上不能重复,ID是主键。
有一次发现有问题,可能是工号重复了,这可能是当初手动插入数据的时候弄得。
现在要找到重复的那个数据。
下面的语句即可
select * from (SELECT count(*) as c,gh FROM tb_driver group by gh ) as b where c>1
红色的子查询,是把重复的列的个数也显示出来 字段为c。
注意子查询要重命名表,表明为b
这样哪一行有重复就看到了,手动删掉,再也没问题了。
-----------------------------下面是另外的问题
从一个表中,用某种条件查找到该表的上一条数据,然后把上一条数据的某个字段,复制给当前这一条数据的某个字段。
这需要用表别名。
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