我们平时还有一种写法，是先用substr把时间戳的日期前缀截取出来，这样就不用担心闭合不全的问题了：
SELECT A.usr_id,A.mch_nm, A.trx_time,A.trx_amt
FROM
(SELECT *,SUBSTR(trx_time,1,10) AS date_id
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520' ) AS A
WHERE A.date_id BETWEEN '2024-09-01' AND '2024-09-30'
ORDER BY trx_time
In SQL, the BETWEEN operator is inclusive, meaning it includes the boundary values. However, when comparing a timestamp with a date, the date is implicitly treated as the start of the day (i.e., '2024-09-30' is treated as '2024-09-30 00:00:00').
所以如果不想用date，2024-09-30就要改成2024-10-01。但是假如刚好有一条 2024-10-01 00:00:00，那么查询的结果也是错的。保险起见还是加上date比较好。这个太坑了，平时真的很少会留意这些细节
select
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and date(trx_time) <= '2024-12-31'
and
(((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (0,23,1,2))
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND ((TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98')
AND trx_amt > 200
AND (HOUR(trx_time) >=23 OR HOUR(trx_time)<=3))
OR (upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA')
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND ((TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98')
AND trx_amt > 200
AND (HOUR(trx_time) >=23 OR HOUR(trx_time)<=3))
OR (lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa')
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND (TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98')
AND trx_amt > 200
AND (HOUR(trx_time) >=23 OR HOUR(trx_time)<=3)
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
select
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and date(trx_time) <= '2024-12-31'
and
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (0,23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) > '2022-11-01' AND date(trx_time)<='2024-12-31'
AND usr_id = '5201314520'
AND (TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200 AND HOUR(trx_time)IN (23,0,1,2))
OR upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
GROUP BY trx_mon
ORDER BY trx_mon ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND (TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200 AND HOUR(trx_time)IN (23,0,1,2))
OR upper(mch_nm) RLIKE '足疗|保健|按摩|养生|SPA'
GROUP BY trx_mon
ORDER BY trx_mon ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND (TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200 AND HOUR(trx_time)IN (23,0,1,2))
OR lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY trx_mon
ORDER BY trx_mon ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND (TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200 AND HOUR(trx_time)IN (23,0,1,2))
OR lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND ((TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200)AND HOUR(trx_time) IN (23,0,1,2))
OR lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND ((TRUNCATE(trx_amt,0) RLIKE "88$|98$" and trx_amt>200))
AND HOUR(trx_time) IN (23,0,1,2)
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98'
AND trx_amt > 200
AND HOUR(trx_time) IN (23,0,1,2)
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_mon,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98'
AND trx_amt > 200
AND HOUR(trx_time) >=23 AND HOUR(trx_time)<=3
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS trx_month,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98'
AND trx_amt > 200
AND HOUR(trx_time) >=23 AND HOUR(trx_time)<=3
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
SELECT
SUBSTR(trx_time,1,7) AS month,
COUNT(1) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM cmb_usr_trx_rcd
WHERE date(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND usr_id = '5201314520'
AND TRUNCATE(trx_amt,0) LIKE '%88' OR TRUNCATE(trx_amt,0) LIKE '%98'
AND trx_amt > 200
AND HOUR(trx_time) >=23 AND HOUR(trx_time)<=3
AND lower(mch_nm) RLIKE '足疗|保健|按摩|养生|spa'
GROUP BY SUBSTR(trx_time,1,7)
ORDER BY 1 ASC
select
case
when
(truncate(trx_amt, 0) like '%88' or truncate(trx_amt, 0) like '%98') and
trx_amt >= 200 and
(hour(trx_time) between 0 and 3 or hour(trx_time) = 23)
then 'illegal'
else 'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
group by
trx_typ
order by
trx_cnt desc;
SELECT
CASE
WHEN (FLOOR(trx_amt) % 100 IN (88, 98) AND trx_amt > 200)
AND
(TIME(trx_time) >= '23:00:00' OR TIME(trx_time) < '04:00:00')
THEN 'illegal'
ELSE 'other'
END AS trx_typ,
COUNT(*) AS trx_cnt,
ROUND(SUM(trx_amt), 2) AS trx_amt,
COUNT(DISTINCT mch_nm) AS mch_cnt
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
GROUP BY trx_typ
SELECT
CASE WHEN mch_nm LIKE '%按摩保健休闲%' THEN '按摩保健休闲'
WHEN lower(mch_nm) RLIKE '按摩|保健|休闲|养生|spa|会所' THEN
'按摩、保健、休闲、养生、SPA、会所' END AS reg_rules,
COUNT(DISTINCT mch_nm) AS mch_cnt
FROM cmb_usr_trx_rcd
WHERE lower(mch_nm) RLIKE '按摩|保健|休闲|养生|spa|会所'
OR mch_nm LIKE '%按摩保健休闲%'
GROUP BY reg_rules
SELECT
CASE WHEN trx_amt = 288 THEN '1.WithHand'
WHEN trx_amt = 388 THEN '2.WithMimi'
WHEN trx_amt = 588 THEN '3.BlowJobbie'
WHEN trx_amt = 888 THEN '4.Doi'
WHEN trx_amt = 1288 THEN '5.DoubleFly'
ELSE '6.other' END as ser_type,
COUNT(1) AS trx_cnt,
MIN(DATE(trx_time)) AS first_date
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
AND usr_id = '5201314520'
GROUP BY
CASE WHEN trx_amt = 288 THEN '1.WithHand'
WHEN trx_amt = 388 THEN '2.WithMimi'
WHEN trx_amt = 588 THEN '3.BlowJobbie'
WHEN trx_amt = 888 THEN '4.Doi'
WHEN trx_amt = 1288 THEN '5.DoubleFly'
ELSE '6.other' END
ORDER BY 1 ASC