1. 透云智购 和 透云智售项目。删除表中重复的数据 - 2017-09-23 周六,中软国际高科技业务群-零售电商事业部
删除商品表中商品69码重复的数据,只剩id小的一条:
DELETE
FROM commodity
WHERE
id IN (
SELECT e.id FROM
( SELECT a.id FROM commodity a,
( SELECT MIN(id) id, b.code69 FROM commodity b GROUP BY b.code69 ) c
WHERE
a.code69 = c.code69
AND a.id != c.id
) e
);
COMMIT;
2. 获取用户表中用户的所属一级地区,用户表中绑定的是注册时候最底层的行政地区。
SELECT
region_id
FROM
(
SELECT
@r AS _id,
( SELECT @r := PARENT_ID FROM b_region WHERE region_id = _id ) AS PARENT_ID,
@l := @l + 1 AS lvl
FROM
( SELECT @r := #{regionId}, @l := '0' ) vars,
b_region h
WHERE
@r <> '0'
) T1
LEFT JOIN b_region T2 ON T1._id = T2.region_id
WHERE
T2.`PARENT_ID` = 0
ORDER BY
T1.lvl DESC;
3. 新零售fnb餐饮。 去除门店数据有重复的数据。要求同一个门店id只有一条数据
DELETE
FROM
shop_package
WHERE
id IN (
SELECT
t.id
FROM
( SELECT id FROM shop_package r GROUP BY r.store_id HAVING COUNT(id) > 1 ) AS t)