最近工作繁忙，没时间写技术博客，简单发点写过的主要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;