把这段sql改为根据bb.dutytype和bb.dutyno去重select concat(aa.qcbno,bb.seq) as '序号','制程检验' as 'qcsrc', aa.qcbdate as '质检日期',aa.qcbno as '质检单号',bb.seq as '质检单序号',bb.stats as '质检单状态',aa.qcmethod as '质检方式', bb.godcode as '良品码',bb.qcqty as '判定数',bb.resno as '不合格原因',bb.dutytype as '责任类型',bb.dutyno as '责任单位',bb.remark as '不合格描述', dd.loseamt as '损失金额',dd.losestats as '损失确认状态', bb.repairmanno as '返修工单号', aa.prono as '产地', dd.keyindate as '申请时间',dd.keyinuserno as '申请人' from tqmbilprossh aa left join tqmbilprossd bb on aa.facno=bb.facno and aa.qcbno=bb.qcbno left join tqmbildngamt dd on aa.facno=dd.facno and aa.qcbno=dd.qcbno and bb.seq=dd.seq where aa.facno='A' and godcode in('B','F') and aa.prono in('1','C','Q','Y','Z') and bb.godcode in('F','B') and bb.stats in('C','T','Y','L') and dd.losestats = 'N'
时间: 2023-10-01 11:09:59 浏览: 45
以下是根据 bb.dutytype 和 bb.dutyno 去重的 SQL 语句:
SELECT
CONCAT(aa.qcbno, bb.seq) AS '序号',
'制程检验' AS 'qcsrc',
aa.qcbdate AS '质检日期',
aa.qcbno AS '质检单号',
bb.seq AS '质检单序号',
bb.stats AS '质检单状态',
aa.qcmethod AS '质检方式',
bb.godcode AS '良品码',
bb.qcqty AS '判定数',
bb.resno AS '不合格原因',
bb.dutytype AS '责任类型',
bb.dutyno AS '责任单位',
bb.remark AS '不合格描述',
dd.loseamt AS '损失金额',
dd.losestats AS '损失确认状态',
bb.repairmanno AS '返修工单号',
aa.prono AS '产地',
dd.keyindate AS '申请时间',
dd.keyinuserno AS '申请人'
FROM
(
SELECT
dutytype,
dutyno,
MIN(seq) AS min_seq
FROM
tqmbilprossd
GROUP BY
dutytype,
dutyno
) AS bb_distinct
INNER JOIN tqmbilprossd bb ON bb_distinct.dutytype = bb.dutytype AND bb_distinct.dutyno = bb.dutyno AND bb_distinct.min_seq = bb.seq
LEFT JOIN tqmbilprossh aa ON aa.facno = bb.facno AND aa.qcbno = bb.qcbno
LEFT JOIN tqmbildngamt dd ON aa.facno = dd.facno AND aa.qcbno = dd.qcbno AND bb.seq = dd.seq
WHERE
aa.facno = 'A' AND
aa.prono IN ('1', 'C', 'Q', 'Y', 'Z') AND
bb.godcode IN ('F', 'B') AND
bb.stats IN ('C', 'T', 'Y', 'L') AND
dd.losestats = 'N'
ORDER BY
bb.dutytype,
bb.dutyno
主要修改如下:
- 在 SELECT 中加入 CONCAT(aa.qcbno, bb.seq) AS '序号',用于生成序号;
- 在 SELECT 中保留 '责任类型' 和 '责任单位' 两列;
- 在 FROM 子句中添加一个子查询,用于根据 bb.dutytype 和 bb.dutyno 去重;
- 在子查询中使用 MIN(seq) 获取最小的序号;
- 在子查询的结果中使用 INNER JOIN 连接 tqmbilprossd 表,以获取完整的质检单信息;
- 调整 WHERE 子句中条件的顺序,按照常规排列。