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
year(list_date) as Y,
count(distinct ts_code)as cnt
from stock_info
where year(list_date) between 2021 and 2024 and name rlike '.*(医|药|生物).*'
group by 1
order by 1
select ts_code,symbol,name,area,industry,list_date from
(
select
*,
row_number()over(partition by industry order by list_date) as ranking
from stock_info
)t
where t.ranking=1
select ts_code,symbol,name,area,industry,list_date from
(
select
*,
row_number()over(partition by industry order by list_date) as ranking
from stock_info
)t
where t.ranking=1
order by t.list_date;
SELECT
s.ts_code,
s.symbol,
s.name,
s.area,
s.industry,
s.list_date
FROM stock_info s
JOIN (
SELECT
industry,
MIN(list_date) as min_list_date
FROM stock_info
GROUP BY industry
) earliest ON s.industry = earliest.industry AND s.list_date = earliest.min_list_date
order by list_date
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
SELECT
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
SELECT
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
group by
gd.gd_id,
gd.gd_nm,
gd.gd_typ
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
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
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')<='05:00:00' )
order by trx_time
select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and (format(trx_time,'%H:%i:%s') >='22:00:00' or format(trx_time,'%H:%i:%s')<='05:00:00' )
order by trx_time
select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and (format(trx_time,'%H-%i-%s') >='22:00:00' or format(trx_time,'%H-%i-%s')<='05:00:00' )
order by trx_time
WITH ranked_scores AS (
SELECT
s.student_id,
s.name,
sc.score,
ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS rnk
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE s.grade_code = 'S1' AND sc.subject = '物理'
)
SELECT student_id, name, score, rnk
FROM ranked_scores
WHERE rnk <= 10
ORDER BY rnk,student_id
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
with df as (
select s.class_code,
s.student_id,
case
when score>110 then 'excellent'
when score>=90 then 'good'
when score>=60 then 'well'
when score<60 then 'fail'
end as def
from students s
left join scores sc on s.student_id=sc.student_id
where exam_date='2024-06-30'and subject='数学')
select class_code,ttl,
concat(excellent,',',exrate)as exc,
concat(good,',',goodrate)as exc,
concat(well,',',wellrate)as exc,
concat(fail,',',failrate)as exc
from
(select class_code,
count(case when def='excellent'then student_id else null end)as excellent,
count(case when def='good'then student_id else null end)as good,
count(case when def='well'then student_id else null end)as well,
count(case when def='fail'then student_id else null end)as fail,
count(student_id)as ttl,
concat(round(count(distinct case when def='excellent'then student_id else null end)/count(distinct student_id)*100,2),'%') as exrate,
concat(round(count(distinct case when def='good'then student_id else null end)/count(distinct student_id)*100,2),'%' )as goodrate,
concat(round(count(distinct case when def='well'then student_id else null end)/count(distinct student_id)*100,2),'%') as wellrate,
concat(round(count(distinct case when def='fail'then student_id else null end)/count(distinct student_id)*100,2),'%') as failrate
from df
group by class_code)t1
with score_ranges as (
select
st.class_code,
count(*) as class_total,
sum(case when sc.score >= 110 then 1 else 0 end) as excellent,
sum(case when sc.score between 90 and 109 then 1 else 0 end) as good,
sum(case when sc.score between 60 and 89 then 1 else 0 end) as pass,
sum(case when sc.score < 60 then 1 else 0 end) as fail
from
students st
inner join
scores sc on sc.student_id = st.student_id
where
exam_date = '2024-06-30' and sc.subject = '数学'
group by
st.class_code
)
select
class_code,
class_total,
concat(excellent, ' ,', round(100 * excellent / class_total, 2), '%') as excellent,
concat(good, ' ,', round(100 * good / class_total, 2), '%') as good,
concat(pass, ' ,', round(100 * pass / class_total, 2), '%') as pass,
concat(fail, ' ,', round(100 * fail / class_total, 2), '%') as fail
from score_ranges
group by class_code
order by class_code;
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区分好，只要学过初中英语你就能会啊！