2022-10-20 09:41:22
问题现象】
联查销货单提示8156数据库错误，请重试
【解决方案】
---执行前先备份账套，执行后重启服务
---删除重复的用户栏目方案
DELETE FROM Eap_ColumnSet_User
WHERE id IN (SELECT r.eid
FROM (SELECT Row_number()
OVER(
PARTITION BY eSolutionID, eUserId, eField, eIdSearchPlan
ORDER BY eSolutionID, eUserId, eField, eIdSearchPlan) AS rankid,
*
FROM (SELECT e.SolutionID AS eSolutionID,
e.UserId AS eUserId,
e.Field AS efield,
e.IdSearchPlan AS eIdSearchPlan,
id AS eid
FROM Eap_ColumnSet_User e
INNER JOIN (SELECT *
FROM (SELECT SolutionID,
UserId,
Field,
IdSearchPlan,
Count(*) AS y
FROM Eap_ColumnSet_User
GROUP BY SolutionID,
UserId,
Field,
IdSearchPlan)a
WHERE a.y > 1)b
ON b.SolutionID = e.SolutionID
AND b.UserId = e.UserId
AND b.Field = e.Field
AND b.IdSearchPlan = e.IdSearchPlan)h)r
WHERE r.rankid > 1)
---删除重复栏目方案
DELETE FROM eap_ColumnSetSolution_User
WHERE id IN (SELECT r.eid
FROM (SELECT Row_number()
OVER(
PARTITION BY eSolutionID, eUserId, eIdSearchPlan
ORDER BY eSolutionID, eUserId, eIdSearchPlan) AS rankid,
*
FROM (SELECT e.SolutionID AS eSolutionID,
e.UserId AS eUserId,
e.IdSearchPlan AS eIdSearchPlan,
id AS eid
FROM eap_ColumnSetSolution_User e
INNER JOIN (SELECT *
FROM (SELECT SolutionID,
UserId,
IdSearchPlan,
Count(*) AS y
FROM eap_ColumnSetSolution_User
GROUP BY SolutionID,
UserId,
IdSearchPlan)a
WHERE a.y > 1)b
ON b.SolutionID = e.SolutionID
AND b.UserId = e.UserId
AND b.IdSearchPlan = e.IdSearchPlan)h)r
WHERE r.rankid > 1)
