然后将查询添加到子查询不会产生任何结果

admin

然后将查询添加到子查询不会产生任何结果

sql

在lastReports本身子查询，返回10行。“包装器”记录确实存在，但是当我加入它们时，我0 rows returned in 12ms from:在SQLite上得到了“记录器”记录。

我的联接很好，我没有犯选择错误（SQL LEFT
JOIN子查询别名）…我缺少什么，还是不明白？

SELECT
    name
FROM
    teachers
INNER JOIN (SELECT
            teacher_id,
            reportingPeriod,
            ReportingType,
            date('now') - 3 as AgeOfReport,
            count(id) as NumberOf
        FROM
            reports
        GROUP BY
            teacher_id
        ORDER BY
            teacher_id ASC,
            reportingPeriod asc
    ) AS lastReports 
ON teachers.id = lastReports.teacher_id;


SAMPLE DATA

TEACHERS
ID NAME 
-----------------
1  Mr John Smith
2  Ms Janet Smith

REPORTS
---------------------------------------------------
ID   TEACHER_ID   REPORTINGPERIOD REPORTINGTYPE
1   1             Jan 2017         Draft
2   1             Feb 2017         Draft
3   2             Jun 2018         Draft
4   2             Jul 2018         Draft
5   1             Mar 2017         Final


DESIRED RESULTS
------------------
Mr John Smith      Final    Mar 2017
Ms Janet Smith     Draft    Jul 2018

阅读 185

2021-07-01

共1个答案

admin

你可以试着让row_number由TEACHER_ID柱和秩序的REPORTINGPERIOD，这意味着在更近的子查询日期，得到rn = 1每个数据TEACHER_ID的最大日期和JOIN上TEACHERS。

CREATE TABLE TEACHERS(
  ID INT,
  NAME VARCHAR(50)
);



INSERT INTO TEACHERS VALUES (1, 'Mr John Smith');
INSERT INTO TEACHERS VALUES (2, 'Ms Janet Smith');


CREATE TABLE REPORTS(
  ID INT,
  TEACHER_ID int,
   REPORTINGPERIOD DATE,
  REPORTINGTYPE varchar(100)
);



INSERT INTO REPORTS VALUES (1,1,'Jan 2017', 'Draft');
INSERT INTO REPORTS VALUES (2,1,'Feb 2017', 'Draft');
INSERT INTO REPORTS VALUES (3,2,'Jun 2018', 'Draft');
INSERT INTO REPORTS VALUES (4,2,'Jul 2018', 'Draft');
INSERT INTO REPORTS VALUES (5,1,'Mar 2017', 'Final');

查询1 ：

SELECT t.NAME,
       t1.REPORTINGTYPE,
       t1.REPORTINGPERIOD 
FROM teachers as t INNER JOIN
(
    SELECT *,(SELECT COUNT(*) FROM REPORTS tt WHERE tt.TEACHER_ID = t1.TEACHER_ID and tt.REPORTINGPERIOD>=t1.REPORTINGPERIOD) rn
    FROM REPORTS t1
) as t1 on t1.TEACHER_ID = t.id and rn = 1
ORDER BY t.NAME

结果：

|         t.NAME | t1.REPORTINGTYPE | t1.REPORTINGPERIOD |
|----------------|------------------|--------------------|
|  Mr John Smith |            Final |           Mar 2017 |
| Ms Janet Smith |            Draft |           Jun 2018 |

2021-07-01