sql server 组合查询 in sql 合并查询

文章目录

• 关联查询
• 合并查询
• 一、内连接查询（INNER JOIN | ON)
• 二、三表连接
• 三、左外连接（LEFT JOIN ON)

• 3.1 leetcode例题
• 3.2 on和where的区别
• 3.3 去重查询

• 四、右外连接（RIGHT JOIN ON)
• 五、总结
• 六、数据类型

关联查询

例题如下

select count(*)
from t_activity a 
join t_user u1 on a.activity_owner = u1.user_id
join t_user u2 on a.activity_create_by = u2.user_id
left join t_user u3 on a.activity_edit_by = u3.user_id
where
a.activity_name like "%Lex%"
and 
u1.u_login_act like "%xx%"

上面的查询代码，查的是某个市场活动，查t_activity表（并设置别名为a，简写）
关联查询t_user表（设置别名为u1），没有on后面的条件，a表中每一行数据都对应user表中的全部数据
【联系实际就是一个市场活动表，我想要查这个活动负责人的信息，给a表一个owner列，值与其负责人的user_id相等，方便查询】
on后面是过滤这些数据，留下u1表中user_id列与a表中activity_owner（市场活动所有者）列相等的数据
【联系实际，也就是不是每一个用户都会去负责市场活动，所以a表要的数据是当前负责人的个人信息】
再次过滤，查的是市场活动创建者的个人信息，再次过滤，查的是市场活动编辑者的个人信息，查的是市场活动名字有Lex且这个市场活动负责人的u_login_act包含xx的

合并查询

使用合并查询必须保证合并的两张表个的列数相同，列的数据类型可以不同
比如合并的两张表是两个班级的学生信息表，列名都为id和name
则可以合并这两种表

SELECT * FROM t1 UNION SELECT * FROM t2;

保留重复记录

SELECT * FROM t1 UNION ALL SELECT * FROM t2;

一、内连接查询（INNER JOIN | ON)

SELECT * FROM t_employees
INNER JOIN t_jobs
ON t_employees.`job_id` = t_jobs.`job_id`;

二、三表连接

SELECT * FROM t_employees
INNER JOIN t_departments
ON t_employees.`department_id` = t_departments.`dpID`
INNER JOIN t_jobs
ON t_jobs.`job_address` = t_departments.`dpID`;

三、左外连接（LEFT JOIN ON)

左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果，匹配不到，则返回NULL值填充

SELECT employee_id,first_name,salary,department_id 
FROM t_employees
LEFT JOIN t_departments
ON t_departments.`dpID` = t_employees.`department_id`;

上诉代码查询t_employees表和t_departments表，连接的过程当中用的是左连接，也就是LEFT JOIN，左连接就是以左表为主表去向右表去匹配，匹配到就显示这个完整的结果，如果左表t_employees对应的t_departments表没有结果则显示NULL

3.1 leetcode例题

解题如下：

select p.FirstName,p.LastName,a.City,a.State
from Person p
LEFT JOIN Address a
on p.PersonId = a.PersonId;

一开始我用错了用了where做条件判断

3.2 on和where的区别

on后不管条件是否为真，都显示左边表的记录。
where则过滤掉条件不为真的左边表的记录。

对于非主键字段要进行去重处理
如下：

select a.FirstName,a.LastName,b.City,b.State 
from Person a 
left join 
(select PersonId,City,State,row_number()over(partition by PersonId order by AddressId desc) as nt from Address ) b 
on a.PersonId = b.PersonId 
and b.nt = 1

row_number() over()分组排序功能：
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

over()里
Partition By 后的字段代表根据哪个字段分组，
Order By 后的字段标识用哪个字段排序
得出的列以nt作为字段名
最后根据on后面的b.nt=1取每个分组的第一个数据。

以下是常用sql关键字的优先级

from > where > group by > having > order by

而partition by应用在以上关键字之后，实际上就是在执行完select之后，在所得结果集之上进行partition
partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序，而group by则只保留参与分组的字段和聚合函数的结果。

partition by常同row_number() over一起使用
‘’
partition by和group by的区别和对比

3.3 去重查询

ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）

oracle rownumber over partition by,row_number() over partition by去重复

四、右外连接（RIGHT JOIN ON)

SELECT employee_id,first_name,salary,department_id 
FROM t_employees
RIGHT JOIN t_departments
ON t_departments.`dpID` = t_employees.`department_id`;

五、总结

inner join：2表值都存在
outer join：附表中值可能存在null的情况。
总结：
　　①A inner join B：取交集
　　②A left join B：取A全部，B没有对应的值，则为null
　　③A right join B：取B全部，A没有对应的值，则为null
　　④A full outer join B：取并集，彼此没有对应的值为null
　　上述4种的对应条件，在on后填写。

六、数据类型

类型	大小	范围	格式	用途
int	4字节	-2147483648~2147483647		整数
double	8字节	-1.797E+308~-2.22E-308		双精度浮点数
double（M，D）		M表示整数位和小数位的总和，D表示小数位		双精度浮点数
decimal（M，D）		M最大值为65		小数值
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3		HH:MM:SS	时间值
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期和时间
char		0~255字符		定长字符串char(10)个字符
VARCHAR		0~65535字节		变长字符串varchar(10)10个字符
BLOB		0~65535字节		二进制形式的长文本数据
TEXT		0~65535字节		长文本