例如下面的 根据 self_wechatid 进行group by ,统计出 性别总数,男总数(gender = 1为男),女总数(gender = 2为男),不详总数。
SELECT
self_wechatid,
count(*) AS total,
sum(IF(gender = 1, 1, 0)) AS man,
sum(IF(gender = 2, 1, 0)) AS girl,
(
count(*) - sum(IF(gender = 1, 1, 0)) - sum(IF(gender = 2, 1, 0))
) AS non
FROM
al_wx_contact
WHERE
create_time > 1526730791
GROUP BY
self_wechatid;
insert into 表1(字段1,字段2,字段3) select 字段1,字段2,字段3 from 表1 where id=1
update sumholdtime s,province b set s.province = b.province where s.area_code=b.area_code;
update t_tc_telephone s,t_mobile_number_section b set s.province=b.province,s.city=b.city where s.area_code = b.area_code and s.area_code != '';
update t_non_tc_telephone s,t_mobile_number_section b set s.province=b.province,s.city=b.city where s.area_code = b.area_code and s.area_code != '';
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id
或
DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id
DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL
或
DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL
DELETE t1,t2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25
注意此处的delete t1,t2 from 中的t1,t2不能是别名 如:
delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25
在数据里面执行是错误的(MYSQL 版本不小于5.0在5.0中是可以的)
后续持续更新中