mysql

mysql版本：8.0.22

查询

举例：

1. 查询表中某个字段出现次数大于1的记录数

/*查询表中某个字段出现次数大于1的记录数*/
select us.user_id,count(*)  from user(表名) us(别名) GROUP BY user_id HAVING count(*)>1

2.主从表关联，主表user 从表order 关联 user_id

	
/*显示从表数量*/
SELECT
	user_id,
	order_quantity 
FROM
	order pow 
WHERE
	user_id= 339
/*从表对user_id字段的数量做统计*/
SELECT
	sum( order_quantity ) 
FROM
	order pow 
WHERE
	user_id= 339 
GROUP BY
	user_id
/*主表对从表的数量做统计，并显示在主表上  子查询 0.5s*/
SELECT po.*,( SELECT sum( order_quantity ) FROM order pow WHERE po.user_id= pow.user_id GROUP BY pow.user_id) AS order_quantitys 
FROM
	user po 
WHERE
	po.order_number = "10630162"
/*左关联  0.039*/
SELECT po.* ,sum(pow.order_quantity )
FROM
	user po 
	LEFT JOIN order pow on po.user_id= pow.user_id
WHERE
	po.order_number = "10630162"
GROUP BY po.user_id

/*全关联 0.04*/
SELECT po.* ,sum(pow.order_quantity )
FROM
	user po, 
	order pow  
WHERE
  po.user_id= pow.user_id 
	and
	po.order_number = "10630162"
GROUP BY po.user_id
/*左关联 0.034*/
SELECT
	po.*,
	pow.order_quantitys 
FROM
	user po
LEFT JOIN (SELECT
	sum( order_quantity ) order_quantitys ,pow.user_id
FROM
	order pow 
GROUP BY
	pow.user_id
	) pow ON po.user_id = pow.user_id
WHERE
	po.order_number = "10630162"