SQL函数
2024-10-19 00:22:13
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 '' THEN
'女'
WHEN '' 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 '' THEN
'正常'
WHEN '' THEN
'风险'
WHEN '' THEN
'危险'
WHEN '' THEN
'偏小风险'
WHEN '' THEN
'偏小危险'
ELSE
'其他'
END
) AS value1_status,
a.value1,
(
CASE a.value2_status
WHEN '' THEN
'正常'
WHEN '' THEN
'风险'
WHEN '' THEN
'危险'
WHEN '' THEN
'偏小风险'
WHEN '' THEN
'偏小危险'
ELSE
'其他'
END
) AS value2_status,
a.value2,
(
CASE a.value3_status
WHEN '' THEN
'正常'
WHEN '' THEN
'风险'
WHEN '' THEN
'危险'
WHEN '' THEN
'偏小风险'
WHEN '' 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
最新文章
- CSS3 Border-image
- android手机出现sqlite3 not found的解决方法
- java Timer（定时调用、实现固定时间执行）
- uva 10881 - Piotr's Ants
- 《算法问题实战策略》-chaper21-树的实现和遍历
- poj 3255 求次大最短路
- 项目中常用方法总结（将将DataTable数据集映射到实体对象）【转】
- 编译Boost 详细步骤 适用 VC6 VS2003 VS2005 VS2008 VS2010
- php如何上传txt文件，并且读取txt文件
- the c programing language 学习过程7
- gRPC源码分析（c++）
- 浅谈TCP IP协议栈(四)IP协议解析
- Python变量命名规范
- urllib 学习二
- SpringBoot学习历程
- nginx配置文件nginx.conf 不包括server节点
- 利用latex制作个人简历
- Robolectric使用教程
- Git的4个阶段的撤销更改
- hdu 3262 09 宁波 现场 C - Seat taking up is tough 暴力 难度:0