实用sql语句

● 删除表中的重复记录

DELETE
FROM
    people
WHERE
    peopleName IN (
        SELECT
            peopleName
        FROM
            people
        GROUP BY
            peopleName
        HAVING
            count(peopleName) > 1
    )
AND peopleId NOT IN (
    SELECT
        min(peopleId)
    FROM
        people
    GROUP BY
        peopleName
    HAVING
        count(peopleName) > 1
)

 ●批量替换某一字段的值

UPDATE tb_sku
SET images = (REPLACE(images, 'image.viuman.com', '39.106.171.57'))

●查可选源频道

select channel_id from provider_channel_mapping group by channel_id having count(provider_id) > 1

 ●mysql update语句自增某字段

UPDATE takeout_order SET num=(num + 1)

 ●判断语句

select ma.compid,
       ma.companyname,
       count(bo.order_no)                                           as orderNum,
       sum(bo.actual_price)                                         as total,
       count(case when bo.status = '3' then bo.order_no else null end) as finishedOrderNum,
       count(case when bo.status != '1' then bo.order_no else null end) as unfinishedOrderNum
from m_auth ma
       join bulk_order bo on ma.compid = bo.seller_id
where ma.companyname like '东道牛酒'
group by ma.compid
order by bo.total_price desc

 ●查询好吃狗用户列表联查最近一次扫码的餐厅

select tu.id, tu.phone, tu.username, tr.name, tu.create_time, tu.vip_over_time, min(tur.scan_time)
from takeout_user tu
       left join takeout_user_rest tur on tu.id = tur.user_id
       left join takeout_rest tr on tur.restid = tr.restid
where tu.valid_status = 1
  and tr.name like ?
  and not exists(
    select 1 from takeout_user_rest tur2 where tur.user_id = tur2.user_id and tur.scan_time > tur2.scan_time)
group by tu.id