Description:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
-> left join
-> (
-> SELECT
-> l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
-> cAudit
-> FROM
-> bs_class c
-> JOIN
-> bs_lesson l
-> ON
-> c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
-> WHERE
-> c.nschoolId=1
-> AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
-> AND c.nAudit!=2
-> AND c.nState!=3
-> )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
-> where r.nSchoolId=1 and r.sCode in ('RMBJ59038','RMBJ59001');
+------------+-------+-----------+-----------------------------------------+
| sClassCode | sName | sCode | sName |
+------------+-------+-----------+-----------------------------------------+
| NULL | NULL | RMBJ59001 | 海淀知春路大运村校区201教室 |
| NULL | NULL | RMBJ59038 | 海淀知春路大运村校区212教室 |
+------------+-------+-----------+-----------------------------------------+
2 rows in set (0.01 sec)
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
-> left join
-> (
-> SELECT
-> l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
-> cAudit
-> FROM
-> bs_class c
-> JOIN
-> bs_lesson l
-> ON
-> c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
-> WHERE
-> c.nschoolId=1
-> AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
-> AND c.nAudit!=2
-> AND c.nState!=3
-> )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
-> where r.nSchoolId=1 and r.sCode in ('RMBJ59038');
+---------------+---------------------------+-----------+-----------------------------------------+
| sClassCode | sName | sCode | sName |
+---------------+---------------------------+-----------+-----------------------------------------+
| QTOEF20163002 | TOEFL基础班(Qtest) | RMBJ59038 | 海淀知春路大运村校区212教室 |
+---------------+---------------------------+-----------+-----------------------------------------+
1 row in set (0.00 sec)
The same sql return different result only changed condition.
Two sql all contain 'RMBJ59038',but, one result`s field 'sClassCode' contains 'QTOEF20163002 ',another one isn`t.
How to repeat:
1. bs_lesson datas:
select nschoolid,sclasscode,sroomcode,sectbegin from bs_lesson l where l.nschoolid=1 and l.sroomcode='RMBJ59001' and l.sectbegin>'2015-05-01';
+-----------+------------+-----------+---------------------+
| nschoolid | sclasscode | sroomcode | sectbegin |
+-----------+------------+-----------+---------------------+
| 1 | TFP15112 | RMBJ59001 | 2015-05-30 08:30:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-05-30 11:40:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-05-31 08:30:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-05-31 11:40:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-06-06 08:30:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-06-06 11:40:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-06-07 08:30:00 |
| 1 | TFP15112 | RMBJ59001 | 2015-06-07 11:40:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-02 13:30:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-02 15:40:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-04 13:30:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-04 15:40:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-06 13:30:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-06 15:40:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-08 13:30:00 |
| 1 | TSD153028 | RMBJ59001 | 2015-08-08 15:40:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-14 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-14 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-05 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-05 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-12 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-12 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-18 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-18 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-20 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-20 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-04 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-04 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-11 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-11 10:10:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-01 13:30:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-01 15:40:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-03 13:30:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-03 15:40:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-05 13:30:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-05 15:40:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-07 13:30:00 |
| 1 | TSD153027 | RMBJ59001 | 2015-08-07 15:40:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-17 13:30:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-17 15:40:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-18 13:30:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-18 15:40:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-19 13:30:00 |
| 1 | TS153095 | RMBJ59001 | 2015-08-19 15:40:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-13 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-13 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-27 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-27 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-21 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-21 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-28 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-06-28 10:10:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-19 08:00:00 |
| 1 | TS153025 | RMBJ59001 | 2015-07-19 10:10:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-09 13:30:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-09 15:40:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-15 13:30:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-15 15:40:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-15 18:30:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-16 13:30:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-16 15:40:00 |
| 1 | TSD153029 | RMBJ59001 | 2015-08-16 18:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-10 13:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-10 15:40:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-10 18:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-12 13:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-12 15:40:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-12 18:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-14 13:30:00 |
| 1 | TSD153030 | RMBJ59001 | 2015-08-14 15:40:00 |
+-----------+------------+-----------+---------------------+
select nschoolid,sclasscode,sroomcode,sectbegin from bs_lesson l where l.nschoolid=1 and l.sroomcode='RMBJ59038';
+-----------+---------------+-----------+---------------------+
| nschoolid | sclasscode | sroomcode | sectbegin |
+-----------+---------------+-----------+---------------------+
| 1 | NB161158 | RMBJ59038 | 2016-06-28 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-06-28 15:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-06-29 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-06-29 15:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-06-30 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-06-30 15:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-01 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-01 15:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-02 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-02 15:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-03 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-03 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-04 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-04 10:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-04 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-04 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-05 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-05 10:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-05 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-05 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-06 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-06 10:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-06 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-06 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-07 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-07 10:10:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-07 13:00:00 |
| 1 | NB161158 | RMBJ59038 | 2016-07-07 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-08 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-08 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-08 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-08 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-09 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-09 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-09 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-09 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-10 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-10 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-10 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-10 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-11 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-11 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-11 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-11 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-12 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-12 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-12 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-12 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-13 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-13 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-13 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-13 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-14 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-14 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-14 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-14 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-15 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-15 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-15 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-15 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-16 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-16 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-16 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-16 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-17 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-17 10:10:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-17 13:00:00 |
| 1 | NB161159 | RMBJ59038 | 2016-07-17 15:10:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-18 08:30:00 |
| 1 | NQ1640 | RMBJ59038 | 2016-07-18 10:10:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-19 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-19 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-20 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-20 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-21 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-21 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-22 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-22 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-23 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-23 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-24 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-24 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-25 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-25 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-26 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-26 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-27 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-27 10:40:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-28 08:30:00 |
| 1 | NC16237 | RMBJ59038 | 2016-07-28 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-29 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-29 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-30 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-30 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-31 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-07-31 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-01 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-01 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-02 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-02 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-03 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-03 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-04 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-04 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-05 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-05 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-06 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-06 10:40:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-07 08:30:00 |
| 1 | NC16238 | RMBJ59038 | 2016-08-07 10:40:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-05-23 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-05-27 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-05-30 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-03 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-06 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-10 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-13 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-17 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-20 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-24 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-06-27 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-07-01 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-07-04 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-07-08 19:00:00 |
| 1 | QTOEF20163002 | RMBJ59038 | 2016-07-11 19:00:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-06 13:00:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-06 14:40:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-13 13:00:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-13 14:40:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-27 13:00:00 |
| 1 | NCM15222 | RMBJ59038 | 2015-12-27 14:40:00 |
| 1 | NCM15222 | RMBJ59038 | 2016-01-10 13:00:00 |
| 1 | NCM15222 | RMBJ59038 | 2016-01-10 14:40:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-17 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-17 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-17 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-17 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-18 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-18 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-18 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-18 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-19 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-19 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-19 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-19 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-20 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-20 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-20 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-20 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-21 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-21 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-21 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-21 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-22 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-22 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-22 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-22 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-23 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-23 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-23 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-23 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-24 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-24 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-24 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-24 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-25 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-25 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-25 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-25 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-26 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-26 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-26 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-26 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-27 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-27 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-27 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-27 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-28 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-28 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-28 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-28 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-29 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-29 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-29 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-29 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-30 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-30 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-30 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-30 18:10:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-31 08:30:00 |
| 1 | NQC16117 | RMBJ59038 | 2016-01-31 11:40:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-31 15:00:00 |
| 1 | NQC16123 | RMBJ59038 | 2016-01-31 18:10:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-05 13:00:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-05 14:40:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-12 13:00:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-12 14:40:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-19 13:00:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-19 14:40:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-26 13:00:00 |
| 1 | NCM15221 | RMBJ59038 | 2015-12-26 14:40:00 |
| 1 | NCM15221 | RMBJ59038 | 2016-01-02 13:00:00 |
| 1 | NCM15221 | RMBJ59038 | 2016-01-02 14:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-11 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-11 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-12 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-12 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-13 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-13 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-14 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-14 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-15 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-15 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-16 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-16 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-17 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-17 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-18 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-18 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-19 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-19 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-20 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-20 10:40:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-21 08:30:00 |
| 1 | NBM16111 | RMBJ59038 | 2016-02-21 10:40:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-06 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-06 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-13 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-13 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-20 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-20 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-27 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-03-27 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-10 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-10 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-17 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-17 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-24 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-04-24 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-08 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-08 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-15 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-15 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-22 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-22 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-29 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-05-29 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-05 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-05 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-19 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-19 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-26 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-06-26 10:10:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-07-03 08:30:00 |
| 1 | NQ16602 | RMBJ59038 | 2016-07-03 10:10:00 |
+-----------+---------------+-----------+---------------------+
2. bs_class datas:
select c.nSchoolId,c.sCode,c.sName,c.nAudit,c.nState from bs_class c where c.sCode='QTOEF20163002';
+-----------+---------------+---------------------------+--------+--------+
| nSchoolId | sCode | sName | nAudit | nState |
+-----------+---------------+---------------------------+--------+--------+
| 1 | QTOEF20163002 | TOEFL基础班(Qtest) | 1 | 0 |
+-----------+---------------+---------------------------+--------+--------+
1 row in set (0.61 sec)
3.one sql:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
left join
(
SELECT
l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
cAudit
FROM
bs_class c
JOIN
bs_lesson l
ON
c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
WHERE
c.nschoolId=1
AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
AND c.nAudit!=2
AND c.nState!=3
)tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
where r.nSchoolId=1 and r.sCode in ('RMBJ59038','RMBJ59001');
+------------+-------+-----------+-----------------------------------------+
| sClassCode | sName | sCode | sName |
+------------+-------+-----------+-----------------------------------------+
| NULL | NULL | RMBJ59001 | 海淀知春路大运村校区201教室 |
| NULL | NULL | RMBJ59038 | 海淀知春路大运村校区212教室 |
+------------+-------+-----------+-----------------------------------------+
2 rows in set (0.01 sec)
4.two sql:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
left join
(
SELECT
l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
cAudit
FROM
bs_class c
JOIN
bs_lesson l
ON
c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
WHERE
c.nschoolId=1
AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
AND c.nAudit!=2
AND c.nState!=3
)tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
where r.nSchoolId=1 and r.sCode in ('RMBJ59038');
+---------------+---------------------------+-----------+-----------------------------------------+
| sClassCode | sName | sCode | sName |
+---------------+---------------------------+-----------+-----------------------------------------+
| QTOEF20163002 | TOEFL基础班(Qtest) | RMBJ59038 | 海淀知春路大运村校区212教室 |
+---------------+---------------------------+-----------+-----------------------------------------+
1 row in set (0.00 sec)