SQL查询一个例子：关于where和on的思考

是潮汕的灿灿展吖

于 2024-08-23 11:17:52 发布

阅读量433

点赞数 4

文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_34168515/article/details/141461172

版权

文章目录

一、前言
二、获取周一到周日的数据
三、往日期填写
四、where 和 on 的思考，过滤掉周四的数据

一、前言

关于SQL语句，由于现在AI智能软件的横行，大部分都知道利用这些软件去生成一些SQL。首先可以肯定，这是一个非常好的现象，毕竟真的可以简化工作。

但是有时候，还是要多动脑筋，毕竟靠这个吃饭，自己强大才能立于不败之地

对于该篇的分享，用一个最简单的例子

比如
本周周一到周日，一共7行，每天的数据。

由于该篇文章比较简单，直接下结论：
1、where是处理整张表的，会把数据过滤掉
2、如果使用left join，on会保留左边，只处理右边的数据、如果使用right join，on会保留右边，只处理左边的数据，以此类推

对于新手的建议：
很多时候，数据库作为一个承载数据的容器，提供数据给程序使用。一个好的查询语句，可以让简化大量的程序，而且对于程序来说，一个好的SELECT返回，处理起来也会十分方便。

二、获取周一到周日的数据

通过百度搜索：Sqlserver 查询本周周一到周日

-- 设置一个假设的数据表名称，比如 'your_table_name'
-- 需要根据实际情况替换为你的数据表名称
 
SELECT *
FROM your_table_name
WHERE [DateColumn] >= DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) -- 本周一的日期
  AND [DateColumn] < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 7); -- 下周一的日期

周一和周日行

SELECT '周一' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) AS DAY_NUM
UNION ALL
SELECT '周二' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 1) AS DAY_NUM
UNION ALL
SELECT '周三' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 2) AS DAY_NUM
UNION ALL
SELECT '周四' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 3) AS DAY_NUM
UNION ALL
SELECT '周五' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 4) AS DAY_NUM
UNION ALL
SELECT '周六' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 5) AS DAY_NUM
UNION ALL
SELECT '周日' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 6) AS DAY_NUM

输出

WEEK_NUM  DAY_NUM
周一	2024-08-19 00:00:00.000
周二	2024-08-20 00:00:00.000
周三	2024-08-21 00:00:00.000
周四	2024-08-22 00:00:00.000
周五	2024-08-23 00:00:00.000
周六	2024-08-24 00:00:00.000
周日	2024-08-25 00:00:00.000

以上的 GETDATE() 可以视为变量

三、往日期填写

id name age create_time sex
1	小明	18	2024-08-23 10:04:26.000	男
2	小红	18	2024-08-22 10:04:43.000	男
3	小军	17	2024-08-22 10:05:00.000	男
4	小吕	15	2024-08-21 11:03:41.000	女

SELECT 
WEEK_NUM,
DAY_NUM,
age
FROM
(SELECT '周一' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) AS DAY_NUM
UNION ALL
SELECT '周二' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 1) AS DAY_NUM
UNION ALL
SELECT '周三' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 2) AS DAY_NUM
UNION ALL
SELECT '周四' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 3) AS DAY_NUM
UNION ALL
SELECT '周五' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 4) AS DAY_NUM
UNION ALL
SELECT '周六' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 5) AS DAY_NUM
UNION ALL
SELECT '周日' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 6) AS DAY_NUM) AS t1
LEFT JOIN 
(
SELECT
CONVERT(varchar, create_time, 23) as 'day',
SUM(age) as age
FROM
TJ_USERNAME
WHERE create_time >= DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
  AND create_time < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 7)
GROUP BY
	CONVERT(varchar, create_time, 23)
) AS t2 ON CONVERT(varchar, t1.DAY_NUM, 23) = t2.day

结果

WEEK_NUM DAY_NUM age
周一	2024-08-19 00:00:00.000	
周二	2024-08-20 00:00:00.000	
周三	2024-08-21 00:00:00.000	15
周四	2024-08-22 00:00:00.000	35
周五	2024-08-23 00:00:00.000	18
周六	2024-08-24 00:00:00.000	
周日	2024-08-25 00:00:00.000

四、where 和 on 的思考，过滤掉周四的数据

where 是直接处理整个返回结果的，会导致周四那一行消失掉

如果使用left join 的 on，会保留左边的数据，但是右边关于周四的，就会被过滤掉

SELECT 
WEEK_NUM,
DAY_NUM,
age
FROM
(SELECT '周一' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) AS DAY_NUM
UNION ALL
SELECT '周二' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 1) AS DAY_NUM
UNION ALL
SELECT '周三' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 2) AS DAY_NUM
UNION ALL
SELECT '周四' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 3) AS DAY_NUM
UNION ALL
SELECT '周五' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 4) AS DAY_NUM
UNION ALL
SELECT '周六' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 5) AS DAY_NUM
UNION ALL
SELECT '周日' as WEEK_NUM,DATEADD(week, DATEDIFF(week, 0, GETDATE()), 6) AS DAY_NUM) AS t1
LEFT JOIN 
(
SELECT
CONVERT(varchar, create_time, 23) as 'day',
SUM(age) as age
FROM
TJ_USERNAME
WHERE create_time >= DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
  AND create_time < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 7)
GROUP BY
	CONVERT(varchar, create_time, 23)
) AS t2 ON CONVERT(varchar, t1.DAY_NUM, 23) = t2.day AND t2.day != CONVERT(varchar, DATEADD(week, DATEDIFF(week, 0, GETDATE()), 3), 23)

结果

WEEK_NUM DAY_NUM age
周一	2024-08-19 00:00:00.000	
周二	2024-08-20 00:00:00.000	
周三	2024-08-21 00:00:00.000	15
周四	2024-08-22 00:00:00.000	
周五	2024-08-23 00:00:00.000	18
周六	2024-08-24 00:00:00.000	
周日	2024-08-25 00:00:00.000