sql精妙语句整理2

Mysql 数据库根据一张表内容更新另一张表

 

UPDATE `goods`,(SELECTa.goods_id,a.`barcode` FROM goods_unit a WHERE a.`level`='2') b SETgoods.`code`=b.barcode WHERE goods.`id`=b.goods_id

 

查询重复记录

SELECT aa.stype_detailid FROM(SELECTstype_detailid,COUNT(*) AS COUNT FROM storage_goods_change WHERE`enterprise_id`='6904b67c1024464d86837196e2d9dd43' AND `stype`='5' GROUP BYstype_detailid HAVING COUNT>1)aa

批量插入：

字段分隔逗号转列

 

SELECT a.`cid`lineid,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`line`,',',b.help_topic_id+1),',',-1)csid

FROM

(SELECT * FROM customer_visit_lineWHERE enterprise_id='ed51c15a9f4143c0a19150465c69530a' ANDemployeeid='4a18ecbcdd034adcacd8a80c413e90be')a

JOIN

mysql.help_topic b

ON b.help_topic_id < (LENGTH(a.line) -LENGTH(REPLACE(a.line,',',''))+1)

ORDER BY a.cid;

 

mysql如何更新一个表中的某个字段值等于另一个表的某个字段值

 

update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;

 

查询当天记录

SELECT * FROM `customer_visit_emp` a WHEREDATE(a.`createtime`)=DATE(NOW())；

 

关于大于小于

<![CDATA[

              and es.intotime<=#{edate}

         ]]>