常用sql Demo

常用sql demo

1. 删除一个表中重复脏数据

DELETE FROM test

WHERE 1=1

    AND `name` in (

        SELECT * FROM ( (SELECT `name` `names`

                                         FROM test GROUP BY name HAVING COUNT(`name`)>1) ) a

   )

     AND id not in (

          SELECT * FROM ( (SELECT MIN(id) ids FROM test GROUP BY NAME HAVING COUNT(`name`)>1) ) b

)

2.统计常用数据

统计结果

SELECT name,

      SUM(CASE WHEN sb.sblb = '1' THEN 1 ELSE 0 END) AS 待验证,

      SUM(CASE WHEN sb.sblb = '2' THEN 1 ELSE 0 END) AS 通过,

      SUM(CASE WHEN sb.sblb = '3' THEN 1 ELSE 0 END) AS 失效FROM SBMP_SBXX sbJOIN FR_ZTJCXX ztjc ON    ztjc.qylx is NOT NULL AND ztjc.qyzt = '1' AND ztjc.ssgq IS NOT NULL AND ztjc.ID = sb.ztidWHERE sb.rdsj = 'XXX'

3.mysql 表备份

create table xxx_bak select * from tablea

4.表新增字段

ALTER TABLE sp_sales_order_item ADD sale_type VARCHAR(4) COMMENT 'F-成品 P-样机';

5.关联多张表，（字典表，多条数据用，分隔展示）

SELECT si.sales_region '大区',

CASE WHEN si.sales_region = 'DONGBEI' THEN '东北'

WHEN si.sales_region = 'HUABEI' THEN '华北一'

WHEN si.sales_region = 'HUANAN' THEN '华南'

WHEN si.sales_region = 'HUDONGYI' THEN '华东一'

WHEN si.sales_region = 'HUDONGER' THEN '华东二'

WHEN si.sales_region = 'LUYU' THEN '华北二'

WHEN si.sales_region = 'XIBEI' THEN '西北'

WHEN si.sales_region = 'XINAN' THEN '西南'

WHEN si.sales_region = 'CHINA' THEN '全国'

WHEN si.sales_region = 'ALLNET' THEN '全网'

WHEN si.sales_region = 'SUZHOU' THEN '总部测试'

WHEN si.sales_region = 'NHUABEI' THEN '华北'

WHEN si.sales_region = 'HUAZHONG' THEN '华中' ELSE '' END '大区DESC',

si.store_no '门店编码', si.name '门店名称', si.address '地址', si.type '门店性质', si.grade '门店等级', si.is_fight '作战地图', si.is_near_dyson '有无戴森',  a1.area_name '省', a2.area_name '市', a3.area_name '县', si.exhibition_location '位置', sale.pay_amount '销售金额', ywjlzg.zg '业务经理及主管', ywdd.dd '业务督导', dgy.dg '导购', dgsl.dg '导购数量'FROM `platform-store`.sp_store_info si LEFT JOIN `platform-store`.sp_store_workspace sw ON sw.status = 'ENABLED' AND si.workspace_id = sw.idLEFT JOIN `platform-admin`.sys_area a1 ON a1.status = 'ENABLED' AND a1.area_id = sw.province_idLEFT JOIN `platform-admin`.sys_area a2 ON a2.status = 'ENABLED' AND a2.area_id = sw.city_idLEFT JOIN `platform-admin`.sys_area a3 ON a3.status = 'ENABLED' AND a3.area_id = sw.district_idLEFT JOIN (SELECT si.store_no, sum(so.pay_amount) pay_amount

FROM `platform-store`.sp_sales_order so

JOIN `platform-store`.sp_store_info si ON si.id = so.store_id AND si.delete_flag = '0' WHERE so.status = 'FINISHED' AND FROM_UNIXTIME(so.order_time/1000, '%Y-%m') >= DATE_FORMAT(DATE_SUB(now() ,INTERVAL 6 MONTH), '%Y-%m')

AND FROM_UNIXTIME(so.order_time/1000, '%Y-%m') <= DATE_FORMAT(DATE_SUB(now() ,INTERVAL 1 MONTH), '%Y-%m')

AND ISNULL(so.refund_status)

GROUP BY so.store_id) sale ON sale.store_no = si.store_noLEFT JOIN (SELECT bssp.store_id, GROUP_CONCAT(bs.name SEPARATOR ',') zg FROM `platform-store`.sp_biz_staff_store_permission bssp

JOIN `platform-store`.sp_biz_staff bs ON bs.id = bssp.staff_id AND bs.staff_status = 'ENABLED' AND (bs.position = 'BIZ_MANAGER' OR bs.position = 'BIZ_CHARGE')

GROUP BY bssp.store_id) ywjlzg ON si.id = ywjlzg.store_idLEFT JOIN (SELECT bssp.store_id, GROUP_CONCAT(bs.name SEPARATOR ',') dd FROM `platform-store`.sp_biz_staff_store_permission bssp

JOIN `platform-store`.sp_biz_staff bs ON bs.id = bssp.staff_id AND bs.staff_status = 'ENABLED' AND bs.position = 'BIZ_SUPERVISOR' GROUP BY bssp.store_id) ywdd ON si.id = ywdd.store_idLEFT JOIN (SELECT cs.store_id, GROUP_CONCAT(ci.name SEPARATOR ',') dg FROM `platform-store`.sp_clerk_info ci

JOIN `platform-store`.sp_clerk_store cs ON ci.id = cs.clerk_id

WHERE ci.delete_flag = 0 AND ci.position = 'SHOP_GUIDE' GROUP BY cs.store_id) dgy ON si.id = dgy.store_idLEFT JOIN (SELECT cs.store_id, count(ci.name) dg FROM `platform-store`.sp_clerk_info ci

JOIN `platform-store`.sp_clerk_store cs ON ci.id = cs.clerk_id

WHERE ci.delete_flag = 0 AND ci.position = 'SHOP_GUIDE' GROUP BY cs.store_id) dgsl ON si.id = dgsl.store_id WHERE si.delete_flag = '0'

查询结果

6. A 表数据插入到B表

INSERT INTO `platform-store`.`sp_store_company_change_log`(

`store_no`,

`company_no`,

`company_name`,

`change_time`,

`status`,

`creater`,

`create_time`,

`modifier`,

`modify_time`,

`version`) SELECT DISTINCT

si.store_no,

si.company_no,

c.name company_name,

si.create_time,

'1',

'admin',

UNIX_TIMESTAMP(now())*1000,

'admin',

UNIX_TIMESTAMP(now())*1000,

0FROM sp_store_info si LEFT JOIN sp_company c ON si.company_no = c.sap_company_no and c.delete_flag = 0WHERE si.delete_flag = 0

7. 查询某个类型前几笔数据

SELECT

    p.*FROM    gift AS p,

    (SELECT

        GROUP_CONCAT(id order by created_time asc) AS ids

    FROM        gift

WHERE deleted = 0    GROUP BY type) AS bWHERE    FIND_IN_SET(p.id, b.ids) BETWEEN 1 AND 3;

8. mysql逗号分隔转成多行数据

笛卡尔积实现：

select a.ID,substring_index(substring_index(a.loginName,',',b.help_topic_id+1),',',-1)

from

account a

join

mysql.help_topic  b

on b.help_topic_id < (length(a.loginName)- length(replace(a.loginName,',',''))+1)

order by a.ID;

8. mysql分组内某个字段修改

update sp_customer_desire_info_log t,(

              SELECT a.*, count(1) AS rankFROM sp_customer_desire_info_log aLEFT JOIN sp_customer_desire_info_log b ON a.customer_id = b.customer_idAND a.edit_time >= b.edit_timeGROUP BY a.customer_id, a.edit_timeORDER BY a.customer_id,

a.edit_time desc)t1

set t.times=t1.rank

where  t.id=t1.id