mysql高级

蠕虫复制：（复制表数据）：插入字段列不包含主键、唯一键，会冲突

不同表复制

INSERT INTO table_a SELECT * FROM table_b;

INSERT INTO table_a(a,b) SELECT a,b FROM table_b where id=1;

同表复制

INSERT INTO table_a SELECT * FROM table_a;

INSERT INTO table_a(a,b) SELECT a,b FROM table_a where id=1;

 

order by ,group by 连用：关联表统计，分组后排序

select * from (
select p.product_id,p.price,p.goods_id,count(i.order_id) as sales from sdb_b2c_products p
left join sdb_b2c_order_items i on i.product_id=p.product_id
where p.goods_id in (22904,22903,21916,21915,21914,22905)
group by p.product_id
)t order by t.sales desc;

 

 

指定顺序排序

select p.product_id,p.name,p.price,p.goods_id from sdb_b2c_products p where p.goods_id in (22904,22903,21916,21915,21914,22905)
order by field(p.goods_id,22904,22903,21916,21915,21914,22905);

 

添加序号

select (@i:=@i+1)pm,p.product_id,p.goods_id from sdb_b2c_products p,(select @i:=0)j where p.goods_id in (22731,22729,22728) ORDER BY FIELD(p.goods_id,22731,22729,22728) limit 100;

 

列转行：将原有字段的值组合成一个（注意：拼接太长会被截取，慎用）

SELECT appid, GROUP_CONCAT(package_name) as channel FROM app_game_package GROUP BY appid;