MySQL 查询语句（不定时更新）

查询

（1）分组时将某个字段的值进行拼接

-- 将学生按班级分组，对同一班级的学生姓名进行拼接
SELECT classid,group_concat(stu_name) as stu_names FROM student GROUP BY classid

-- 将客户共享表crm_share_join中负责人beshare_uid 以cid分组并用逗号拼接起来，查询 new_charege 字段以 (创建人uid,负责人A,负责人B,负责人C...,) 形式展示
SELECT
	s.cid,
	s.beshare_uid,
	s.share_uid,
	c.creatid,
	c.id,
	c.charge_person,
	CONCAT(c.creatid,',',group_concat(s.beshare_uid),',') as new_charege
FROM
	crm_share_join s,
	crm_customer c
WHERE
	s.cid = c.id
AND s.creat_id <> s.beshare_uid
GROUP BY
	s.cid
HAVING
new_charege<> c.charge_person

 （2）连表查询在另一张表某个字段的数量以及和

-- 查询教师列表以及教师学生总数以及学生总成绩和
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id 

-- 查询教师的学生总成绩大于500的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING score_total > 500

-- 查询教师学生总数大于10 的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING stu_num > 10

-- 查询教师的学生成绩在200到500之间，学生总数在10到20区间的
SELECT t.*,COUNT(s.id) as stu_num,SUM(s.score) as score_total FROM teacher t LEFT JOIN student s ON s.id = t.teacher_id WHERE ... GROUP BY t.id HAVING (score_total BETWEEN 200 AND 500 ) AND (stu_num BETWEEN 10 AND 20)

 （3）查询字段中包含两个"," 的数据

-- 查询客户负责人大于等于2的
SELECT c.id,c.name FROM customer WHERE ( LENGTH(charge_person) - LENGTH(REPLACE(charge_person, ',', '')) ) >= 2

（4）分组统计并列出百分比

-- 按用户的部门进行分组，并查询部门人员占总人员的百分比
SELECT
	group_id,
	num,
	concat( round(num / total * 100.00, 2),'%' ) percent
FROM
	(
	SELECT
		* 
	FROM
		( SELECT group_id, count(1) num FROM crm_user GROUP BY group_id ) t1
		INNER JOIN ( SELECT count(1) total FROM crm_user ) t2 ON 1 = 1 
	) t

（5）将两个字段拼接

-- 将id和name拼接为一个字段
SELECT CONCAT(id,'-',name) as title FROM crm_test 

（6）两表字段比较，不同的查出来

-- 查询共享客户表中创建人和客户创建人不同的数据(我这里需要查的字段比较多，所以需要多表联查)
SELECT
	sc.creatid,
	sc.charge_person,
	s.id AS sid,
	s.beshare_uid,
	c.id AS cid,
	c.cname,
	c.charge_person,
	c.creatid AS newcreatid
FROM
	crm_share_customer sc,
	crm_share_join s,
	crm_customer c,
	crm_customer_data cd
WHERE
	sc.id = s.bid
AND s.cid = c.id
AND c.id = cd.cid
AND sc.creatid <> c.creatid

（7）同表不同字段查重

-- 查询customer_data表中手机号1与手机号2重复的数据
SELECT
	u2.cid,
	u2.cphonetwo
FROM
	crm_customer_data u1,
	crm_customer_data u2	
WHERE
	u1.id <> u2.id
    AND  (u1.cphone = u2.cphonetwo)

（8）查询一周、一月内数据

-- 字段类型为date  如时间字段存储格式为时间戳，需如下处理:from_unixtime(`时间字段`)

-- 查询当天数据
SELECT cid,cname FROM crm_customer WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
-- 查询近一周的数据
SELECT cid,cname FROM crm_customer WHERE DATE_SUB(CURDATE(),INTERVAL 1 WEEK) <= DATE(create_time)
-- 查询近一月的数据
SELECT cid,cname FROM crm_customer WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(create_time)