Ideal logic and business logic

Today does an SQL statement, which is disgusting.

The requirement is also quite simple and common, that is, to compare whether a form has been changed.

If there is any change, send a mail. If not, the application is approved directly.

 

You can understand this.

There is no suspense in the general practice. For example, if the name of A is a backup table, let's make a PREA. Does a make a backup in PREA every time it is updated.

 

The problem is: we need to compare a record in a with a record in PREA, Which is troublesome.

One by one?

No, that's too tired. Someone gave me an idea:

"For the difference between set a and Set B (remove the same item), if set a and Set B get the difference set, then set B and set a get the difference set, and then merge, that's all."

 

This is probably the case with SQL:

(
Select   *   From A
Except
Select   *   From PREA
)
Union   All
(
Select   *   From PREA
Except
Select   *   From A
)

Then I made such a view, for example, diffofa.

If you look up a record in this view, it means there is a difference! If not, does it mean there is no difference.

The application is not bad. But the problem was quickly tested:

When there is no record in PREA, So !!! At this time, it will be considered as a difference...

=. =

Yes, there is no problem at all from the collection point of view, but it is also strange from the business point of view.

 

In fact, in the system I have encountered, there are two situations that will lead to no value in the pre. When a table is still located, that is, when it is inserted for the first time, there is no value in the pre.

The other is when the process ends. In this case, the PRE table is cleared. Because it has no value.

 

So this problem is well solved. Change to Pre and A to calculate the difference set. That is, the SQL statement is killed in half and becomes:

Select * FromPREA
Except
Select * FromA

..