SQL从头表中选择，其中明细表行具有多个值 [英] SQL select from header table where detail table rows have multiple values

问题描述

我正在尝试将查询合并在一起以获取报告的数据.

I am trying to put together a query to fetch data for a report.

我不是SQL的新手，但是我承认我实际上只是很少使用它.我有一小袋花样，可以成功解决.

I am not a newcomer to SQL, but I will admit that I really only make very light use of it. I have a small bag of tricks which I manage to make go a long way.

我感觉这一次可能需要嵌套查询才能实现我的目标，这是我以前从未尝试使用过的.

I have a feeling that I might be needing a nested query to accomplish my goal this time, which I have never attempted to use before.

这是我的问题的极为简化的版本.

This is an extremely simplified version of my problem.

我有2个表:employee-header和employee-details.

这些表之间是一对多的关系(1个标头包含许多详细信息)

The is a one to many relationship between these tables (1 header to many details)

标题包含有关该员工的联系信息，每位员工一行，每位员工均带有id-主键.

The header contains contact information about the employee, one row per employee, each employee with an id - the primary key.

详细信息表包含每个员工多行，每行代表该员工实现的training-type的记录.每行都从表头表中引用员工的id.

The details table contains multiple rows per employee, each representing a record of a training-type achieved by the employee. Each row references the employee's id from the header table.

我的目标是在雇员具有多个training-types ...的地方选择一个雇员及其培训类型的列表，即该雇员同时具有training-type-1和trainingg-type-2

My goal is to SELECT a list of employees and their training types WHERE the employee has multiple training-types... i.e. the employee has both training-type-1 and traing-type-2

我想让我感到困扰的是，可以有任意数量的训练类型，因此，不是每种训练类型都有自己的列，而是有自己的行.

I guess the thing that's tripping me up is that there can be an arbitrary number of training types, so instead of each training type having it's own column, they instead have their own row.

我试图使我的问题尽可能清楚，并使其简化以使其可实现……事实是，它实际上是更复杂的既有查询的一部分，该查询还执行多个日期操作在详细信息行上查看培训是否有效，已过期或已存档(自过期以来已重新获得的过期培训项目).我很乐意提供更多必要的信息.

I've tried to make my problem as clear as possible, and simplify it enough to make it attainable... the truth is that it's actually one part of a much more complex pre-existing query that also performs several date operations on the detail row to see whether the training is valid, expired, or archived (an expired training item that has been reattained since expiry). I am happy to provide any more information that could help if required.

如何获取符合我需要的结果集?

How would I be able to fetch a result set that meets my needs?

为了使我的问题更清楚，这里有一些示例数据说明了这种情况.

In an attempt to make my problem clearer, here is some sample data that illustrates the situation.

employee-header表:

emp-id | employee-name
------------------
   1   | Employee A
   2   | Employee B
   3   | Employee C

employee-detail表:

det-id | emp-id | training-type
-------------------------------
   1   |   1    | forklift
   2   |   1    | welding
   3   |   1    | lifting
   4   |   2    | forklift
   5   |   2    | lifting
   6   |   3    | welding
   7   |   3    | forklift

因此，如果我想从标题中选择emp-id和employee-name，对于所有training-type均为铲车"和焊接"的员工，我的查询将是什么.

So if I wanted to select the emp-id and employee-name from the header, of all employees that had a training-type of both "forklift" AND "welding" what would my query need to be.

推荐答案

这可以通过子查询来实现:

This can be achieved by a sub-query:

SELECT h.id, h.employee_name, d.training-type
FROM employee-header h INNER JOIN employee-details d ON h.id = d.id
WHERE h.id IN -- here is the sub-query
(SELECT id from employee-details 
 WHERE training-type IN ('forklift','welding') 
 GROUP BY id HAVING COUNT(DISTINCT training-type) = 2)

当您需要在比赛中选择确切的数字时，可以在选择标准中使用IN子句，然后仅使用与请求值的数量匹配的HAVING COUNT(DISTINCT训练类型)

When you need to select the exact number on matches you can use IN clause in the selection criteria and then just use the HAVING COUNT(DISTINCT training-type) matching the number of requested values

这篇关于SQL从头表中选择，其中明细表行具有多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！