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;
select
sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt
, count(1)/2 as ttl_cnt
,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p
from hand_permutations
select
left(purchase_time,7) as mon
,case when product_type='iPhone' then 'iPhone'
else 'Not iPhone' end as category
,count(distinct user_id)
from
apple_pchs_rcd
group by
mon,category
select
substr(purchase_time,1,7) as mon
,case when product_type='iPhone' then 'iPhone'
else 'Not iPhone' end as category
,count(distinct user_id)
from
apple_pchs_rcd
group by
mon,category
select
substr(purchase_time,1,7) as mon
,case when product_type='iPhone' then 'iPhone'
else 'Not iPhone' end as category
,count(distinct user_id)
from
apple_pchs_rcd
group by
1,2
select
substr(purchase_time,1,7) as mon,
case when product_type='iPhone' then 'iPhone'
else 'Not iPhone' end as category,
count(distinct user_id)
from
apple_pchs_rcd
group by 1,2
WITH user_monthly_stats AS (
SELECT
DATE_FORMAT(purchase_time, '%Y-%m') AS month,
user_id,
MAX(CASE WHEN product_type = 'iPhone' THEN 1 ELSE 0 END) AS bought_iphone,
MAX(CASE WHEN product_type <> 'iPhone' THEN 1 ELSE 0 END) AS bought_other
FROM apple_pchs_rcd
GROUP BY DATE_FORMAT(purchase_time, '%Y-%m'), user_id
)
SELECT
month,
SUM(bought_iphone) AS iphone_users,
SUM(bought_other) AS other_users
FROM user_monthly_stats
GROUP BY month
ORDER BY month;
SELECT
substr(purchase_time, 1,7) AS mon,
CASE
WHEN product_type = 'iPhone' THEN 'iPhone'
ELSE 'Not iPhone'
END AS category,
COUNT(DISTINCT user_id) AS user_count
FROM apple_pchs_rcd
GROUP BY mon, category
ORDER BY mon, category;
WITH search_click AS (
SELECT
q.usr_id,
q.key_word,
c.click_page_id,
q.search_tm,
q.session_id
FROM jx_query_rcd q
LEFT JOIN jx_click_rcd c ON q.usr_id = c.usr_id AND q.session_id = c.session_id
)
SELECT
COUNT(search_tm) AS total_searches,
COUNT(click_page_id) AS total_clicks,
ROUND((COUNT(click_page_id) / NULLIF(COUNT(search_tm), 0)) * 100, 2) AS click_rate
FROM search_click;
SELECT YEAR(list_date) AS Y, COUNT(1) AS cnt
FROM stock_info
WHERE ts_code LIKE '%SZ'
AND name LIKE '%银行%'
GROUP BY YEAR(list_date)
ORDER BY YEAR(list_date) ;
WITH user_events AS (
SELECT
live_id,
enter_time AS event_time,
1 AS user_change
FROM ks_live_t1
UNION ALL
SELECT
live_id,
leave_time AS event_time,
-1 AS user_change
FROM ks_live_t1
),
online_timeline AS (
SELECT
live_id,
event_time,
SUM(user_change) OVER (
PARTITION BY live_id
ORDER BY event_time, user_change DESC
) AS current_online
FROM user_events
)
SELECT
o.live_id,
l.live_nm,
MAX(o.current_online) AS max_online_users
FROM online_timeline o
LEFT JOIN ks_live_t2 l ON o.live_id = l.live_id
GROUP BY o.live_id, l.live_nm, l.live_type
ORDER BY max_online_users DESC;
SELECT
u.user_id,
DAYNAME(lr.start_time) AS day_of_week,
COUNT(*) AS listens_per_day
FROM
qqmusic_user_info u
JOIN
listen_rcd lr ON u.user_id = lr.user_id
GROUP BY
u.user_id, day_of_week
ORDER BY
u.user_id ASC, day_of_week ASC;
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 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区分好，只要学过初中英语你就能会啊！