我们平时还有一种写法，是先用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比较好。这个太坑了，平时真的很少会留意这些细节
with a as (
select '2024' as trx_month,mch_nm,sum_trx_amt
from(
select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time) = 2024 and usr_id = '5201314520'
group by mch_nm) as a
order by sum_trx_amt desc
limit 3),
b as (
select b.*
from(
select a.*,row_number()over(partition by a.trx_month order by a.sum_trx_amt desc) as rank_num
from(
select substr(trx_time,1,7) as trx_month,mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time) = 2024 and usr_id = '5201314520'
group by substr(trx_time,1,7),mch_nm
) as a
) as b
where b.rank_num <= 3
)
select a.* from a
union all
select b.trx_month,b.mch_nm,b.sum_trx_amt from b
with a as (
select '2024' as trx_month,mch_nm,sum_trx_amt
from(
select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time) = 2024 and usr_id = '5201314520'
group by mch_nm) as a
order by sum_trx_amt desc
limit 3),
b as (
select b.*
from(
select a.*,row_number()over(partition by a.trx_month,mch_nm order by a.sum_trx_amt desc) as rank_num
from(
select substr(trx_time,1,7) as trx_month,mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time) = 2024 and usr_id = '5201314520'
group by substr(trx_time,1,7),mch_nm
) as a
) as b
where b.rank_num <= 3
)
select a.* from a
union all
select b.trx_month,b.mch_nm,b.sum_trx_amt from b
with time_temp as(
select min(trx_time) as first_trx_time
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm = '红玫瑰按摩保健休闲'
)
select *
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between
(select first_trx_time from time_temp)
and
(select date_add(first_trx_time,interval 2 hour) from time_temp)
SELECT
'2022-10-03 17:20:20' AS time_he_love_me,
DATEDIFF(CURRENT_DATE(), '2022-10-03') AS days_we_falling_love,
TIMESTAMPDIFF(HOUR,'2022-10-03 17:20:20',NOW()) AS hours_we_falling_love,
DATEDIFF(MIN(DATE(trx_time)), '2022-10-03') AS days_from_first_trx
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520' AND mch_nm = '红玫瑰按摩保健休闲'
SELECT
'2022-10-03 17:20:20' AS time_he_love_me,
DATEDIFF(CURRENT_DATE(), '2022-10-03') AS days_we_falling_love,
TIMESTAMPDIFF(HOUR,'2022-10-03 17:20:20',NOW()) AS hours_we_falling_love,
DATEDIFF(MIN(DATE(trx_time)), '2022-10-03') AS days_from_first_trx
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520' AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY mch_nm
SELECT
a.trx_mon,
COALESCE(b.last_day, '1900-01-01') AS last_day,
COALESCE(b.day_of_mon, 0) AS day_of_mon,
COALESCE(b.trx_amt, 0) AS trx_amt,
COALESCE(b.trx_cnt, 0) AS trx_cnt,
COALESCE(ROUND(b.avg_day_amt, 2), 0) AS avg_day_amt,
COALESCE(ROUND(b.avg_day_cnt, 2), 0) AS avg_day_cnt
FROM
(SELECT DISTINCT DATE_FORMAT(date_value, '%Y-%m') AS trx_mon
FROM date_table
WHERE DATE_FORMAT(date_value, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
) a
LEFT JOIN
(SELECT
DATE_FORMAT(a.trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(MAX(a.trx_time)) AS last_day,
DAY(LAST_DAY(MAX(a.trx_time))) AS day_of_mon,
SUM(a.trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(a.trx_amt) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_amt,
COUNT(*) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_cnt
FROM cmb_usr_trx_rcd a
LEFT JOIN cmb_mch_typ m ON a.mch_nm = m.mch_nm
WHERE a.usr_id = 5201314520
AND DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
AND (m.mch_typ = '休闲娱乐' OR m.mch_typ IS NULL)
AND HOUR(a.trx_time) IN (23, 0, 1, 2)
AND a.trx_amt >= 288
GROUP BY DATE_FORMAT(a.trx_time, '%Y-%m')
ORDER BY 1
) b
ON a.trx_mon = b.trx_mon
ORDER BY a.trx_mon;
select
a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,'0') as day_of_mon,
coalesce(b.trx_amt,'0') as trx_amt,
coalesce(b.trx_cnt,'0') as trx_cnt,
coalesce(round(b.avg_day_amt,2),'0') as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),'0') as avg_day_cnt
from(
select distinct substr(date_value,1,7) as trx_mon
from date_table
where date_value between '2023-01-01' AND '2024-06-30'
) as a
left join
(
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(a.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(a.trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and date(a.trx_time) BETWEEN '2023-01-01' AND '2024-6-30'
and ((b.mch_typ='休闲娱乐'or b.mch_typ is null)
and a.trx_amt>=288
and hour(a.trx_time) in (23,0,1,2))
group by substr(a.trx_time,1,7)
order by 1 asc) as b
on a.trx_mon = b.trx_mon
select
a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,'0') as day_of_mon,
coalesce(b.trx_amt,'0') as trx_amt,
coalesce(b.trx_cnt,'0') as trx_cnt,
coalesce(b.avg_day_amt,'0') as avg_day_amt,
coalesce(b.avg_day_cnt,'0') as avg_day_cnt
from(
select distinct substr(date_value,1,7) as trx_mon
from date_table
where date_value between '2023-01-01' AND '2024-06-30'
) as a
left join
(
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(a.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(a.trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and date(a.trx_time) BETWEEN '2023-01-01' AND '2024-6-30'
and ((b.mch_typ='休闲娱乐'or b.mch_typ is null)
and a.trx_amt>=288
and hour(a.trx_time) in (23,0,1,2))
group by substr(a.trx_time,1,7)
order by 1 asc) as b
on a.trx_mon = b.trx_mon
select
a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,'0') as last_day,
coalesce(b.trx_amt,'0') as trx_amt,
coalesce(b.trx_cnt,'0') as trx_cnt,
coalesce(b.avg_day_amt,'0') as avg_day_amt,
coalesce(b.avg_day_cnt,'0') as avg_day_cnt
from(
select distinct substr(date_value,1,7) as trx_mon
from date_table
where date_value between '2023-01-01' AND '2024-06-30'
) as a
left join
(
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(a.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(a.trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and date(a.trx_time) BETWEEN '2023-01-01' AND '2024-6-30'
and ((b.mch_typ='休闲娱乐'or b.mch_typ is null)
and a.trx_amt>=288
and hour(a.trx_time) in (23,0,1,2))
group by substr(a.trx_time,1,7)
order by 1 asc) as b
on a.trx_mon = b.trx_mon
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day,
sum(a.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(a.trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and year(a.trx_time) in (2023,2024)
and b.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1 asc
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and year(a.trx_time) in (2023,2024)
and b.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1 asc
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as day
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in (2023,2024)
group by substr(trx_time,1,7)
order by 1 asc
select substr(trx_time,1,7) as trx_mon,max(last_day(trx_time)) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024)
and usr_id = '5201314520'
group by substr(trx_time,1,7)
order by 1 asc
select substr(trx_time,1,7) as trx_mon,last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024)
and usr_id = '5201314520'
order by 1 asc
select substr(trx_time,1,7) as trx_mon,last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024)
and usr_id = '5201314520'
select b.mch_typ,a.mch_nm,count(1) as trx_cnt,sum(a.trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where year(a.trx_time) = 2024 and a.usr_id = '5201314520'
and b.mch_nm is null
group by b.mch_typ,a.mch_nm
select b.mch_typ,count(1) as trx_cnt ,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520' and year(a.trx_time) = 2024
group by b.mch_typ
select b.mch_typ,count(1) as trx_cnt ,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where a.usr_id = '5201314520'
group by b.mch_typ