最近工作繁忙，没时间写技术博客，简单发点写过的主要sql吧
同字段不同值分列统计
其实就是利用了一下mysql的case... then...做聚合
SELECT
t.ads_id,
t.position_id,
a. NAME AS ads_name,
SUM(CASE t.action WHEN 0 THEN 1 ELSE 0 END) 'require',
SUM(CASE t.action WHEN 1 THEN 1 ELSE 0 END) 'in',
SUM(CASE t.action WHEN 2 THEN 1 ELSE 0 END) 'click',
SUM(CASE t.action WHEN 4 THEN 1 ELSE 0 END) 'inPage',
SUM(CASE t.action WHEN 8 THEN 1 ELSE 0 END) 'out'
FROM
`statistics_ads` `t`
LEFT JOIN `ads` `a` ON `t`.`ads_id` = `a`.`id`
GROUP BY
`t`.`ads_id`,
`t`.`position_id`
ORDER BY
`t`.`ads_id` DESC;
效果
image.png
搜索权重匹配排序
其实就是将mysql的LENGTH和substring_index灵活运用了下
SELECT
`t`.`id` AS `book_id`,
`t`.`book_name`,
`t`.`book_introduction`,
`t`.`book_images`,
`t`.`book_author`,
`t`.`book_count`,
`t`.`book_star`,
`t`.`book_status`,
`c`.`category_name`
FROM
`book_store`.`book` `t`
LEFT JOIN `book_store`.`category` `c` ON `t`.`book_category` = `c`.`id`
WHERE
(
(
`t`.`book_name` LIKE '%天降%'
)
OR (
`t`.`book_author` LIKE '%天降%'
)
)
AND (`book_online` = 1)
ORDER BY
LENGTH(
substring_index(t.book_name, '天降', 1)
),
LENGTH(
substring_index(t.book_author, '天降', 1)
),
LENGTH(t.book_name),
LENGTH(t.book_author)
LIMIT 20
image.png
查询/统计某个字段中特殊符号的个数
-- 1.
SELECT
count( 1 ) AS total
FROM
tableName
WHERE
type = 1
AND is_deleted = 0
AND LENGTH( `code` ) - LENGTH( REPLACE ( `code`, '.', '' ) ) != LENGTH( full_name ) - LENGTH( REPLACE ( full_name, '-', '' ) );
-- 2.
SELECT
id,
LENGTH( `code` ) - LENGTH( REPLACE ( `code`, '.', '' ) ) AS codeSplitNum,
LENGTH( full_name ) - LENGTH( REPLACE ( full_name, '-', '' ) ) AS fullNameSplitNum
FROM
tableName
WHERE
type = 1
AND is_deleted = 0
HAVING
codeSplitNum != fullNameSplitNum;
