query for dup

 avatar
user_0223141589
mysql
2 years ago
597 B
16
Indexable
WITH duplicate as (SELECT 
zpc.LOT_ID
, count(*)
FROM biz.dbt_stg.stg_odoo_prod__zip_product_characteristic zpc
WHERE zpc.name = 'CPU Identification'
GROUP BY 1
HAVING count(*) > 1
)

SELECT 
spl.NAME as serial
, p.PRODUCT_NAME
, zpc.LOT_ID
, zpc.LIFETIME_VALUE
, zpc.CREATE_UID
, zpc.CREATE_DATE
FROM biz.dbt_stg.stg_odoo_prod__zip_product_characteristic zpc
JOIN duplicate d on d.lot_id = zpc.LOT_ID
JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT spl on spl.id = zpc.LOT_ID
JOIN BIZ.DBT_ODOO.PRODUCTS p on spl.PRODUCT_ID = p.PRODUCT_ID
WHERE zpc.name = 'CPU Identification'
order by 1 asc;
Editor is loading...