2026-04-29
绘制小时进入人数曲线
select lpad(hour(enter_time), 2, '0') as hour_entered,count(*) enter_count
from ks_live_t1
group by 1
order by 1
2026-04-29
HAVING子句中的子查询
select mch_nm,sum(trx_amt) mch_total_amt
from cmb_usr_trx_rcd
group by 1
having sum(trx_amt) > ((select avg(trx_amt) from cmb_usr_trx_rcd)*10)
2026-04-29
HAVING子句中的子查询
select mch_nm,sum(trx_amt) mch_total_amt
from cmb_usr_trx_rcd
group by 1
having (select sum(trx_amt) from cmb_usr_trx_rcd) > ((select avg(trx_amt) from cmb_usr_trx_rcd)*10)
2026-04-29
NOT IN子查询
select *
from cmb_usr_trx_rcd
where usr_id not in (select usr_id from cmb_usr_trx_rcd where mch_nm='肯德基')
2026-04-29
IN子查询
select * from cmb_usr_trx_rcd
where usr_id in (select usr_id from cmb_usr_trx_rcd where mch_nm='肯德基')
order by usr_id
2026-04-29
IN子查询
select * from cmb_usr_trx_rcd
where mch_nm='肯德基'
order by usr_id
2026-04-29
WHERE子查询 + 比较运算符
select * from cmb_usr_trx_rcd
where trx_amt > (select avg(trx_amt) from cmb_usr_trx_rcd)
order by trx_amt desc
2026-04-29
基础标量子查询-不带分组
select *,(select avg(trx_amt) from cmb_usr_trx_rcd) avg_trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520
order by trx_time desc
2026-04-29
基础标量子查询-不带分组
select usr_id, mch_nm,trx_time,trx_amt,avg(trx_amt) avg_trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520
group by usr_id,mch_nm,trx_time,trx_amt
2026-04-29
替换空格
select replace(singer_name,' ','') new_singer_name
from singer_info
2026-04-29
美狗计
select replace(song_name,'人','狗') new_song_name
from song_info
where song_name like '%人%'
2026-04-29
名字中字母e左起小于等于3位的歌手
select singer_name,case when locate('e', singer_name) in(1,2,3) then 1 else 0 end as if_e_lessthan3
from singer_info
2026-04-29
名字中字母e左起小于等于3位的歌手
select singer_name,case when locate('e', singer_name) then 1 else 0 end as if_e_lessthan3
from singer_info
group by 1
2026-04-29
名字中字母e左起小于等于3位的歌手
select singer_name,case when locate('e', singer_name) then 1 else 0 end as if_e_lessthan3
from singer_info
2026-04-29
按歌手名字字符长度统计歌手个数
select length(singer_name), count(singer_id)
from singer_info
group by 1
2026-04-28
统计字符长度
select singer_name,char_length(singer_name) len
from singer_info
2026-04-28
歌手名字大写
select upper(singer_name) as uppered_name
from singer_info
2026-04-28
基于共同兴趣爱好的餐厅推荐（1）-我吃过啥
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid='MT10000'
group by 2
order by 2
2026-04-28
基于共同兴趣爱好的餐厅推荐（1）-我吃过啥
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid='MT10000'
order by 2
2026-04-28
找出与X轴交点小于等于0的一元一次函数
select
*
from
numbers_for_fun
where
a=0 and b<>0 and b*c>=0
order by
id