select
mch_nm
from cmb_usr_trx_rcd
where usr_id in ('5201314520', '5211314521') and year(trx_time) = 2024
group by mch_nm
having count(distinct usr_id) = 2；很棒的思路。记得加distinct
SELECT
distinct
gd.gd_id,
gd.gd_nm,
gd.gd_typ
FROM
xhs_fav_rcd fav
JOIN
gd_inf gd ON fav.mch_id = gd.gd_id
LEFT JOIN
xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE
pchs.mch_id IS NULL
1、5点多的，只要不超过6点，都应被算进去；
2、你的代码细节有问题，详见下面
select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and (date_format(trx_time,'%H:%i:%s') >='22:00:00' or date_format(trx_time,'%H:%i:%s')<='06:00:00' )
order by trx_time
WITH total_score AS (
SELECT
s.class_code,
COUNT(DISTINCT s.student_id),
SUM(sc.score) AS sum,
t.name
FROM students s
INNER JOIN scores sc ON s.student_id = sc.student_id
INNER JOIN teachers t ON s.class_code = t.head_teacher
GROUP BY s.class_code, t.name
)
select * from total_score 。你with创建了一个子查询，后面没有select动作了，当然会报错了
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;
WITH yearly_avg AS (
SELECT
city,
YEAR(dt) AS year,
AVG(REPLACE(tmp_h, '℃', '') + 0) AS avg_high_temp
FROM weather_rcd_china
WHERE city = 'shenzhen'
AND dt BETWEEN '2011-01-01' AND '2022-12-31'
GROUP BY city, YEAR(dt)
),
yearly_avg_with_lag AS (
SELECT
city,
year,
avg_high_temp,
LAG(avg_high_temp,1,1000) OVER (ORDER BY year) AS prev_year_avg_temp
FROM yearly_avg
),
temp_changes AS (
SELECT
year,
avg_high_temp,
prev_year_avg_temp,
(avg_high_temp - COALESCE(prev_year_avg_temp, 0)) AS temp_change
FROM yearly_avg_with_lag
)
SELECT
year,
cast(avg_high_temp as decimal(10,2)) as avg_tmp_h,
CASE
WHEN ABS(avg_high_temp - COALESCE(prev_year_avg_temp, 0)) between 1 and 100 THEN 'Yes'
ELSE 'No'
END AS significant_change
FROM temp_changes
WHERE year BETWEEN 2011 AND 2022
ORDER BY year;
with active_users_before_august as (
select
usr_id,
date_format(login_time, '%Y-%m') as month
from
user_login_log
where
login_time < '2024-08-01'
group by
usr_id, date_format(login_time, '%Y-%m')
having
count(*) >= 10
),
active_users_after_august as (
select
usr_id,
date_format(login_time, '%Y-%m') as month
from
user_login_log
where
login_time >= '2024-08-01'
group by
usr_id, date_format(login_time, '%Y-%m')
having
count(*) >= 10
)
select
count(distinct au1.usr_id) as inactive_user_count
from
active_users_before_august au1
where
not exists (
select 1
from active_users_after_august au2
where au2.usr_id = au1.usr_id
);
select
trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1) / count(distinct usr_id) as avg_trx_per_user
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
(
(year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by
trx_amt
order by
avg_trx_per_user desc
limit 5;
WITH daily_rides AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date
FROM hello_bike_riding_rcd
),
streak_groups AS (
SELECT
user_id,
ride_date,
ride_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ride_date) DAY AS grp
FROM daily_rides
),
streak_counts AS (
SELECT user_id, grp, COUNT(*) AS streak
FROM streak_groups
GROUP BY user_id, grp
)
SELECT
user_id,
MAX(streak) AS max_streak
FROM streak_counts
GROUP BY user_id
HAVING max_streak >= 3
ORDER BY max_streak DESC
LIMIT 3
with daily_sales as (
select
date(order_time) as order_date,
goods_id,
sum(order_gmv) as total_gmv
from
order_info
where
date(order_time) >= '2024-10-01' and date(order_time) < '2024-11-01'
group by
date(order_time), goods_id
),
ranked_sales as (
select
order_date,
goods_id,
total_gmv,
row_number() over (partition by order_date order by total_gmv asc) as ranking
from
daily_sales
)
select
order_date,
goods_id,
total_gmv,
ranking
from
ranked_sales
where
ranking <= 3
order by
order_date,
ranking;
SELECT
a.prd_id,
p.prd_nm,
SUM(a.if_snd) as expose_count,
SUM(a.if_vw) as view_count,
SUM(a.if_cart) as cart_count,
SUM(a.if_buy) as buy_count
FROM tb_pg_act_rcd a
JOIN tb_prd_map p ON a.prd_id = p.prd_id
GROUP BY a.prd_id, p.prd_nm
HAVING expose_count > 500
ORDER BY expose_count DESC;
SELECT
COUNT(*) AS count_520,
COUNT(DISTINCT snd_usr_id) AS sender_count,
COUNT(DISTINCT rcv_usr_id) AS receiver_count,
SUM(CASE WHEN rcv_datetime > '1900-01-01' THEN 1 ELSE 0 END) AS received_count,
SUM(CASE WHEN rcv_datetime = '1900-01-01' THEN 1 ELSE 0 END) AS unreceived_count
FROM tx_red_pkt_rcd
WHERE pkt_amt = 520;
SELECT
c.cty_cls AS city_level,
COUNT(*) AS send_count,
SUM(r.pkt_amt) AS total_amount
FROM tx_red_pkt_rcd r
INNER JOIN tx_usr_bas_info u ON r.snd_usr_id = u.usr_id
INNER JOIN tx_cty_map c ON u.cty = c.cty
GROUP BY c.cty_cls
ORDER BY send_count DESC;
SELECT
u.cty AS city,
COUNT(*) AS send_count,
SUM(r.pkt_amt) AS total_amount,
COUNT(DISTINCT r.snd_usr_id) AS sender_count,
ROUND(SUM(r.pkt_amt) / COUNT(DISTINCT r.snd_usr_id), 2) AS avg_per_sender
FROM tx_red_pkt_rcd r
INNER JOIN tx_usr_bas_info u ON r.snd_usr_id = u.usr_id
WHERE u.cty IN ('北京市', '上海市', '广州市', '深圳市')
GROUP BY u.cty
ORDER BY total_amount DESC;
SELECT
CASE
WHEN buy_count = 1 THEN '1次'
WHEN buy_count BETWEEN 2 AND 3 THEN '2-3次'
ELSE '4次以上'
END as freq_range,
COUNT(*) as user_count
FROM (
SELECT cust_uid, SUM(if_buy) as buy_count
FROM tb_pg_act_rcd
GROUP BY cust_uid
) t
GROUP BY freq_range
ORDER BY user_count DESC;
SELECT
c.gdr,
SUM(a.if_snd) as expose_count,
SUM(a.if_buy) as buy_count,
ROUND(SUM(a.if_buy) * 100.0 / SUM(a.if_snd), 2) as conversion_rate
FROM tb_pg_act_rcd a
JOIN tb_cst_bas_inf c ON a.cust_uid = c.cust_uid
GROUP BY c.gdr
ORDER BY conversion_rate DESC;
SELECT
ts_code,
MIN(trade_date) as start_date,
COUNT(*) as consecutive_days
FROM (
SELECT
ts_code,
trade_date,
pct_change,
DATE_SUB(trade_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY ts_code ORDER BY trade_date) DAY) as group_key
FROM daily_stock_prices
WHERE pct_change > 0
) t
GROUP BY ts_code, group_key
HAVING COUNT(*) >= 3
ORDER BY consecutive_days DESC;
SELECT
d.ts_code,
s.name,
ROUND(AVG((d.high_price - d.low_price) / d.open_price) * 100, 2) as avg_volatility
FROM daily_stock_prices d
JOIN stock_info s ON d.ts_code = s.ts_code
WHERE d.open_price > 0
GROUP BY d.ts_code, s.name
ORDER BY avg_volatility DESC;
SELECT
DATE(order_time) as order_date,
SUM(order_gmv) as daily_gmv,
COUNT(*) as order_count
FROM order_info
GROUP BY DATE(order_time)
ORDER BY order_date;
select mch_nm from cmb_usr_trx_rcd where usr_id in ('5201314520', '5211314521') and year(trx_time) = 2024 group by mch_nm having count(distinct usr_id) = 2；很棒的思路。记得加distinctWITH total_score AS ( SELECT s.class_code, COUNT(DISTINCT s.student_id), SUM(sc.score) AS sum, t.name FROM students s INNER JOIN scores sc ON s.student_id = sc.student_id INNER JOIN teachers t ON s.class_code = t.head_teacher GROUP BY s.class_code, t.name ) select * from total_score 。你with创建了一个子查询，后面没有select动作了，当然会报错了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区分好，只要学过初中英语你就能会啊！