sql

最新推荐文章于 2024-06-09 23:10:45 发布

striverpan

最新推荐文章于 2024-06-09 23:10:45 发布

阅读量257

点赞数

分类专栏：大数据

本文链接：https://blog.youkuaiyun.com/w616358337/article/details/72875716

版权

大数据专栏收录该内容

8 篇文章

订阅专栏

本文介绍了SQL的基础知识和常用查询技巧，包括简单的SELECT语句、条件查询、多条件组合查询、统计函数使用、分组及HAVING子句、表连接、字符串操作等，为读者提供了丰富的SQL实战案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql是基本功 复杂的sql还是需要时间学习
sql很常用 能完成很多工作
#select * FROM store_information
#SELECT DISTINCT * FROM store_information
#where 查询
#SELECT * from store_information where sales > 1000;
#多条件查询
#SELECT * from store_information where sales > 1000 and store_name LIKE '%A%'
#SELECT * FROM store_information WHERE sales > 1000 OR (sales >500 and sales <200)
#in语句 若in中只有一个值 则等于where ‘字段名’=xxx
#SELECT * from store_information where sales in (1500,200)
#BETWEEN and语句
#SELECT * FROM store_information where sales BETWEEN 100 and 500
#like 语句 通配符 _下划线代表一个字符 * % 代表任意多字符
#SELECT * from store_information where store_name like 'S_%'
#ORDER BY语句 ASC 从小到大 Desc 从大到小
#SELECT * from store_information ORDER BY store_name
#sql统计函数
#SELECT COUNT(*) from store_information
#SELECT AVG(sales) FROM store_information
#SELECT MAX(sales) FROM store_information
#SELECT SUM(sales) from store_information
#分组
#SELECT store_name,SUM(sales) from store_information GROUP BY store_name
#Having 统计函数条件限定
#SELECT store_name,SUM(sales) FROM store_information GROUP BY store_name HAVING SUM(sales) >1000 
#as 别名 字段 表两种
#SELECT store_name,SUM(sales) as total FROM store_information GROUP BY store_name HAVING SUM(sales) >1000 
#连接查询 内联结
#SELECT * from store_information as a1,Geography as a2 where a1.store_name = a2.store_name
#外联结 左联结 右联结 LEFT JOIN on 
#SELECT * FROM store_information as a1 LEFT JOIN Geography as a2 on a1.store_name = a2.store_name 
#SELECT * FROM store_information as a1 RIGHT JOIN Geography as a2 on a1.store_name = a2.store_name 
#字段拼接
#SELECT CONCAT(region_name,'+',store_name) from geography
#获取子串
#SELECT SUBSTR(store_name,3) from store_information
#替换字符串
#SELECT REPLACE (store_name, "An", "B")FROM store_information
#子查询
SELECT a1.store_name ,a1.Sales FROM Store_Information a1 WHERE a1.Store_Name IN (SELECT Store_Name FROM Geography a2 WHERE a2.Store_Name = a1.Store_Name);
#分级分档
create temporary table tmp_label_t_level_pty_d_11 as 
select pty_id,
case  when CYCLE_CNT<=0.1 then '1:低' 
 when CYCLE_CNT>0.1 and CYCLE_CNT<=0.3 then '2:较低' 
 when CYCLE_CNT>0.3 and CYCLE_CNT<=0.7 then '3:中等' 
 when CYCLE_CNT>0.7 and CYCLE_CNT<=0.9 then '4:较高' 
 when CYCLE_CNT>0.9 then '5:高' 
 else '0:未知' end as zhou_zhuan_lv_shui_pin_lvl
 from (
select pty_id,CYCLE_CNT_rank*1.0/tot_cnt as zhou_zhuan_lv_shui_pin_lvl 
from (select pty_id,dense_rank() over(order by CYCLE_CNT desc) as CYCLE_CNT_rank,
count(pty_id) over() as tot_cnt 
from (select pty_id,CYCLE_CNT 
from ana_crmpicture.ds_t_s1_app_cust_char_c_d where 1 = 1 and dt=20170707) t1 ) t2) t3;