前言
記錄自己在使用 MySQL 過程中踩過的坑、SQL 最佳化方案等。
有收穫的話請 點個贊,沒有收穫的話可以 反對 沒有幫助 舉報 三連。
需求
實現一個統計功能,在轉賬記錄表中,統計每個使用者的充值次數-pt、充值數額-pa,提現次數-wt,提現數額-wa;支援時間範圍查詢,四個統計欄位範圍查詢,分頁查詢等。
表結構
CREATE TABLE `table_name` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '使用者',
`admin` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '管理員',
`txid` char(120) NOT NULL DEFAULT '' COMMENT '交易號',
`confirm` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '確認數',
`number` decimal(20,8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT '數量',
`pay` decimal(20,8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT '實際數量',
`opt_type` enum('in','out') NOT NULL DEFAULT 'in' COMMENT '型別',
`status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '狀態',
`msg` char(255) NOT NULL DEFAULT '' COMMENT '訊息',
`created` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '建立時間',
`createip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '建立ip',
`updated` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '修改時間',
`updateip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '修改ip',
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `txid` (`txid`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8 COMMENT='轉賬記錄表';
思路梳理
- 方案1:分兩次查詢,先查充值或提現,再用得到的
uid
進行in
查詢 - 方案2:寫個子查詢
sql
,根據查詢條件組裝,然後join
查詢
實現(方案2)
話不多說直接上 sql
//子查詢 查詢條件
$field = "SELECT uid,COUNT(uid) AS %s,SUM(number) AS %s FROM ${table}
WHERE opt_type = '%s' AND created BETWEEN %s AND %s
GROUP BY uid HAVING %s BETWEEN %s AND %s ";
$sql = "SELECT a.*, COUNT(b.uid) AS %s, SUM(b.number) AS %s FROM ( @field@ ) a
LEFT JOIN ${table} b ON a.uid = b.uid AND b.opt_type = '%s'
GROUP BY uid LIMIT ${firstRow},${page_size};";
根據查詢條件可以組裝四種sql出來,下面就放出來其中一種,最終執行的 sql
↓
SELECT
a.*, COUNT(b.uid) AS pt,
SUM(b.number) AS pa
FROM
(
SELECT
uid,
COUNT(uid) AS wt,
SUM(number) AS wa
FROM
table_name
WHERE opt_type = 'out'
AND created BETWEEN 1472918400 AND 1571673600
GROUP BY uid
HAVING `wt` BETWEEN 0 AND 1
) a
LEFT JOIN table_name b ON a.uid = b.uid
AND b.opt_type = 'in'
GROUP BY uid
LIMIT 0,10;
分析
用 EXPLAIN
去分析該 sql
,效果並不好,這個功能暫時還沒做最佳化,歡迎各位評論~~~
本作品採用《CC 協議》,轉載必須註明作者和本文連結