# SQL statement to achieve the number of daffodils

2020-11-09 10:51:34

# SQL Statement to achieve the number of daffodils

" Narcissistic number ", So-called " Narcissistic number " A three digit number , The sum of its cubes is equal to the number itself . for example ：370 It's a " Narcissistic number ", because 370=3 The third power of ＋7 The third power of ＋0 The third power of

## Implementation method

In this paper, two methods are used to calculate the number of Narcissus

### Method 1

The first one uses a layer of while loop , from 100-1000 Traverse , Take out its bit 、 ten 、 A hundred digit number , After the calculation of the third power, add it and compare it with the original figure , Equal to print out .
It takes out a bit 、 ten 、 The hundred bit method requires built-in functions ：CAST function
CAST Function is used to explicitly convert an expression of one data type to another , Such as ：

``cast(substring(cast(@n as varchar(3)),1,1) as int``

The statement will int Type variable n First change it to varchar( Variable length string ) type , recycling substring The function is taken out from 1 It starts with a character 1 Characters , That is, in the calculation of the number of Narcissus is taken out 3 Hundreds of digits , Finally, we will transform it into int Type variable .
The whole code of method one is as follows ：

``````declare @n int,@x as int,@y as int,@z as int
set @n=100
while @n<1000
begin
set @x=cast(substring(cast(@n as varchar(3)),1,1) as int)
set @y=cast(substring(cast(@n as varchar(3)),2,1) as int)
set @z=cast(substring(cast(@n as varchar(3)),3,1) as int)
if power(@x,3)+power(@y,3)+power(@z,3)=@n
begin
print @n
end
set @n=@n+1
end``````

use x,y,z Store their hundreds separately 、 Ten and one

### Method 2

Method two is the method I used at the beginning , That is to say, the same definition x,y,z Store its hundreds 、 Ten and one , adopt 3 individual while loop ,x from 1-9 Traverse ,y、z Respectively from the 0-9 Traverse , adopt x,y,z The sum of the third power and 100x+10y+z Compare the number of daffodils .
And in the beginning error The implementation code of is as follows ：

``````declare @x int, @y int,@z int,@m int
set @x=1
set @y=0
set @z=0
while @x<10
begin
while @y<10
begin
while @z<10
begin
if power(@x,3)+power(@y,3)+power(@z,3)=(@x*100+@y*10+@z)
begin
select @m=@x*100+@y*10+@z
print @m
end
set @z=@z+1
end
set @y=@y+1
end
set @x=@x+1
end``````

adopt sql server After executing the code, only the command has successfully completed the statement , No output , The code is wrong
After inspection , It turns out that there may be a problem with the cycle , Then use the following code to test ：

``````declare @x int, @y int,@z int,@m int
set @x=1
set @y=0
set @z=0
while @x<10
begin
while @y<10
begin
while @z<10
begin
print @z
set @z=@z+1
end
set @y=@y+1
end
set @x=@x+1
end``````

It is found that only 10 Time The reason for the discovery is while Nested loop outer loop variable cannot enter inner loop , therefore take set @y And set @z Before the statement is placed in the inner loop
Revised code ( correct ) as follows ：

``````declare @x int, @y int,@z int,@m int
set @x=1
while @x<10
begin
set @y=0
while @y<10
begin
set @z=0
while @z<10
begin
if power(@x,3)+power(@y,3)+power(@z,3)=(@x*100+@y*10+@z)
begin
select @m=@x*100+@y*10+@z
print @m
end
set @z=@z+1
end
set @y=@y+1
end
set @x=@x+1
end``````

The output is the same as that of method 1 Finally get 153,370,371,407 Count the daffodils