sql学习-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44165280/article/details/131272891

本文介绍了SQL中处理空值、数据去重、排序的方法，包括使用IFTEST判断空内容，SELECTDISTINCT进行去重，以及ORDERBY进行排序。还涉及到分组查询、聚合函数如SUM和COUNT的应用，以及HAVING子句的条件过滤。此外，讲解了如何利用子查询进行多表关联和删除重复数据，以及DATEDIFF函数计算日期差和MIN函数查找最早日期。

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

遍历数组

sql遍历in

 void updateMaterialDirIdNullByIds(@Param("ids") List<Long> ids);

    <update id="updateMaterialDirIdNullByIds" parameterType="java.lang.Long">
        UPDATE bi_material
        SET material_dir_id = NULL
        WHERE id IN
        <foreach item="itemId" index="index" collection="ids" open="(" separator="," close=")">
            #{itemId}
        </foreach>
    </update>

返回参

mapper

    /**
     * 查出所有子节点ID查询
     * @param materialDirId
     * @作者 梁伟浩
     * @日期 2024/6/22 15:23 星期六
     * @return Integer
     */
    List<Long> selectChilderIds(Long materialDirId);

  <select id="selectChilderIds" resultType="java.lang.Long" parameterType="java.lang.Long">
    WITH RECURSIVE Subtree AS (
      SELECT id, parent_id
      FROM bi_material_dir
      WHERE id = #{materialDirId} AND is_deleted = 0
      UNION ALL
      SELECT bd.id, bd.parent_id
      FROM bi_material_dir bd
             JOIN Subtree st ON bd.parent_id = st.id
      WHERE bd.is_deleted = 0
    )
    SELECT id
    FROM Subtree;
  </select>

判空

 <if test="isFilterEmptyContent">
              and content IS NOT NULL and content != ''
 </if>

去重，排序

select distinct author_id id from views where viewer_id = author_id order by id;

sql：null值无法与确定的值作比较

分组，倒序，获取最大值

select customer_number
from Orders 
group by customer_number
Order by count(customer_number) desc
limit 1

having聚合条件，等于加条件

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log

INNER JOIN Websites

ON access_log.site_id=Websites.id)

GROUP BY Websites.name

HAVING SUM(access_log.count) > 200;

group by 根据查出的条件，指定分组

在这里插入图片描述

注意联表子查询的时候，该用那张表的id作为条件,查那张表就用那张表的id作为条件

select DISTINCT p.product_id,p.product_name from product p left join sales s on p.product_id = s.product_id
where
s.product_id NOT IN (SELECT product_id FROM Sales WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31')

删除重复电子邮箱，保留最小的id唯一邮箱

delete p1 from person p1,person p2
where p1.email = p2.email and p1.id > p2.id;

子查询，三个表，根据两个表的结构在关联

select name from SalesPerson where sales_id 
not in
 (select sales_id from orders where com_id = (select com_id from company where name = "RED"))

返回值是日期相差的天数函数DATEDIFF

DATEDIFF('2007-12-31','2007-12-30');   # 1
DATEDIFF('2010-12-30','2010-12-31');   # -1

min可以比较时间大小

select player_id,min(event_date) first_login from Activity group by player_id

查询第二高薪水，limit1,1获取第二条哦0,1是第一条，disinct去重，ifNull（查询，null），不存在啧返回null
- ```
select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary
```
查找两个表不同的条件共同的数据可以使用union连接，结果集