欢迎使用CSDN-markdown编辑器

奇葩BUG–ID唯一性没约束

发现:页面显示相同的两条记录(2组4条),
过程:确认数据库没有相同2条记录,查找sql语句的错误,
语句如下:

SELECT
*
FROM
(
SELECT
row_.*, ROWNUM rownum_
FROM
(
SELECT
om. ID,
c.cph CPH,
b.mc BXGSMC,
B. ID BXGSID,
om.createdate,
r.xm ywyname,
T .tdmc tdmc,
z.bxdlgsmc BDGSMC,
ob.JQXBDH,
ob.SYXBDH,
OB.JQX,
OB.CCS,
OB.SYXBF,
successywy.xm sname,
failywy.xm fname,
issuedywy.xm iname
FROM
order_bxgsid ob
RIGHT JOIN order_main om ON ob.orderid = om. ID
LEFT JOIN car_info c ON c. ID = om.clid
LEFT JOIN ryb r ON r. ID = om.ywyid
LEFT JOIN ryb successywy ON successywy. ID = ob.successywyid
LEFT JOIN ryb failywy ON failywy. ID = ob.failywyid
LEFT JOIN ryb issuedywy ON issuedywy. ID = ob.issuedywyid
LEFT JOIN zzjg z ON z. ID = r.bxdlgsbh
LEFT JOIN td T ON T . ID = r.tdbh
LEFT JOIN bxgs b ON b. ID = ob.bxgsid
WHERE
1 = 1
AND r.bxdlgsbh = 1
AND ob.handlestatus = ‘0’
ORDER BY
om.createdate
) row_
WHERE
ROWNUM <= 100
) table_alias
WHERE
table_alias.rownum_ >= 1

发展:以为是right join导致的错误,倒腾半天无果,然后开始删除附表的left join,发现删除后显示正常,遂认为某个left join 有问题, 然后逐条删除,发现错误发生在 car_info表,(LEFT JOIN car_info c ON c. ID = om.clid),苦思不得解;

结局:最后实在没招,看了下该主表的clid(om.clid),手动到car_info表里查询,发现 car_info 中 id = 1 的记录居然有两条删除一条 问题解决