本文深入探讨了SQL查询中的并集、交集、差集操作，以及如何利用ROW_NUMBER函数进行排序与分页。通过实际代码示例，展示了在HR员工表与销售客户表中查询国家、地区和城市信息的方法，包括并集查询、交集查询、差集查询以及排序与分页技术。
use tsql2012;
select country, region, city from hr.employees
union all
select country, region, city from sales.customers;
select country, region, city from hr.employees
union
select country, region, city from sales.customers;
select country, region, city from hr.employees
intersect
select country, region, city from sales.customers;
select row_number() over(partition by country, region, city order by (select 0)) as rownum,
country, region, city
from hr.employees
intersect
select row_number() over(partition by country, region, city order by (select 0)) as rownum,
country, region, city
from sales.customers;
with intersect_all
as
(select row_number() over(partition by country, region, city order by (select 0)) as rownum,
country, region, city
from sales.customers
intersect
select row_number() over(partition by country, region, city order by (select 0)) as rownum,
country, region, city
from hr.employees)
select country, region, city from intersect_all;
select country, region, city from hr.employees
except
select country, region, city from sales.customers;
select country, region, city from sales.customers
except
select country, region, city from hr.employees;
with except_all
as
(select row_number() over(partition by country, region, city order by (select 0)) as rownum,
country, region, city
from hr.employees
except
select row_number() over(partition by country, region, city order by(select 0)) as rownum,
country, region, city
from sales.customers)
select country, region, city from except_all;
select country, count(*) as numlocations
from (select country, region, city from hr.employees
union
select country, region, city from sales.customers) as U
group by u.country;
select empid, orderid, orderdate
from (select top(2) empid, orderid, orderdate
from sales.orders
where empid = 3
扫一扫
2335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
