日常记录mysql语句神奇用法

1 SUBSTRING_INDEX
SELECT t1.name,t1.seo_description as seo,tt.price,tt.id,SUBSTRING_INDEX(t2.pc_url,’,’,1) AS url FROM product t1 ,product_pic t2,

分隔字符串并拿到下标索引0的数据
注意:SUBSTRING_INDEX(t2.pc_url,’,’,count),当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。从这里复制的函数，两个单引号可能是中文的单引号需要重新输入。

2SELECT store_name as storeName,store.adcode,store.id as storeId,store.logo_url as storeLogoUrl,store.address as address,store_product_info.product_id, store_product_info.price as price,adcode,seller_id as sellerId,store_product_info.cate_id, ROUND( 6378.138 * 2 * ASIN( SQRT( POW( SIN( ( #{lon} * PI() / 180 - lat * PI() / 180 ) / 2 ), 2 ) + COS( #{lon} * PI() / 180) * COS(lat * PI() / 180) * POW( SIN( ( #{lat} * PI() / 180 - lon * PI() / 180 ) / 2 ), 2 ) ) )*1000 ) AS distince FROM store
通过经纬度计算距离
3mysql对结果集进行去重

SELECT DISTINCT tt.*  FROM(
SELECT  s.id ,s.`store_name` AS storeName,c.is_pay,s.sales_volume AS salesVolume,s.adcode ,IFNULL(s.service,'')AS service,IFNULL(b.num,0) AS storeProductCount,(CASE WHEN c.`qiye_passport` = "" THEN FALSE WHEN c.`qiye_passport` != "" THEN TRUE END)AS qiyePassport,SUBSTRING_INDEX(s.address,' ',-1)AS address,s.logo_url
FROM store  AS s 
JOIN company AS c 
ON s.seller_company_id = c.id 
LEFT JOIN (SELECT  store_id,COUNT(*) AS num FROM  store_product_case  GROUP BY store_id )b
ON  s.id=b.store_id
RIGHT JOIN 
store_product_info AS sp
ON 
s.id = sp.store_id AND sp.product_id IN (68,69,70,71,72) AND price != 0 AND publish_flag = TRUE 
WHERE adcode IN (SELECT adcode FROM AREA  WHERE citycode = (SELECT citycode FROM AREA WHERE  NAME LIKE "%南京市%" LIMIT 1))
ORDER BY 
c.money DESC,
c.create_date DESC

)tt 

3删除重复行（保留一行）

delete from table_name as ta where ta.唯一键 <> ( select max(tb.唯一键) from table_name as tb where ta.判断重复的列 = tb.判断重复的列);

原文:https://blog.csdn.net/ljy520yzy/article/details/8631264

4替换字符串
UPDATE table_name SET field_name = replace (field_name,‘from_str’,‘to_str’) WHERE field_name LIKE ‘%from_str%’