SQL相关

已于 2023-04-08 11:12:27 修改
阅读量106 收藏
点赞数
分类专栏： 大数据面试题 文章标签： sql 数据库
于 2022-09-29 09:29:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_49190756/article/details/126642711
版权

计算互相关注的对数

id1 id2

a     b 表示a关注b

select
a.fromID,
b.toID,
if(b.fromID is not null,'Y','N') as is_friend
from table a left join table b on a.fromID = b.toID and a.toID = b.fromID

计算每个视频类型下面播放量前10的视频id

type id timestamp

select
type,
id
from
(
    select
    type,
    id,
    row_number()over(partition by type order by counts desc) as rn
    from
    (
        select
        id,
        type,
        count(timestamp) as counts
        from
        table
        group by id,type
    ) a
)
where a.rn <= 10
;

拉链表

SELECT  *
FROM    (
            SELECT  old.数据
                    ,old.开始时间
                    ,CASE    WHEN new.id IS NULL THEN old.结束时间 
                             ELSE '20220901' 
                     END AS 结束时间
            FROM    old_table old
            LEFT JOIN new_table new
            ON      old.id = new.id

            UNION ALL

            SELECT  new.数据
                    ,'20220901' AS 开始时间
                    ,'99999999' AS 结束时间
            FROM    new_table NEW
        ) 
;

求直播间最大在线人数

room_id user_id start_time end_time

设置一个flag，用户每进入一次，flag设为1，每退出一次，flag设为-1

select
room_id,
max(uv) as max_uv
from
(
    select
    room_id,
    flag_time,
    sum(flag)over(partition by room_id order by flag_time) as uv
    from
    (
        select
        room_id,
        start_time as flag_time,
        1 as flag
        from
        temp
        
        union all
    
        select
        room_id,
        end_time as flag_time,
        -1 as flag
        from
        temp
    ) a
) b
group by
room_id
;

行转列

CREATE TABLE [Inpours]
(
   [ID]                INT IDENTITY(1,1), 
   [UserName]          NVARCHAR(20),  --游戏玩家
    [CreateTime]        DATETIME,      --充值时间    
    [PayType]           NVARCHAR(20),  --充值类型    
    [Money]             DECIMAL,       --充值金额
    [IsSuccess]         BIT,           --是否成功 1表示成功， 0表示失败
    CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)
)
 
INSERT INTO Inpours SELECT '张三', '2010-05-01', '支付宝', 50, 1
 
INSERT INTO Inpours SELECT '张三', '2010-06-14', '支付宝', 50, 1
 
INSERT INTO Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1
 
INSERT INTO Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1
 
INSERT INTO Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1
 
INSERT INTO Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1
 
INSERT INTO Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
       CASE PayType WHEN '支付宝'     THEN SUM(Money) ELSE 0 END AS '支付宝',
       CASE PayType WHEN '手机短信'    THEN SUM(Money) ELSE 0 END AS '手机短信',
       CASE PayType WHEN '工商银行卡'  THEN SUM(Money) ELSE 0 END AS '工商银行卡',
       CASE PayType WHEN '建设银行卡'  THEN SUM(Money) ELSE 0 END AS '建设银行卡'
FROM Inpours
GROUP BY CreateTime, PayType

列转行

CREATE TABLE ProgrectDetail
(
    ProgrectName         NVARCHAR(20), --工程名称
    OverseaSupply        INT,          --海外供应商供给数量
    NativeSupply         INT,          --国内供应商供给数量
    SouthSupply          INT,          --南方供应商供给数量
    NorthSupply          INT           --北方供应商供给数量
)
 
INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL
SELECT 'D', 250, 30, 15, 15
SELECT ProgrectName, 'OverseaSupply' AS Supplier,
        MAX(OverseaSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NativeSupply' AS Supplier,
        MAX(NativeSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'SouthSupply' AS Supplier,
        MAX(SouthSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NorthSupply' AS Supplier,
        MAX(NorthSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName

求任意一门课成绩大于均分的学生

id,course,score

select
id
from
(
    select
    id,
    course,
    score,
    avg()over(partition by course) as avgscore
    from
    (
        select
        id,
        course,
        score
        from
        table
    )
)
where score >= avgscore
group by id
;

求每门课成绩均大于均分的学生

id,course,score

select
id,
sum(flag) as isqualified
from
(
    select
    id,
    course,
    score,
    avgscore
    if(score >= avgscore,0,1) as flag
    from
    (
        select
        id,
        course,
        score,
        avg()over(partition by course) as avgscore
        from
        (
            select
            id,
            course,
            score
            from
            table
        )
    )
)
group by id
having sum(flag)=0
;

求股票的波峰和波谷（非最大值和最小值）

id,code,time,price

id--自增序列 code--股票代码 time--时间 price--价格

 原表stocks：

+----+------+---------------------+-------+
| id | code | time                | price | 
+----+------+---------------------+-------+
| 1  | 1    | 2017-12-14 12:03:06 | 12    | 
| 2  | 1    | 2017-12-14 12:03:07 | 11    | 
| 3  | 1    | 2017-12-14 12:03:08 | 13    | 
| 4  | 1    | 2017-12-14 12:03:09 | 10    | 
| 5  | 1    | 2017-12-14 12:03:10 | 15    | 
| 6  | 2    | 2017-12-14 12:03:06 | 12    | 
| 7  | 2    | 2017-12-14 12:03:07 | 14    | 
| 8  | 2    | 2017-12-14 12:03:08 | 13    | 
| 9  | 2    | 2017-12-14 12:03:09 | 10    | 
| 10 | 2    | 2017-12-14 12:03:10 | 15    | 
+----+------+---------------------+-------+

 tmp_stocks：

select
code,
time,
price,
row_number()over(partition by code order by time) as rn
from
stocks
;
+------+---------------------+-------+----+
| code | time                | price | rn | 
+------+---------------------+-------+----+
| 1    | 2017-12-14 12:03:06 | 12    | 1  | 
| 1    | 2017-12-14 12:03:07 | 11    | 2  | 
| 1    | 2017-12-14 12:03:08 | 13    | 3  | 
| 1    | 2017-12-14 12:03:09 | 10    | 4  | 
| 1    | 2017-12-14 12:03:10 | 15    | 5  | 
| 2    | 2017-12-14 12:03:06 | 12    | 1  | 
| 2    | 2017-12-14 12:03:07 | 14    | 2  | 
| 2    | 2017-12-14 12:03:08 | 13    | 3  | 
| 2    | 2017-12-14 12:03:09 | 10    | 4  | 
| 2    | 2017-12-14 12:03:10 | 15    | 5  | 
+------+---------------------+-------+----+

中间表 

select
*
from
tmp_stocks a
left join
tmp_stocks b
on b.rn = a.rn-1 and a.code = b.code
left join
tmp_stocks c
on c.rn = a.rn+1 and a.code = c.code
;
code | time                | price | rn | code  | time                | price | rn    | code  | time                | price | rn    | 
+------+---------------------+-------+----+-------+---------------------+-------+-------+-------+---------------------+-------+-------+
| 1    | 2017-12-14 12:03:06 | 12    | 1  | <nil> | <nil>               | <nil> | <nil> | 1     | 2017-12-14 12:03:07 | 11    | 2     | 
| 1    | 2017-12-14 12:03:07 | 11    | 2  | 1     | 2017-12-14 12:03:06 | 12    | 1     | 1     | 2017-12-14 12:03:08 | 13    | 3     | 
| 1    | 2017-12-14 12:03:08 | 13    | 3  | 1     | 2017-12-14 12:03:07 | 11    | 2     | 1     | 2017-12-14 12:03:09 | 10    | 4     | 
| 1    | 2017-12-14 12:03:09 | 10    | 4  | 1     | 2017-12-14 12:03:08 | 13    | 3     | 1     | 2017-12-14 12:03:10 | 15    | 5     | 
| 1    | 2017-12-14 12:03:10 | 15    | 5  | 1     | 2017-12-14 12:03:09 | 10    | 4     | <nil> | <nil>               | <nil> | <nil> | 
| 2    | 2017-12-14 12:03:06 | 12    | 1  | <nil> | <nil>               | <nil> | <nil> | 2     | 2017-12-14 12:03:07 | 14    | 2     | 
| 2    | 2017-12-14 12:03:07 | 14    | 2  | 2     | 2017-12-14 12:03:06 | 12    | 1     | 2     | 2017-12-14 12:03:08 | 13    | 3     | 
| 2    | 2017-12-14 12:03:08 | 13    | 3  | 2     | 2017-12-14 12:03:07 | 14    | 2     | 2     | 2017-12-14 12:03:09 | 10    | 4     | 
| 2    | 2017-12-14 12:03:09 | 10    | 4  | 2     | 2017-12-14 12:03:08 | 13    | 3     | 2     | 2017-12-14 12:03:10 | 15    | 5     | 
| 2    | 2017-12-14 12:03:10 | 15    | 5  | 2     | 2017-12-14 12:03:09 | 10    | 4     | <nil> | <nil>               | <nil> | <nil> | 
select a.code,a.time,a.price,
case when b.price is null then "未知"
      when c.price is null then "未知"
      when a.price>b.price and a.price>c.price then "波峰"
      when a.price<b.price and a.price<c.price then "波谷"
else "中间" end as mark,a.rn
from tmp_stocks a 
left join 
tmp_stocks b on a.code=b.code and b.rn=a.rn-1
left join
tmp_stocks c on a.code=c.code and c.rn=a.rn+1 order by a.code,a.rn;

+------+---------------------+-------+--------+----+
| code | time                | price | mark   | rn | 
+------+---------------------+-------+--------+----+
| 1    | 2017-12-14 12:03:06 | 12    | 未知     | 1  | 
| 1    | 2017-12-14 12:03:07 | 11    | 波谷     | 2  | 
| 1    | 2017-12-14 12:03:08 | 13    | 波峰     | 3  | 
| 1    | 2017-12-14 12:03:09 | 10    | 波谷     | 4  | 
| 1    | 2017-12-14 12:03:10 | 15    | 未知     | 5  | 
| 2    | 2017-12-14 12:03:06 | 12    | 未知     | 1  | 
| 2    | 2017-12-14 12:03:07 | 14    | 波峰     | 2  | 
| 2    | 2017-12-14 12:03:08 | 13    | 中间     | 3  | 
| 2    | 2017-12-14 12:03:09 | 10    | 波谷     | 4  | 
| 2    | 2017-12-14 12:03:10 | 15    | 未知     | 5  | 
+------+---------------------+-------+--------+----+

千叶真尹
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
数据分析初学入门——22本SQL学习书籍推荐
Data_lukyo的博客
01-12 7308
很多数据分析初学者都被推荐学SQL，SQL是最基础且是必备的技能之一，但是不知道该从何学起，很多课程讲了很多知识点，但是初学的朋友没有对这一领域整体框架的认识，不知道该学哪些知识、有哪些知识、实际工作需要到什么程度，常常会担心自己该用的没学、学了的没理解或不会用等等。这里通过介绍21本学习SQL的经典书籍（可以说几乎所有人们经典书籍都囊括在内，对初学者、从业者都认可和检验的）。
SQL相关的时间日期类型
zhan_qian的博客
07-16 3万+
SQL相关的时间日期类型: 一、Date（日期类型） 二、Time（时间类型） 三、Datetime（日期时间类型） 四、Timestamp（时间戳类型） 五、Year（年份类型，一个字节）
sql相关日期截取函数
天马行空的创作中心
08-31 4917
长日期字段截取： s.exe_bgn_time timestamp 长度 6 2018-08-31 18:27:04 substring(s.exe_bgn_time,11,6) 11是指从11个字符开始数，6是指截取6个字段。
SQL Server 触发器
PinkCoder
05-27 6万+
SQL Server 触发器
SQL注入
m0_51457307的博客
11-08 2万+
一、什么是SQL注入 SQL注入（SQL lnjection）是发生在Web程序中数据库层的安全漏洞，是比较常用的网络攻击方式之一，他不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至修改数据库。也就是说，SQL注入就是在用户输入的字符串中添加SQL语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的SQL语句就会被数据库服务器误认为是正常的SQL语句而运行，攻击者就可以执行计划外的命令或者访问未授权的数据。 二、SQL注入的原理 1.恶意拼接查
完整版彻底卸载SQL Server2019
Januea的博客
11-18 9万+
重新安装SQL Server2019等要注意的事项
python中sql相关(查询、更新)，以及sql参数化
xiangrikui1155的专栏
09-06 4605
import pymysql def beta_mysql(): connect = pymysql.Connect( host='1.1.1.1', port=3906, user='user', passwd='pwd', db='db', charset='utf8' ) return connect def select(sql): connect=beta_mysql(
Text2Sql相关总结
weixin_41797870的博客
07-30 5488
前段时间了接触到Text2Sql，调研了解了一些，大致整理下。 简介 Text-to-SQL系统能够将自然语言描述转化成对应的SQL查询语句，这项技术能够有效地辅助人们对海量的数据库进行查询。因此，该项研究引起了工业界和学术界的广泛关注。其中，WikiSQL、Spider等大规模标注数据集进一步指出了该技术目前面临的挑战：泛化性（跨领域）、复杂性（SQL语法）、正确性（问题和表格的对齐关系），也促进了一系列后续算法的研究与系统的开发。 在这里，我们给出Text-to-SQL任务一个相对正式的定义：在给定关系
彻底卸载SQL Server
热门推荐
yukichen的博客
02-21 27万+
很多人在面对SQL Server出现的一些无法解决的问题时，会选择卸载重装，但是SQL Server卸载不干净的话，后续安装过程会出现很多问题，因此安全干净的卸载软件是很重要的步骤。 1、在运行中输入services.msc,然后找到所有跟Sql Server有关的服务，并且停止这些服务。（一定要先停止服务再卸载，否则后续卸载文件过程中会失败） 2、进入控制面板，点击“程序卸载” 3...
windows sql server 如何卸载干净？
ha_123_qq的博客
11-19 9万+
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 windows sql server 怎么卸载干净？前言一、windows sql server是什么？二、如何卸载干净1、关闭sql server服务2.到控制面板，卸载sql server3、删除磁盘里的安装文件4、清空注册表总结 前言 安装完windows sql server后，如果只在控制面板上卸载掉相关服务，可能会卸载不干净。 如果再次安装，及时提示你安装成功，但当你开始连接数据库的时候，有可能会导致连接不上，那怎么才能卸载干
SQL相关电子书
02-21
SQL相关电子书，有SQL必知必会，SQL完全应用，有需要可以下载哈，后续也会继续分享相关资料哟
sql相关知识
02-26
项目相关的sql，备份在这个地方，方便以后检查和使用。
SQL相关操作笔记（附习题）
12-14
SQL相关操作 插入 删除 更新 触发器 视图 索引 权限 授权图 插入 ABC 删除 delete from workson where empno=(select empno from employee where empname='\u5218\u56fd\u98ce') and projectno=(select ...
sql学习相关
12-01
这是一本学习linux 的书籍，这本书非常值得看，写的浅显易懂，语言诙谐，让人在愉快的氛围中成长
sqlserver驱动jar
11-23
sqlserver驱动jar java sqlserver驱动jar,java sqlserver驱动jar,java sqlserver驱动jar
【大数据】Spark使用大全:下载安装、RDD操作、JAVA编程、SQL
Joker_ZJN的博客
06-13 966
一文详解Spark的使用
SQLAlchemy 连接池
最新发布
youhebuke225的博客
06-17 124
调整这些参数可以帮助你更好地管理数据库连接，并减少因连接超时或服务器断开连接而导致的问题。记得根据你的应用程序的需求和数据库服务器的配置来合理设置这些参数。在 SQLAlchemy 中，确实可以通过配置连接池的参数来管理连接的超时和回收，从而确保连接在需要时能够被重新建立。SQLAlchemy 使用的是。作为其默认的连接池实现。为了配置这些参数，你可以在创建引擎时将它们传递给。
mysql-线上常用运维sql-2
jll126的博客
06-11 370
在MySQL中，直接查询当前正在执行的事务可能不是非常直观，因为MySQL并没有提供直接的命令或系统视图来列出所有正在运行的事务。但是，你可以通过几种方法间接地获取这些信息。
springboot 酒庄内部管理系统（源码+sql+论文）
u014445459的博客
06-17 613
绪论 1.1 系统研究目的意义 随着信息技术的不断发展，我们现在已经步入了信息化的时代了，而信息时代的代表便是网络技术的日渐成熟，而现在网络已经和我们的生活紧密的联系起来了，我们不敢想象没有网络我们的生活会像怎么样，信息化时代的到来成为不可抗拒的潮流，人类文明正在进入一个崭新的时代。因此，一个好的酒庄信息内部管理系统也以方便、快捷、费用低、绿色环保的优点正慢慢地进入这个行业和领域，将传统的酒庄员工管理方式和酒庄信息的管理彻底的解脱出来，提高效率，减轻工人人员以往繁忙的工作，加速信息的更新速度，使酒庄管
举个sql相关的例子
05-31
好的，比如有一个订单表orders，其中包含订单号(order_id)、客户号(customer_id)、订单总价(total_price)等字段。现在需要查询每个客户的订单总价之和，并按照总价之和从高到低排序，可以使用如下的SQL语句： ``` SELECT customer_id, SUM(total_price) as total FROM orders GROUP BY customer_id ORDER BY total DESC ``` 这条SQL语句会先根据客户号对订单表进行分组，然后对每组的订单总价求和，最后将各个分组的客户号和订单总价之和作为结果集输出，同时按照订单总价之和从高到低排序。这样就可以方便地查询每个客户的订单总价之和，并按照总价之和从高到低排序了。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值