我觉得今天我写的sql可以在博客里写上一笔了
SELECT
register_addr 'key',
count(user_id) 'value'
FROM
(
SELECT
bi.user_id,
(
CASE
WHEN LOCATE('省', bi.register_addr) > 0 THEN
LEFT (
bi.register_addr,
LOCATE('省', bi.register_addr) - 1
)
WHEN trim(bi.register_addr) IN ('北京', '上海') THEN
trim(bi.register_addr)
END
) AS register_addr
FROM
cl_user u
LEFT JOIN cl_user_base_info bi ON u.id = bi.user_id
WHERE
(
DATE_FORMAT(u.regist_time, '%Y-%m-%d') >= CONCAT(
DATE_FORMAT(SYSDATE(), '%Y-%m'),
'-01'
)
)
AND (bi.register_addr != '')
) t
GROUP BY
register_addr
查询所有表信息:
SELECT
table_name,
column_name,
column_comment,
data_type
FROM
information_schema. COLUMNS
WHERE
table_schema = (SELECT DATABASE())
ORDER BY
table_name ASC;
sql 嵌套统计查询:
SELECT
*
FROM
cl_user
LEFT JOIN cl_user_base_info ON cl_user.id = cl_user_base_info.user_id
LEFT JOIN cl_user_auth ON cl_user_base_info.user_id = cl_user_auth.user_id
LEFT JOIN cl_borrow ON cl_user_base_info.user_id = cl_borrow.user_id
LEFT JOIN ( SELECT COUNT(0) num, borrow_id FROM cl_extension_log GROUP BY borrow_id) cut ON cut.borrow_id = cl_borrow.id
LEFT JOIN ( SELECT COUNT(0) num, id FROM cl_borrow GROUP BY id ) cutbo ON cutbo.id = cl_borrow.id
GROUP BY cl_user.id