根据日期表条件使用“左联接"，以便了解谁缺勤 [英] Using Left Join based on date tables condition so that know who employees is absent
问题描述
我有两个像这样的来自microsoft Access Database的表
I have two table From microsoft Access Database like this
1.HR_Personnel
1.HR_Personnel
+-----+----------+----------------------+
| ID | NIP | Name |
+----------------+----------------------+
| 1 | 200 | Teguh |
| 2 | 201 | Supomo |
| 3 | 203 | DHINI ADHITYAS M |
| 4 | 204 | Dhani Amanda |
+-----+----------+----------------------+
TA_Record_Info
TA_Record_Info
+---------+-----------------------+
| Per_Code| Date_Time |
+---------+-----------------------+
| 3 | 2013-02-20 07:45:57 |
| 2 | 2013-02-20 07:46:13 |
| 1 | 2013-02-20 08:48:07 |
| 1 | 2013-02-20 15:53:40 |
| 3 | 2013-02-20 16:01:02 |
| 2 | 2013-02-21 07:31:57 |
| 3 | 2013-02-21 07:39:29 |
| 3 | 2013-02-21 15:51:47 |
| 2 | 2013-02-21 16:11:21 |
| 2 | 2013-02-22 07:47:45 |
| 1 | 2013-02-22 07:53:31 |
| 3 | 2013-02-22 16:01:43 |
| 2 | 2013-02-22 16:11:19 |
| 1 | 2013-02-22 16:15:26 |
+---------+-----------------------+
预期结果编辑
+-------+-----------------+-------------+-------------+-------------+
| NIP | Nama | adate | InTime | OutTime |
+-------+-----------------+-------------+-------------+-------------+
| 201 | Teguh | 2013-02-20 | 08:48:07 | 15:53:40 |
| 202 | Supomo | 2013-02-20 | 07:46:13 | - |
| 203 | DHINI ADHITYAS M| 2013-02-20 | 07:45:57 | 16:01:02 |
| 204 | Dhani Amanda | 2013-02-20 | - | - |
| 201 | Teguh | 2013-02-21 | - | - |
| 202 | Supomo | 2013-02-21 | 07:31:57 | 16:11:21 |
| 203 | DHINI ADHITYAS M| 2012-08-21 | 07:39:29 | 15:51:47 |
| 204 | Dhani Amanda | 2013-02-21 | - | - |
| 201 | Teguh | 2012-08-22 | 07:53:31 | 16:15:26 |
| 202 | Supomo | 2012-08-22 | 07:47:45 | 16:11:19 |
| 203 | DHINI ADHITYAS M| 2012-08-22 | - | 16:01:43 |
| 204 | Dhani Amanda | 2013-02-22 | - | - |
+-------+-----------------+-------------+-------------+-------------+
我尝试使用此查询 EDIT
SELECT p.NIP AS NIP,
p.Name AS Nama,
Format (a.Date_Time, 'yyyy-mm-dd') as adate,
IIF((Min(a.Date_Time) <> Max(a.Date_Time)),
Format (Min(a.Date_Time), 'hh:mm:ss'),
IIF( Format (Min(a.Date_Time), 'hh:mm:ss') < '12:00:00',
Format (Min(a.Date_Time), 'hh:mm:ss'),
'-'
)
)as InTime,
IIF((Max(a.Date_Time) <> Min(a.Date_Time)),
Format (Max(a.Date_Time), 'hh:mm:ss'),
IIF( Format (Max(a.Date_Time), 'hh:mm:ss') > '12:00:00',
Format (Max(a.Date_Time), 'hh:mm:ss'),
'-'
)
)as OutTime
FROM HR_Personnel AS p
LEFT JOIN TA_Record_Info a
ON p.ID = a.Per_Code
GROUP BY p.Per_Code,
p.Per_Name,
Format (a.Date_Time, 'yyyy-mm-dd')
Order BY Format (a.Date_Time, 'yyyy-mm-dd'),
Right(p.Per_Code,2),
p.Per_Name
但结果显示如下 EDIT
+-------+-----------------+-------------+-------------+-------------+
| NIP | Nama | adate | InTime | OutTime |
+-------+-----------------+-------------+-------------+-------------+
| 204 | Dhani Amanda | | | - |
| 201 | Teguh | 2013-02-20 | 08:48:07 | 15:53:40 |
| 202 | Supomo | 2013-02-20 | 07:46:13 | - |
| 203 | DHINI ADHITYAS M| 2013-02-20 | 07:45:57 | 16:01:02 |
| 202 | Supomo | 2013-02-21 | 07:31:57 | 16:11:21 |
| 203 | DHINI ADHITYAS M| 2012-08-21 | 07:39:29 | 15:51:47 |
| 201 | Teguh | 2012-08-22 | 07:53:31 | 16:15:26 |
| 202 | Supomo | 2012-08-22 | 07:47:45 | 16:11:19 |
| 203 | DHINI ADHITYAS M| 2012-08-22 | - | 16:01:43 |
+-------+-----------------+-------------+-------------+-------------+
我认为由于我离开查询而导致的查询结果
I think the results of my query as it caused by i left join using
打开p.ID = a.Per_Code
ON p.ID=a.Per_Code
编辑 因此，不会显示teguh在2013-02-21的日期.表格仅显示了从2013年2月22日至2013年2月22日整体缺席的达尼·阿曼达(Dhani Amanda).
EDIT so teguh are not present at 2013-02-21 date is not displayed. Table only shows Dhani Amanda who was absent from the date 2013-02-22 - 2013-02-22 in overall.
我只想显示在所有特定日期或标记为Intime和OutTime的日期为空"中没有出现雇员会议室"表
I just wanted to show employee roomates table is not present in all the particular date or Dates marked with Intime and OutTime empty
最后，我必须更改查询的内容吗?我希望你能帮助我.谢谢.
Finnally what must i change from my query? I hope you can help me. thanks.
更新
我错了，我在表结果和预期结果表之间上下颠倒地写，然后结果表作为不存在的雇员不存在Teguh，我改变了我的解释.
然后在查询中
I was wrong.I write upside down between the table result and expected result table.And then the result table not present Teguh as employees who are not present.I have change my explain.
And then in the query
SELECT p.NIP AS NIP,
p.Name AS Nama,
更改
SELECT p.NIP AS NIP,
p.Name AS Nama,
我用EDIT标签编辑了我的问题.谢谢.
I have edit my question with EDIT tag. thanks.
推荐答案
您已经获得了以下代码行: 格式(a.Date_Time，'yyyy-mm-dd')为日期，
You've got this line of code: Format (a.Date_Time, 'yyyy-mm-dd') as adate,
您期望为表a中没有与之相关联的日期的人获得一个日期.唯一有效的方法是创建一个新查询(或SELECT语句)，该新查询从表a中选择唯一的日期，而不将其联接回到表p.
And you're expecting to get a date for a person who has no dates associated with them in table a. The only way that's going to work is if you create a new query (or SELECT statement) that selects the unique dates from table a, and NOT JOIN that back to table p.
尝试运行此命令，然后查看是否可以从中构建其余的查询.我的工作有些麻烦，但是您应该可以把它赶到终点线.
Try running this, and then see if you can build the rest of your query off it. I'm a little swamped at work, but you should be able to take it over the finish line.
SELECT QueryDate.ID, QueryDate.NIP, QueryDate.nama, QueryDate.adate, IIf((Min(TA_Record_Info.Date_Time)<>Max(TA_Record_Info.Date_Time)),Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss"),IIf(Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss")<"12:00:00",Format(Min(TA_Record_Info.Date_Time),"hh:mm:ss"),'-')) AS InTime, IIf((Max(TA_Record_Info.Date_Time)<>Min(TA_Record_Info.Date_Time)),Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss"),IIf(Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss")>"12:00:00",Format(Max(TA_Record_Info.Date_Time),"hh:mm:ss"),'-')) AS OutTime
FROM QueryDate LEFT JOIN TA_Record_Info ON (QueryDate.adate=DateValue(TA_Record_Info.Date_Time)) AND (QueryDate.ID=TA_Record_Info.Per_Code)
GROUP BY QueryDate.ID, QueryDate.NIP, QueryDate.nama, QueryDate.adate;
我有一个名为QueryDate的单独查询，由以下内容组成:
I have a separate query called QueryDate which consists of:
SELECT HR_Personnel.ID, HR_Personnel.NIP, HR_Personnel.Name as nama, QDt.UniqueDate as adate
FROM HR_Personnel, (SELECT DateValue([Date_Time]) AS UniqueDate
FROM TA_Record_Info
GROUP BY DateValue([Date_Time])) AS QDt;
如果努力的话，可以将两者结合起来.
You can probably combine the two if you try hard enough.
这篇关于根据日期表条件使用“左联接"，以便了解谁缺勤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
