为在某些分支机构工作的人员寻找假期
我有3个数据表:一些人在一个分支机构工作，另一些人在两个或更多分支机构工作。
我们定义了2种情况的假日:类型1所有分支都是关闭的，类型0是某个分支关闭(不是全部)。
现在我想找一个人，如果所有的人-分支机构都关闭了，那么假期是怎么回事
假期
ID Date AllBranch
1 2019/02/01 1
2 2019/02/05 0
3 2019/02/06 0BranchHoliday
ID HolidayID BranchID
1 2 2
2 2 3
3 3 2PersonBranch
ID PersonID BranchID
1 10 2
2 11 2
3 11 3
4 12 2
5 12 4我想要的结果是：
PersonID Hdate
10 2019/02/01
11 2019/02/01
12 2019/02/01
10 2019/02/05
11 2019/02/05
10 2019/02/06In date (2019/02/01) AllBranch为1，因此所有人都在放假
In date (2019/02/05) AllBranch为0，因此：
仅在分支2中的holiday
- PersonID-11工时is 10在假日表中定义，因此在holiday
- PersonID-12 2分支( 2,3 )中定义关闭，在BranchHoliday BranchHoliday表中定义BranchId 2，3，因此在2分支(2，4)中定义关闭，在BranchHoliday中定义关闭，因此不在BranchHoliday
中定义
在日期(2019/02/06)中，AllBranch为0，因此：
- PersonID-10仅在分支2中定义，分支2在假日表中定义，因此在BranchId 2中分支(2，3)中也是如此，并且仅分支3未在BranchHoliday表中定义closed，因此在BranchHoliday表中未定义关闭2分支(2，4)中，分支3未定义为已关闭，因此不在BranchId
中
我需要sql query，而我使用的是SQL Server 2016
回答 2
Stack Overflow用户
发布于 2019-04-24 03:18:28
我不知道你为什么要接受Esteban的答案，因为你的预期结果是这样的。仅6行。
PersonID Hdate
10 2019/02/01
11 2019/02/01
12 2019/02/01
10 2019/02/05
11 2019/02/05
10 2019/02/06然而，Esteban的查询生成了7行。在2月6号假期，它错误地包含了允许11号人在2月6日放假。这与你的问题定义相矛盾：
In date (2019/02/06) AllBranch is 0 so:
* ...
* PersonID-11 work in 2 branch(2,3) and only branch 3 is NOT define closed
in BranchHoliday table so is in NOT holiday假设，个人的BranchHoliday(s)应该检查(加入)与假日的表，对吗？但Esteban的查询并没有做到这一点。
Esteban的查询产生不正确的输出：
实时测试：http://sqlfiddle.com/#!18/86728/2
| PersonID | Date |
|----------|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |
| 11 | 2019-02-06 |给出这些不同的数据：
假期
ID Date AllBranch
1 2019/02/01 1
2 2019/02/05 0
3 2019/02/06 0
4 2019/04/02 0BranchHoliday
ID HolidayID BranchID
1 2 2
2 2 3
3 3 2
4 4 2
5 4 4Esteban的查询再次产生错误的输出：
实时测试：http://sqlfiddle.com/#!18/10348/1
| PersonID | Date |
|----------|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 12 | 2019-02-05 |
| 10 | 2019-02-06 |
| 11 | 2019-02-06 |
| 12 | 2019-02-06 |
| 10 | 2019-04-02 |
| 11 | 2019-04-02 |
| 12 | 2019-04-02 |它应该产生这样的结果：
实时测试：http://sqlfiddle.com/#!17/7b6be/1
| id | date |
|----|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |
| 10 | 2019-04-02 |
| 12 | 2019-04-02 |给出了这些数据。请注意，只有分支机构#5的人员才能在4月2日放假。
假期
ID Date AllBranch
1 2019/02/01 1
2 2019/02/05 0
3 2019/02/06 0
4 2019/04/02 0BranchHoliday
ID HolidayID BranchID
1 2 2
2 2 3
3 3 2
4 4 5Esteban的查询再次生成错误的输出，因为只允许分支机构#5上的人员在4月2日放假。但他的查询包括不应允许在4月2日放假的人员。分支机构#5中不包括人员#10和#11，它们不应在4月2日。
实时测试：http://sqlfiddle.com/#!18/fd1d3/1
| PersonID | Date |
|----------|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |
| 11 | 2019-02-06 |
| 10 | 2019-04-02 |
| 11 | 2019-04-02 |它应该产生这样的结果。4月2日不允许员工放假，10号人员也不允许放假，因为10号人员只在2号分公司。只有5号分部的人员才能休假。
实时测试：http://sqlfiddle.com/#!17/299f73/1
| id | date |
|----|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |这是正确的查询(Postgres版本)：
实时测试：http://sqlfiddle.com/#!17/6cf97/2
select h.date, p.id
from holiday h
cross join person p
join personbranch pb
on p.id = pb.personid
left join branchholiday bh
on h.id = bh.holidayid and pb.branchid = bh.branchid
group by h.date, p.id
having
every(h.allbranch)
or
-- choose only every person whose branch ids are all present in branchholiday
-- filtered by holidayid from holiday.id
every(bh.branchid is not null)
order by h.date, p.id输出与问题定义的预期输出相匹配：
| id | date |
|----|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |SQL Server版本：
实时测试：http://sqlfiddle.com/#!18/41a54/3
select p.id, h.date
from holiday h
cross join person p
join personbranch pb
on p.id = pb.personid
left join branchholiday bh
on h.id = bh.holidayid and pb.branchid = bh.branchid
group by h.date, p.id
having
count(case when h.allbranch = 1 then 1 end) = count(*)
or
-- choose only every person whose branch ids are all present in branchholiday
-- filtered by holidayid from holiday.id
count(case when bh.branchid is not null then 1 end) = count(*)
order by h.date, p.id输出与问题定义的预期输出相匹配：
| id | date |
|----|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |这是SQL Server的另一个every习惯用法。您可以更改count(condition) = count(*) to min方法：
实时测试：http://sqlfiddle.com/#!18/41a54/4
having
min(case when h.allbranch = 1 then 1 else 0 end) = 1
or
-- choose only every person whose branch ids are all present in branchholiday
-- filtered by holidayid from holiday.id
min(case when bh.branchid is not null then 1 else 0 end) = 1 输出与问题定义的预期输出相匹配：
| id | date |
|----|------------|
| 10 | 2019-02-01 |
| 11 | 2019-02-01 |
| 12 | 2019-02-01 |
| 10 | 2019-02-05 |
| 11 | 2019-02-05 |
| 10 | 2019-02-06 |Stack Overflow用户
发布于 2019-04-23 15:01:35
一种解决方案是将其分为两部分：
首先选择AllBranch假日的人员，第二轮选择特定假日的人员，其中他们工作的所有分支机构都有定义的假日：
SELECT AllPers.PersonID, H.Date
FROM Holiday H
INNER JOIN (
SELECT DISTINCT PersonID
FROM PersonBranch
) AllPers ON H.AllBranch = 1
UNION
SELECT Sub.PersonID, H.Date
FROM Holiday H
INNER JOIN (
SELECT PB.PersonID
, SUM(IIF(BH.BranchID IS NULL, 1, 0)) AS AnyBranchOpen
FROM PersonBranch PB
LEFT JOIN BranchHoliday BH ON PB.BranchID = BH.BranchID
GROUP BY PB.PersonID
) Sub ON AnyBranchOpen = 0
WHERE H.AllBranch = 0https://stackoverflow.com/questions/55805443
复制相似问题