select
mch_nm
,sum(trx_amt) as trx_amt
,count(1) as trx_cnt
,min(trx_time) as first_time
from
cmb_usr_trx_rcd
where
usr_id='5201314520'
and trx_amt>=288
group by mch_nm
order by 3 desc
1、感受多列分组与单列分组，仔细对比这段代码跟你的代码的区别，数字是一样的；
2、以后你取数了，业务方让你取特定商户、特定分类的聚合数据，也可以加上，这样比较【踏实】（虽然第一列是重复的）
select
area
,count(*) as total_companies
,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies
,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion
from
stock_info
group by
1
order by
4 desc ===按比例排序哦，你写成第3列了。
limit
5
想用非，没毛病啊，select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 21 ，临界点改对了就行。
这题考的就是inner join和left join的使用场景。你运行这段代码试试，SELECT
s.singer_id,
s.singer_name,
a.album_id,
a.album_name,
COUNT(l.id) AS play_count
FROM
singer_info s
JOIN
album_info a ON s.singer_id = a.singer_id
inner JOIN
song_info sg ON a.album_id = sg.album_id
inner JOIN
listen_rcd l ON sg.song_id = l.song_id
GROUP BY
s.singer_id, s.singer_name, a.album_id, a.album_name。
select city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as p
from
weather_rcd_china
where
year(dt)=2021
group by
city
order by
3 desc 而且也能通过
round和cast as decimal是有区别的这个你知道不？ round(23.657,2)=23.66, decimal的话等于23.65。
select city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as p
from
weather_rcd_china
where
year(dt)=2021
group by
city
order by
3 desc 而且也能通过
如下代码可以通过测试呀，你再试试呢。 SELECT
t2.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM
ks_live_t1 t1
JOIN
ks_live_t2 t2
ON
t1.live_id = t2.live_id
WHERE
DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY
t1.live_id, t2.live_nm
ORDER BY
enter_cnt DESC
LIMIT 5;
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
WITH monthly_avg_spending AS (
SELECT
mch_nm,
DATE_FORMAT(trx_dt, '%Y-%m') AS month,
AVG(trx_amt) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm, month
),
lagged_avg_spending AS (
SELECT
mas.mch_nm,
mas.month,
mas.avg_spending,
LAG(mas.avg_spending) OVER (PARTITION BY mas.mch_nm ORDER BY mas.month) AS prev_avg_spending
FROM monthly_avg_spending mas
)
SELECT
las.mch_nm,
las.month,
las.avg_spending,
ROUND(((las.avg_spending - COALESCE(las.prev_avg_spending, 0)) / NULLIF(las.prev_avg_spending, 0)) * 100, 2) AS growth_rate
FROM lagged_avg_spending las
ORDER BY las.mch_nm, las.month;
SELECT
a.trx_mon trx,
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 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 name, subject, class_code, graduate_date
FROM teachers
WHERE gender = 'f' AND year(graduate_date)=1994 OR year(graduate_date)=1995
OR year(graduate_date)=1996 OR year(graduate_date)=1997
ORDER BY graduate_date ASC;
SELECT name, subject, class_code, graduate_date
FROM teachers
WHERE year(graduate_date)=1994 OR year(graduate_date)=1995
OR year(graduate_date)=1996 OR year(graduate_date)=1997 AND gender = 'f'
ORDER BY graduate_date ASC;
SELECT name, subject, class_code, qualification
FROM teachers
WHERE fir_degr = '北京大学' OR fir_degr = '清华大学' AND head_teacher IS NOT NULL
ORDER BY name ASC;
select right('2♠',1) 输出什么？这道题就是为了告诉你，字符串也是可以比较的，比较的逻辑就是字符编码的位置。你把下面这段代码丢给大模型问问是什么意思就明白啦 select ord('J'),ord('K'),ord('Q')select area ,count(*) as total_companies ,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies ,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion from stock_info group by 1 order by 4 desc ===按比例排序哦，你写成第3列了。 limit 5再理解下这种写法：SELECT i.screen_type, ROUND(COALESCE( SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 1 AND i.if_hint = 1)) * 100.0 / NULLIF(SUM(i.if_AI_talking = 1 AND i.if_hint = 1), 0), 0 ), 2) AS AI_with_hint, ROUND(COALESCE( SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 1 AND i.if_hint = 0)) * 100.0 / NULLIF(SUM(i.if_AI_talking = 1 AND i.if_hint = 0), 0), 0 ), 2) AS AI_no_hint, ROUND(COALESCE( SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 0 AND i.if_hint = 1)) * 100.0 / NULLIF(SUM(i.if_AI_talking = 0 AND i.if_hint = 1), 0), 0 ), 2) AS no_AI_with_hint, ROUND(COALESCE( SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 0 AND i.if_hint = 0)) * 100.0 / NULLIF(SUM(i.if_AI_talking = 0 AND i.if_hint = 0), 0), 0 ), 2) AS no_AI_no_hint FROM ks_video_inf i JOIN ks_video_wat_log l ON i.video_id = l.video_id GROUP BY i.screen_type;。这个是最直接的，没有弯弯绕绕的多次计算。你写的相当于多了一次去重。把分子分母都搞小了。同学，两个点说明了你的基础很薄弱哈。 1.请问date('2024-09-30 12:23:12')输出的日期是什么？你写成了betwwen 的尾巴'2024-10-01' ，那会把这天的数据也包括进去的； 2.and 和or区分好，只要学过初中英语你就能会啊！