Combination query bug of Data room Charging System

Disclaimer: the following content only applies to queries on-board records (combined queries) of students, and queries on-board records only for operator work records (combined queries!


It is a bug. In fact, this is not a bug. It is just an error that is easy to make. If you don't pay attention to it, you can't find it. The following is a detailed description:

There are three combined queries in the data room charging system: basic student information query, student computer record query, and operator work record query. The students' computer record query, I personally think that only the records that have already been down should be queried, and the records on the machine should not be queried. The operator's work records are also the same.

If you share the same idea with me, let's see if our implementation method is the same: the SQL statements for combined queries are all concatenated strings, but are we correct?

Method 1:

Method 2:





What are the different query results for the same data and the same combination query? How can this happen? It must be our own problem. We all know that the SQL query statements in the Combined Query are concatenated by conditions, but are we correct?

First splicing:

Select * From t_line where status! = 'Machine in id' and cardno> '0' or cardno <'5'

Splicing:

Select * from (select * From t_line where status! = 'Machine in id') as t_line where cardno> '0' or cardno <'5'

The first splicing method is to simply add a condition to the backend for splicing. I believe someone has made the same mistake with me! In fact, as long as we write the SQL statement, we can easily see where we are wrong: if all the conditions are connected with "and", we can also query the correct results, however, when we use "or" to connect conditions?

For the second join, use a subquery to query the result that meets the "offline" condition as a temporary change, and then query the data we want to query from this temporary table, in this way, no matter whether the following conditions are "and" or ", the records on the computer will be queried.

After a simple explanation above, I believe everyone understands where their bugs are. If you still don't understand them or have better solutions, please discuss them in private ......

Combination query bug of Data room Charging System