SQL Group By和Update表别名 查找数据冲突
来源:互联网 发布:索尼手机拍照软件 编辑:程序博客网 时间:2024/05/16 14:54
做了一个系统,开始的时候设计的有问题。
司机表中有ID ,工号等字段,但是工号原则上不能重复,ID是主键。
有一次发现有问题,可能是工号重复了,这可能是当初手动插入数据的时候弄得。
现在要找到重复的那个数据。
下面的语句即可
select * from (SELECT count(*) as c,gh FROM tb_driver group by gh ) as b where c>1
红色的子查询,是把重复的列的个数也显示出来 字段为c。
注意子查询要重命名表,表明为b
这样哪一行有重复就看到了,手动删掉,再也没问题了。
-----------------------------下面是另外的问题
从一个表中,用某种条件查找到该表的上一条数据,然后把上一条数据的某个字段,复制给当前这一条数据的某个字段。
这需要用表别名。
update
T
set
T.Key1 =
'xxxx'
from
TableA T
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
- SQL Group By和Update表别名 查找数据冲突
- SQL中group by和order by
- sql 中order by 和group by
- sql中group by和order by
- sql语句中where、group by、having、order by 是否可以使用别名
- sql, group by 对数据进行分组
- 数据记录的操作(insert,select,update,delete)函数和分组查询(group by),表连接(join on),嵌套查询(in)
- SQL教程之Group by和Having
- SQL指南-GROUP BY 和 HAVING
- SQL学习之Group by和Having
- SQL学习之Group by和Having
- SQL 语句GROUP BY 和 HAVING
- zz[SQL基础]Group by和Having
- SQL教程之Group by和Having
- SQL学习之Group by和Having
- SQL中子查询和group by查询
- SQL中的Distinct和Group By
- sql group by和having 的用法
- 微服务生态系统的4层模型
- 如何上架一款iOS应用到AppStore
- 在一台服务器上(centOS)配置Redis主从
- 4.2 自定义控件 之 自定义属性与引入布局
- 史上最简单的SpringCloud教程 | 第六篇: 分布式配置中心(Spring Cloud Config)
- SQL Group By和Update表别名 查找数据冲突
- Apache在windows下多站点配置
- 基于MCX514的四轴运动控制器设计方案
- 解决问题:Something's wrong--perhaps a missing \item. \end{thebibliography}
- [BZOJ]2437 [NOI2011] 兔兔与蛋蛋 二分图博弈
- 高清无版权图片网站
- Springboot 实现 Restful 服务,基于 HTTP / JSON 传输
- Maven项目整合SSH框架
- Spring AOP 配置文字简述。(完善中)