最近工作繁忙，沒時間寫技術博客，簡單發點寫過的主要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;

總結

以上是生活随笔為你收集整理的mysql 同一字段分别统计,mysql查询同一个字段根据不同值分组成不同列统计的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。