postgresql - 在PostgreSQL中1为空时比较2个表(Compare 2 Tables When 1 Is Null in PostgreSQL)

Question

1. List item

   (项目清单)

I am kinda new in PostgreSQL and I have difficulty to get the result that I want.

(我是PostgreSQL的新手，我很难获得想要的结果。)

In order to get the appropriate result, I need to make multiple joins and I have difficulty when counting grouping them in one query as well.

(为了获得适当的结果，我需要进行多个连接，并且在将它们分组到一个查询中时也遇到困难。)

The table names as following: pers_person, pers_position, and acc_transaction.

(该表的名称如下：pers_person，pers_position和acc_transaction。)

What I want to accomplish is;

(我要完成的是)

1. To see who was absent on which date comparing pers_person with acc_transaction for any record, if there any record its fine... but if record is null the person was definitely absent.

   (要查看谁在哪个日期缺席比较任何记录的pers_person和acc_transaction，如果有任何记录可以罚款...但是如果record为null，则肯定没有该人。)

2. I want to count the absence by pers_person, how many times in month this person is absent.

   (我要按pers_person计数缺席情况，这个人缺席多少个月。)

3. Also the person hired_date should be considered, the person might be hired in November in October report this person should be filtered out.

   (还应考虑该人的hired_date，该人可能会在十月的十一月被录用，并报告此人应被过滤掉。)

4. pers_postition table is for giving position information of that person.

   (pers_postition表用于提供该人的位置信息。)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

('''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''')

SELECT tr.create_time::date AS Date, pers.pin, tr.dept_name, tr.name, tr.last_name, pos.name, Count(*)
FROM acc_transaction AS tr
RIGHT JOIN pers_person as pers
ON tr.pin = pers.pin
LEFT JOIN pers_position as pos
ON pers.position_id=pos.id
WHERE tr.event_no = 0 AND DATE_PART('month', DATE)=10 AND DATE_PART('month', pr.hire_date::date)<=10 AND pr.pin IS DISTINCT FROM tr.pin
GROUP BY DATE
ORDER BY DATE

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

('''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''')

*This is report for octeber, *Pin is ID number

(*这是八月份的报告，* Pin是ID号)

  ask by Shohrat Permanov translate from so

Answer 1

I'd start by

(我将从)

• changing the RIGHT JOIN for a LEFT JOIN as they works the same in reverse but it's confusing to figure them both in mind :

  (改变RIGHT JOIN为LEFT JOIN因为它们的反向工作原理相同，但是要记住它们两者却令人困惑：)

• removing for now the pers_position table as it is used for added information purpose rather than changing any returned result

  (现在删除pers_position表，因为它用于添加信息，而不是更改任何返回的结果)

• there is an unknown alias pr and I'd assume it is meant for pers (?), changing it accordingly

  (有一个未知的别名pr ，我假设它是为pers （？）定义的，请相应地对其进行更改)

• that leads to strange WHERE conditions, removing them

  (导致奇怪的WHERE条件，将其删除)

• • "pers.pin IS DISTINCT FROM pers.pin" (a field is never distinct from itself)

    (“ pers.pin与pers.pin截然不同”（一个字段永远不会与自己不同）)

• • "AND DATE_PART('month', DATE)=10 " (always true when run in october, always false otherwise)

    (“ AND DATE_PART（'month'，DATE）= 10”（十月运行时始终为true，否则始终为false）)

Giving the resulting query :

(提供结果查询：)

SELECT tr.create_time::date AS Date, pers.pin, tr.dept_name, tr.name, tr.last_name, Count(*)
FROM pers_person as pers
LEFT JOIN acc_transaction AS tr ON tr.pin = pers.pin
WHERE tr.event_no = 0 
    AND DATE_PART('month', pers.hire_date::date)<=10 
GROUP BY DATE
ORDER BY DATE

At the end, I don't know if that answers the question, since the title says "Compare 2 Tables When 1 Is Null in PostgreSQL" and the content of the question says nothing about it.

(最后，我不知道这是否回答了问题，因为标题为“比较2表，其中PostgreSQL中1为空时比较2个表”，而问题的内容对此一无所知。)