You have not met in SQL 1 is not equal to 1 (1<>1) situation!? Here's an example to illustrate the situation.
Let's start with a brief introduction to the four numeric data types in the header.
In T-SQL, numeric and decimal are exact numeric data types, while float and real are approximate numeric data types.
Numeric and decimal are the same. Real is the same as float (24).
When defining a float (n) Type column, if the value of n is explicitly specified, for example
float (ten)
Then when the value of n is between 1-24, the system marks the column (val) as the real type, and when the value of n is between 25-53, the system marks the column as a float type. Validation can be obtained from the system table sys.columns.
If not explicitly specified, the default value for N is 53. To add, because the value of the float type is represented by the scientific notation, n represents the number of significant digits of the value, and MSDN describes N as the number of digits in the mantissa.
Now look at the specific example.
1 CREATE TABLETmain (2IdINT IDENTITY(1,1)PRIMARY KEY,3ValFLOAT4 )5 GO6 7 CREATE TABLETdetail (8IdINT IDENTITY(1,1)PRIMARY KEY,9PidINT,TenValFLOAT One ) A GO - - INSERT intoTmainVALUES( -); the INSERT intoTmainVALUES( -); - - INSERT intoTdetailVALUES(1, -); - INSERT intoTdetailVALUES(1, *); + INSERT intoTdetailVALUES(1, *); - + INSERT intoTdetailVALUES(2, -); A INSERT intoTdetailVALUES(2, -); at INSERT intoTdetailVALUES(2, +); - - --SELECT * from Tmain; - --SELECT * from Tdetail; - - SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0 asSumper in fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid - GROUP bytm.id to + SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0 asSumper - fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid the GROUP bytm.id * $ SELECT * Panax Notoginseng from ( - SELECTTm.id,SUM(Tm.val*Td.val/ 10000.0) asSumper the fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid + GROUP bytm.id A ) TMS the WHERETms. Sumper= 1; + - SELECT * $ from ( $ SELECTTm.id,SUM(Tm.val*Td.val)/ 10000.0 asSumper - fromTmain TMINNER JOINTdetail TD onTm.id=Td.pid - GROUP bytm.id the ) TMS - WHERETms. Sumper= 1;Wuyi the DROP TABLETmain; - DROP TABLETdetail;
Do you want to predict the outcome before you do it?
Take a look at the results of the first two SELECT statements.
First select
A second Select
The result is the same, at least, looks the same!
Take a look at the results of the subsequent two SELECT statements.
A third Select
Fourth a Select
The results of the latter two select produce differences: One is first multiplication, then plus minus, the other is first plus minus, then multiplication.
Because the Val column is a float type and is an approximate numeric data type, it is clear that the result of the first select has a row of data that produces an approximate value of 1 during the calculation. The 1 specified in the WHERE statement, by default, is the int type, and the int type is the exact data type. So the third SELECT statement has a 1<>1 situation.
Examples are complete.
If you define a val column as a decimal type, the result of the above four SELECT statements is the same. It is recommended to use approximate numeric data types with caution, and to anticipate potential problems when calculating and comparing values.
1 is not equal to 1? Differences between numeric, decimal, float, and real data types