代码之家 › 专栏 › 技术社区 › Maxcot

将查询添加到子查询,则不会产生任何结果

sqlite sql

Maxcot · 技术社区 · 6 年前

这个 lastReports 0 rows returned in 12ms from: 这是在SQLite上。

SQL LEFT JOIN Subquery Alias )... 我错过了什么,还是不明白?

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

2 回复 | 直到 6 年前

D-Shih 6 年前

你可以试着 row_number TEACHER_ID 列和顺序 REPORTINGPERIOD ,这意味着子查询中的日期越近,get rn = 1 每个 教师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

Results

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

CL. 6 年前

这个 REPORTINGPERIOD 值比较不正确,因为 Jul 先来后到 Jun yyyy-mm 最重要的领域是第一位的。

在SQLite中,您可以简单地 use MAX() to select entire rows :

SELECT t.Name,
       r.ReportingType,
       max(r.ReportingPeriod)
FROM Teachers t
JOIN Reports r ON t.ID = r.Teacher_ID
GROUP BY r.Teacher_ID;

NAME            REPORTINGTYPE  max(r.ReportingPeriod)
--------------  -------------  ----------------------
Mr John Smith   Final          2017-03               
Ms Janet Smith  Draft          2018-07