实用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
)

连接查询简化版

DELETE p1
FROM
    Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
    AND p1.Id > p2.Id

●查询某字段重复记录

select account_name
from electric_prestore_collect
group by account_name
having count(account_name) > 1

●将sex字段反转

UPDATE employee
SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );

●查找id为奇数的电影

SELECT
    *
FROM
    cinema
WHERE
    id % 2 = 1;

●查找有五名及以上student的class

SELECT
    class
FROM
    courses
GROUP BY
    class
HAVING
    count( DISTINCT student ) >= 5;

●查找薪资大于其经理薪资的员工信息

SELECT
    E1.* FROM
    Employee E1
    INNER JOIN Employee E2
    ON E1.ManagerId = E2.Id
    WHERE E1.Salary > E2.Salary;

●查找没有订单的顾客信息

SELECT
    C.Name
FROM
    Customers C
    LEFT JOIN Orders O
    ON C.Id = O.CustomerId
WHERE
    O.CustomerId IS NULL;

●查找一个 Department 中收入最高者的信息

SELECT
    D.NAME Department,
    E.NAME Employee,
    E.Salary
FROM
    Employee E,
    Department D,
    ( SELECT DepartmentId, MAX( Salary ) Salary 
     FROM Employee 
     GROUP BY DepartmentId ) M
WHERE
    E.DepartmentId = D.Id
    AND E.DepartmentId = M.DepartmentId
    AND E.Salary = M.Salary;

●查找工资第二高的员工

SELECT DISTINCT Salary 
FROM Employee 
ORDER BY Salary DESC 
LIMIT 1, 1

●查找连续出现三次的数字

SELECT
    DISTINCT L1.num
FROM
    Logs L1,
    Logs L2,
    Logs L3
WHERE L1.id = l2.id - 1
    AND L2.id = L3.id - 1
    AND L1.num = L2.num
    AND l2.num = l3.num;

●将得分排序，并统计排名

SELECT
    S1.score 'Score',
    COUNT( DISTINCT S2.score ) 'Rank'
FROM
    Scores S1
    INNER JOIN Scores S2
    ON S1.score <= S2.score
GROUP BY
    S1.id, S1.score
ORDER BY
    S1.score DESC;

●批量替换某一字段的值

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 max(enterprise_id)                                         as enterprise_id,
       account_id,
       count(distinct (case when invoice_status = 0 then id end)) as not_invoice_num,
       sum(case when invoice_status = 0 then bill_amount end)     as not_invoice_amount,
       count(distinct (case when invoice_status = 1 then id end)) as invoicing_num,
       sum(case when invoice_status = 1 then bill_amount end)     as invoicing_amount,
       count(distinct (case when invoice_status = 2 then id end)) as invoiced_num,
       sum(case when invoice_status = 2 then bill_amount end)     as invoiced_amount,
       count(distinct (case when red_flush = true then id end))   as red_flush_num,
       sum(case when red_flush = true then bill_amount end)       as red_flush_amount
from property_platform_bill
where deleted = false
  and account_id > :accountId
  and date_created >= :start
  and date_created < :end
group by account_id
order by enterprise_id 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