1，字符串截取拼接
CONCAT(LEFT(c.id_card,LENGTH(c.id_card)-4),'****');
SUBSTRING_INDEX(c.context,'}',1);
SUBSTRING_INDEX(a.task_context,':',-1) as context;
c.mobile LIKE CONCAT('%', '${mobile}', '%')
2，Case函数
( CASE a.type_code WHEN 'bp' THEN '血压' WHEN 'hr' THEN '心率' WHEN 'fbg' THEN '空腹血糖' WHEN '2hpbg' THEN '餐后两小时血糖' WHEN 'tc' THEN '总胆固醇' WHEN 'bua' THEN '血尿酸' WHEN 'bmi' THEN '体质指数' ELSE '其他' END ) AS type_code
3，字符串拼接
CONCAT_WS('/',b.value1,b.value2,b.value3);
CONCAT(a.value1,'mmol/L') AS value1
4，日期加减
DATEDIFF(DATE(MAX(take_time)),DATE(MIN(take_time))) as days
5，年龄计算
YEAR (NOW()) - YEAR (b.birthday) AS birthday
6，IF函数
IF(b.gender=0,'女','男') AS gender
7，COUNT函数
count(DISTINCT(a.called_user_id) & a.begin_calltime>0)
SELECT a.user_id, a.realname, (SELECT COUNT(*) from ut_pic b where b.user_id=a.user_id) as picTotal, (SELECT COUNT(*) from ut_pic c where c.user_id=a.user_id and is_show=1) as pic, (SELECT COUNT(*) from ut_video d where d.user_id=a.user_id) as videoTotal, (SELECT COUNT(*) from ut_video e where e.user_id=a.user_id and is_show=1) as video FROM ut_user a
with ta as ( select nvl(v.update_time,v.create_time) sj,v.housekeeper_id hid from t_crd_video v union all select nvl(a.update_time,a.create_time) sj,a.housekeeper_id hid from t_crd_album a ) select h.id ID,h.real_name realName, (select count(1) from t_crd_album t where t.vaild=1 and t.housekeeper_id=h.id and t.is_show=1) picShow, (select count(1) from t_crd_album t where t.vaild=1 and t.housekeeper_id=h.id) picTotal, (select count(1) from t_crd_video v where v.vaild=1 and v.housekeeper_id=h.id and v.is_show=1 ) vidShow, (select count(1) from t_crd_video v where v.vaild=1 and v.housekeeper_id=h.id ) vidTotal, to_char((select max(sj) from ta where ta.hid=h.id),'yyyy-mm-dd ') updateTime from t_crd_housekeeper h where h.vaild=1
8，外联
SELECT f.id, f.store_name, f.vendor_name, f.area_name, f.store_phone, h.realname, h.mobile, f.created_time FROM ( SELECT a.id, a.store_name, c.vendor_name, b.area_name, a.store_phone, a.created_time FROM pd_store a, pd_vendor_area b, pd_vendor c WHERE a.vendor_area_id = b.id AND a.vendor_id = c.id AND a.vendor_id = 1 AND b.area_type = 1 AND b.area_name LIKE '%%' LIMIT 0,5 ) f LEFT OUTER JOIN pd_clerk g ON f.id = g.store_id AND g.user_role = 2 LEFT OUTER JOIN pd_user h ON g.user_id = h.uid
9, 外联
SELECT k.operater_id, k.realname, k.mobile, k.pdNum, l.operater_id, l.realname, l.mobile, l.dslNum FROM ( SELECT a.operater_id, b.realname, b.mobile, COUNT(DISTINCT a.user_id) AS pdNum FROM pd_indicator_values a, pd_user b, pd_user_vendor c WHERE a.operater_id = b.uid AND a.vendor_id = 12 AND a.type_code <> 'hr' AND a.user_id = c.user_id AND a.take_time >= '2016-10-26 00:00:00' AND a.take_time <= '2016-11-30 23:59:59' AND c.vendor_id = 12 AND c.vendor_member_id LIKE '202%' GROUP BY a.operater_id ) k LEFT OUTER JOIN ( SELECT a.operater_id, b.realname, b.mobile, COUNT(DISTINCT a.user_id) AS dslNum FROM pd_indicator_values a, pd_user b, pd_user_vendor c WHERE a.operater_id = b.uid AND a.vendor_id = 12 AND a.type_code <> 'hr' AND a.user_id = c.user_id AND a.take_time >= '2016-10-26 00:00:00' AND a.take_time <= '2016-11-30 23:59:59' AND c.vendor_id = 12 AND c.vendor_member_id NOT LIKE '202%' GROUP BY a.operater_id ) l ON k.operater_id = l.operater_id UNION SELECT k.operater_id, k.realname, k.mobile, k.pdNum, l.operater_id, l.realname, l.mobile, l.dslNum FROM ( SELECT a.operater_id, b.realname, b.mobile, COUNT(DISTINCT a.user_id) AS pdNum FROM pd_indicator_values a, pd_user b, pd_user_vendor c WHERE a.operater_id = b.uid AND a.vendor_id = 12 AND a.type_code <> 'hr' AND a.user_id = c.user_id AND a.take_time >= '2016-10-26 00:00:00' AND a.take_time <= '2016-11-30 23:59:59' AND c.vendor_id = 12 AND c.vendor_member_id LIKE '202%' GROUP BY a.operater_id ) k RIGHT OUTER JOIN ( SELECT a.operater_id, b.realname, b.mobile, COUNT(DISTINCT a.user_id) AS dslNum FROM pd_indicator_values a, pd_user b, pd_user_vendor c WHERE a.operater_id = b.uid AND a.vendor_id = 12 AND a.type_code <> 'hr' AND a.user_id = c.user_id AND a.take_time >= '2016-10-26 00:00:00' AND a.take_time <= '2016-11-30 23:59:59' AND c.vendor_id = 12 AND c.vendor_member_id NOT LIKE '202%' GROUP BY a.operater_id ) l ON k.operater_id = l.operater_id;
N，其他
SELECT b.store_name, a.take_time, c.realname, CONCAT(LEFT(c.id_card,LENGTH(c.id_card)-4),'****'), c.mobile, ( CASE a.type_code WHEN 'bp' THEN '血压' WHEN 'hr' THEN '心率' WHEN 'fbg' THEN '空腹血糖' WHEN '2hpbg' THEN '餐后两小时血糖' WHEN 'tc' THEN '总胆固醇' WHEN 'bua' THEN '血尿酸' WHEN 'bmi' THEN '体质指数' ELSE '其他' END ) AS type_code, a.value1, a.value2, a.value3, d.realname as clerkname, d.mobile as clerkmobile FROM pd_indicator_values a, pd_store b, pd_user c, pd_user d WHERE a.store_id = b.id AND a.user_id = c.uid AND a.operater_id = d.uid AND a.store_id = 164 AND a.take_time >= '2016-09-01 00:00:00' AND a.take_time < '2016-11-01 00:00:00';
SELECT a.realname, a.gender, c.realname as clerkname, c.mobile, d.store_name, b.type_code, b.take_time, CONCAT_WS('/',b.value1,b.value2,b.value3) FROM pd_user a, pd_indicator_values b, pd_user c, pd_store d WHERE a.uid = b.user_id AND b.operater_id = c.uid AND b.store_id = d.id ORDER BY b.user_id ASC;
SELECT COUNT(*) FROM ( SELECT user_id, COUNT(user_id) AS coun, DATEDIFF( DATE(MAX(take_time)), DATE(MIN(take_time)) ) AS days FROM pd_indicator_values WHERE take_time >= '2015-11-01 00:00:00' AND take_time <= '2016-11-24 23:59:59' AND vendor_id = 1 GROUP BY user_id HAVING coun > 1 AND days > 1 ) k;
SELECT h.vendor_name, h.take_time, h.realname, h.created_time, h.gender, h.birthday, h.address, h.mobile, h.id_card, h.type_code, h.value1_status, h.value1, h.value2_status, h.value2, h.value3_status, h.value3, h.clerkname, h.clerkmobile, h.store_name, j.realname as stoname, j.mobile as stomobile FROM ( SELECT e.vendor_name, a.take_time, f.created_time, b.realname, ( CASE b.gender WHEN '0' THEN '女' WHEN '1' THEN '男' ELSE ' ' END ) AS gender, YEAR (NOW()) - YEAR (b.birthday) AS birthday, g.address, b.mobile, b.id_card, ( CASE a.type_code WHEN 'bp' THEN '血压' WHEN 'hr' THEN '心率' WHEN 'fbg' THEN '空腹血糖' WHEN '2hpbg' THEN '餐后两小时血糖' WHEN 'tc' THEN '总胆固醇' WHEN 'bua' THEN '血尿酸' WHEN 'bmi' THEN '体质指数' ELSE '其他' END ) AS type_code, ( CASE a.value1_status WHEN '1' THEN '正常' WHEN '2' THEN '风险' WHEN '3' THEN '危险' WHEN '4' THEN '偏小风险' WHEN '5' THEN '偏小危险' ELSE '其他' END ) AS value1_status, a.value1, ( CASE a.value2_status WHEN '1' THEN '正常' WHEN '2' THEN '风险' WHEN '3' THEN '危险' WHEN '4' THEN '偏小风险' WHEN '5' THEN '偏小危险' ELSE '其他' END ) AS value2_status, a.value2, ( CASE a.value3_status WHEN '1' THEN '正常' WHEN '2' THEN '风险' WHEN '3' THEN '危险' WHEN '4' THEN '偏小风险' WHEN '5' THEN '偏小危险' ELSE '其他' END ) AS value3_status, a.value3, c.realname AS clerkname, c.mobile AS clerkmobile, d.store_name, d.id, q.cou FROM ( SELECT o.user_id, COUNT(o.user_id) AS cou FROM pd_indicator_values o WHERE o.take_time >= '2015-06-01 00:00:00' AND o.take_time <= '2016-11-22 23:59:59' GROUP BY o.user_id HAVING cou > 2 ) q, pd_indicator_values a, pd_user b, pd_user c, pd_store d, pd_vendor e, pd_user_vendor f, pd_user_info g WHERE q.user_id = a.user_id AND a.user_id = b.uid AND a.operater_id = c.uid AND a.store_id = d.id AND a.vendor_id = e.id AND a.user_id = f.user_id AND a.user_id = g.user_id AND a.take_time >= '2015-06-01 00:00:00' AND a.take_time <= '2016-11-22 23:59:59' ) h, pd_clerk i, pd_user j WHERE h.id = i.store_id AND i.user_role = 2 AND i.user_id = j.uid ORDER BY h.cou DESC;
1,将查询结果存入表
insert into pd_temp select * from pd_other;
2,将表数据导出xls文件最大1048576记录数
select * from pd_temp limit 1048576,364387 into outfile 'c:\20.xls';
3,格式转换
记事本另存为ASCII格式
或
iconv -futf8 -tgb2312 -otest 21.xls 20.xls
SELECT a.take_time AS take_time, b.realname AS realname, IF(b.gender=0,'女','男') AS gender, b.mobile AS mobile, b.id_card AS id_card, IF(a.type_code='fbg', '空腹血糖', '餐后两小时血糖') AS type_code, CONCAT(a.value1,'mmol/L') AS value1, d.realname as clerkname, d.mobile as clerkmobile, c.store_name AS store_name FROM pd_indicator_values a, pd_user b, pd_store c, pd_user d WHERE a.user_id = b.uid AND a.operater_id = d.uid AND a.store_id = c.id AND a.vendor_id = 1 AND a.province_code = 520000 AND a.city_code = 520100 AND a.take_time >= '2016-08-01 00:00:00' AND a.take_time < '2016-09-01 00:00:00' AND ( a.type_code = 'fbg' OR a.type_code = '2hpbg' ) AND ( a.value1_status = 3 OR a.value1_status = 5 );
SELECT d.id, d.store_name, c.realname, c.mobile, count(a.caller_user_id), count( DISTINCT (a.called_user_id) & a.begin_calltime > 0 ), count(a.begin_calltime > 0), SUM(a.caller_duration) FROM pd_call_records a, pd_clerk b, pd_user c, pd_store d WHERE a.caller_user_id = b.user_id AND b.user_id = c.uid AND b.store_id = d.id AND a.created_time >= '2016-08-01 18:48:45' GROUP BY a.caller_user_id;
SELECT c.realname, c.mobile, SUBSTRING_INDEX(c.context, '}', 1), c.callednum, c.callnum, c.calltime, d.realname, f.store_name, d.mobile FROM ( SELECT b.realname AS realname, b.mobile AS mobile, SUBSTRING_INDEX(a.task_context, ':' ,- 1) AS context, IF (a.caller_duration > 0, 1, 0) AS callednum, IF (a.called_duration > 0, 1, 0) AS callnum, IFNULL(a.called_duration, 0) AS calltime, a.caller_user_id AS clerkid FROM pd_call_records a, pd_user b WHERE a.called_user_id = b.uid AND task_id > 0 ) c, pd_user d, pd_clerk e, pd_store f WHERE c.clerkid = d.uid AND d.uid = e.user_id AND e.store_id = f.id;
SELECT COUNT(k.user_id) from ( SELECT a.user_id, DATEDIFF(DATE(MAX(a.take_time)),DATE(MIN(a.take_time))) as days FROM pd_indicator_values a WHERE a.vendor_id = 12 AND a.take_time >= '2016-10-26 00:00:00' AND a.take_time <= '2016-11-30 23:59:59' GROUP BY a.user_id )k where k.days>1
邮箱联想匹配：
SELECT a.ID, b.USER_ID, a.PERSON_ID, a.TYPE, a.MAIL, a.CREATE_TIME, a.UPDATE_TIME, a.DELETE_TAG, a.MAIN_USER_ID, b.`NAME`, b.AVATAR, b.LEVEL1_GROUP_ID, b.LEVEL2_GROUP_ID, b.LEVEL3_GROUP_ID FROM t_contacts_person_mail a, t_contacts_person b WHERE a.PERSON_ID = b.ID AND a.DELETE_TAG = 0 AND b.DELETE_TAG = 0 AND locate('@',a.MAIL)>0 -- 子账号 AND (b.USER_ID ='9e5687aa76b74daaae47bdbf9f453e97' OR (b.USER_ID='83fcb7323c9a47de98403be7cedb9433' AND b.IS_OPEN = 1)) -- 主账号 -- AND b.USER_ID in ('83fcb7323c9a47de98403be7cedb9433', '9e5687aa76b74daaae47bdbf9f453e97') AND substring_index(a.MAIL, '@', 1) LIKE CONCAT('%', 'a', '%') GROUP BY a.MAIL ORDER BY (length(substring_index(a.MAIL, '@', 1)) - length('a')) ASC, b.IS_OPEN ASC, b.CREATE_TIME DESC LIMIT 0,10