我有三张桌子.用户帐户、IncomingSequences和AnnotatedEntences.注释员对输入的句子进行注释，并标记意图.然后，管理员判断这些标记，并对标记的意图进行更正.
DB-Fiddle Playground link: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=00a770173fa0568cce2c482643de1d79
假设我自己是管理员，我想根据注释器提取错误报告.
My tables are as follows:
User Accounts table:
| userId | userEmail | userRole |
|---|---|---|
| 1 | user1@gmail.com | editor |
| 2 | user2@gmail.com | editor |
| 3 | user3@gmail.com | editor |
| 4 | user4@gmail.com | admin |
| 5 | user5@gmail.com | admin |
Incoming Sentences Table
| sentenceId | sentence | createdAt |
|---|---|---|
| 1 | sentence1 | 2021-01-01 |
| 2 | sentence2 | 2021-01-01 |
| 3 | sentence3 | 2021-01-02 |
| 4 | sentence4 | 2021-01-02 |
| 5 | sentence5 | 2021-01-03 |
| 6 | sentence6 | 2021-01-03 |
| 7 | sentence7 | 2021-02-01 |
| 8 | sentence8 | 2021-02-01 |
| 9 | sentence9 | 2021-02-02 |
| 10 | sentence10 | 2021-02-02 |
| 11 | sentence11 | 2021-02-03 |
| 12 | sentence12 | 2021-02-03 |
Annotated Sentences Table
| id | annotatorId | sentenceId | annotatedIntent |
|---|---|---|---|
| 1 | 1 | 1 | intent1 |
| 2 | 4 | 1 | intent2 |
| 3 | 2 | 2 | intent4 |
| 4 | 3 | 4 | intent4 |
| 5 | 1 | 5 | intent2 |
| 6 | 3 | 3 | intent3 |
| 7 | 5 | 3 | intent2 |
| 8 | 1 | 6 | intent4 |
| 9 | 4 | 6 | intent1 |
| 10 | 1 | 7 | intent1 |
| 11 | 4 | 7 | intent3 |
| 12 | 3 | 9 | intent3 |
| 13 | 2 | 10 | intent3 |
| 14 | 5 | 10 | intent1 |
Expected Output:
我想要一个输出作为一个表，它提供关于每个编辑器注释的句子总数的信息，以及管理员在编辑器注释的句子之上更正的句子总数.我不想在同一个表中查看管理员标记的计数.如果同时出现，则total admin corrected应返回0.
|userEmail |totalTagged|totalAdminCorrected|
|---------------|------------|---------------------|
|user1@gmail.com| 4 | 3 |
|user2@gmail.com| 2 | 1 |
|user3@gmail.com| 3 | 1 |
我已经尽力了.你可以在DB小提琴中看到这一点
我的查询没有产生预期的输出.请求你的帮助来实现这一点.