查询
(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)