常用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
