sql - 当信息在列中时，仅将两个表中的一个表信息返回到行中

Question

在 PostgreSQL 中，我有 2 个带有字段的表：

• Working_date : id(autonumeric), employee_code(varchar (6)), working_date(date), working_hour(time),
• 出勤：id(autonumeric), employee_code(varchar (6)), attendance_date(date), attendance_hour(time),

数据示例：

工作日期

ID  | employee_code | working_date | working_hour
1   | 12345         | 2015-07-09   | 08:00
2   | 12345         | 2015-07-09   | 13:00
3   | 12345         | 2015-07-09   | 14:00
4   | 12345         | 2015-07-09   | 17:00
5   | 12345         | 2015-07-10   | 08:00
6   | 12345         | 2015-07-10   | 13:00
7   | 12345         | 2015-07-10   | 14:00
8   | 12345         | 2015-07-10   | 17:00
9   | 12345         | 2015-07-11   | 08:00
10  | 12345         | 2015-07-11   | 13:00
11  | 12345         | 2015-07-11   | 14:00
12  | 12345         | 2015-07-11   | 17:00
13  | 12345         | 2015-07-12   | 08:00
14  | 12345         | 2015-07-12   | 13:00
15  | 12345         | 2015-07-12   | 14:00
16  | 12345         | 2015-07-12   | 17:00
17  | 12345         | 2015-07-13   | 08:00
18  | 12345         | 2015-07-13   | 13:00
19  | 12345         | 2015-07-13   | 14:00
20  | 12345         | 2015-07-13   | 17:00

出勤率

ID  | employee_code | attendance_date | attendance_hour
1   | 12345         | 2015-07-09      | 07:56:53
2   | 12345         | 2015-07-09      | 10:33:31
3   | 12345         | 2015-07-09      | 13:00:42
4   | 12345         | 2015-07-09      | 13:00:47
5   | 12345         | 2015-07-09      | 13:30:21
6   | 12345         | 2015-07-09      | 17:00:01
7   | 12345         | 2015-07-10      | 07:48:35
8   | 12345         | 2015-07-10      | 12:15:20
9   | 12345         | 2015-07-10      | 13:58:42
10  | 12345         | 2015-07-10      | 17:02:00
11  | 12345         | 2015-07-11      | 08:06:46
12  | 12345         | 2015-07-11      | 12:00:01
13  | 12345         | 2015-07-11      | 13:52:01
14  | 12345         | 2015-07-11      | 17:05:08
15  | 12345         | 2015-07-12      | 07:55:02
16  | 12345         | 2015-07-12      | 12:03:22
17  | 12345         | 2015-07-12      | 13:37:40
18  | 12345         | 2015-07-12      | 17:05:01
19  | 12345         | 2015-07-13      | 07:54:25
20  | 12345         | 2015-07-13      | 10:44:15
21  | 12345         | 2015-07-13      | 13:59:21
22  | 12345         | 2015-07-13      | 17:01:17

在“出勤”表中有一些重复的行，因为员工多次输入出勤。例如，在 2015-07-09 有 2 个出勤时间（13:00:42、13:00:47）该出去吃午饭了。在这种情况下，我应该只得到两条记录之一。

2015-07-09 的另一个案例是 10:33:31。当员工请求允许离开工作，然后在这种情况下于 13:00:42 / 13:00:47 返回时，就会记录下来。

有没有办法让working_date,working_hour其各自attendance_hour在一个表中，只有纯 SQL 查询（可能是某种类型的子查询）？

例子：

ID  | employee_code | working_date | working_hour1 | attendance_time_1 | working_hour2 | attendance_time_2    | working_hour3 | attendance_time_3 | working_hour4 | attendance_time_4
1   | 12345         | 2015-07-09   | 08:00         | 07:56:53          | 13:00:00      | 13:00:42 or 13:00:47 | 14:00         | 13:30:21          | 17:00         | 17:00:01
2   | 12345         | 2015-07-10   | 08:00         | 07:48:35          | 13:00:00      | 12:15:20             | 14:00         | 13:58:42          | 17:00         | 17:02:00
3   | 12345         | 2015-07-11   | 08:00         | 08:06:46          | 13:00:00      | 12:00:01             | 14:00         | 13:52:01          | 17:00         | 17:05:08
4   | 12345         | 2015-07-12   | 08:00         | 07:55:02          | 13:00:00      | 12:03:22             | 14:00         | 13:37:40          | 17:00         | 17:05:01
5   | 12345         | 2015-07-13   | 08:00         | 07:54:25          | 13:00:00      | 10:44:15             | 14:00         | 13:59:21          | 17:00         | 17:01:17

如果无法通过纯 SQL 查询获得它，那么它如何使用 PL/PGSQL 来实现？

目前我用 PHP 做到这一点：

• 我从表中查询employee_code和字段。此查询在 2 个日期之间运行：, .working_dateworking_datefrom_dateto_date
• 在“for 语句”中，我咨询了 working_date 的每一行所有 working_hour 行：working_hour1, working_hour2, working_hour3, working_hour4。对于每一行都运行一个 SQL 查询。对于这个查询，我发送它employee_code和working_date参数。
• 在嵌套的“for 语句”中，使用 every working_hour，我使用参数运行对“attendance_date”表的查询employee_code：working_date和working_hour。它返回attendance_hourfor each working_hour。

这种方式（使用嵌套的“for 语句”从 PHP 调用 SELECTS）对于获取和显示信息来说太慢了。我在执行它时看到了该进程，并且该进程占用了 100% 的 CPU。

标签： sqlpostgresqlplpgsql

Answer 1

您可以按日期连接这些表，并通过按日期和employee_code 分组将出勤时间汇总到一个数组中，如下所示：

SELECT
w.employee_code, 
w.working_date,
array_agg(distinct(w.working_hour)) working_hours,
array_agg(distinct(a.attendance_hour)) attendance_hours
FROM Working_date w
LEFT JOIN attendance a
ON (w.working_date = a.attendance_date)
GROUP BY w.working_date, w.employee_code
ORDER BY w.working_date

您可以使用 postgres 的unnest()函数来取消嵌套这些数组，但它会将它们放入新行而不是列中。将它们放入单独的列中很困难，因为这些数组的长度可能不同，并且所有行都必须具有相同的列。

这是一个小提琴 http://sqlfiddle.com/#!15/2a75c/7/0