VB to query the database, the connection is no problem, here is my query, has said have grammar question, please give advice or comments!

Select the customer name, customer ID, iif (registered capital & gt;=800, '5', the iif (registered capital & lt; 800 and registered capital & gt;=600, '4', the iif (registered capital & lt; 600 and registered capital & gt;=400, '3', the iif (registered capital & lt; 400 and registered capital & gt;=200, '2', '1')))) as the registered capital of score
Basic information from customers

CodePudding user response:

Select the customer name, customer ID, iif (customer basic information. The registered capital & gt;=800, '5', the iif (customer basic information. The registered capital & lt; 800 and customer basic information. The registered capital & gt;=600, '4', the iif (customer basic information. The registered capital & lt; 600 and customer basic information. The registered capital & gt;=400, '3', the iif (customer basic information. The registered capital & lt; 400 and customer basic information. The registered capital & gt;=200, '2', '1')))) AS the registered capital of score from customer basic information"

CodePudding user response:

1. You are a pile of IIF () function that returns the result is a "value", not "field name"!!!!
2. SQL directly support the IIF () function? (in my impression, it seems no?)
If you don't support, you need to be in "outside" SQL query, using VB statement prior to deal with the "result", then joining together into a SQL statement,

CodePudding user response:

SQL statement used in case when then

Refer to
https://www.cnblogs.com/HDK2016/p/8134802.html

CodePudding user response:

Just checked the, select statement, directly with the if () function, not the iif (),
Usage with VB6 iif (), only the function name is little one I,


(this paragraph is in around, nay reply, located in the "3 f", but unfortunately the network problems, do not reply... Then I went to do other things)

CodePudding user response:

Chewinggum

reference 3 floor response:

SQL statements in case the when then

Refer to
https://www.cnblogs.com/HDK2016/p/8134802.html

But the case when then in VB cannot execute!

CodePudding user response:

1. SQL does not support iff statements to change it for the case when... Then
Such as: case
When the registered capital & gt; 5
=800 thenWhen the registered capital & lt; 800 and registered capital & gt;=600 then 4
. AS a registered capital of score
2. IFF is a function of the access database

CodePudding user response:

Because of grouping is regular, can induce level general formula method simplified statement

 
Select the customer name, customer ID, case when (floor (registered capital/200) + 1 & lt;=5) then floor (registered capital/200) + 1 else 5 basic information from the customer end as the registered capital of score 

CodePudding user response:

Database without the IIF statements, you can try the WHERE and CASE statement,
For example,
In student achievement tables, queries each student's final score (don't statistics fail to pass the exam scores; If a living full didn't pass the exam, still listed)
SELECT
SS. StudentNo
To CONVERT (a DECIMAL (3, 1), AVG (SS. FinalScore)) AS FinalScoreAverage
The FROM
Tb_StudentScore AS SS
WHERE
SS. FinalScore>=60

CodePudding user response:

Equal step value, it's easy to get like this:

SELECT the customer name, customer ID, (+ 200 registered capital) \ AS the registered capital of 200 grade FROM customer basic information

CodePudding user response:

Upstairs, so how to define "registered capital rating" a maximum of 5?

CodePudding user response:

11 references Chen8013 response:

upstairs, so how to define "registered capital rating" a maximum of 5?

So you should adopt the answer to the eighth floor bai
Standard statements of SQL server, access, oracle can perform

CodePudding user response:

refer to 12 floor chewinggum reply:

Quote: reference 11 floor Chen8013 response:

Upstairs, so how to define "registered capital rating" a maximum of 5?

So you should adopt the answer to the eighth floor bai
Standard statements of SQL server, access, oracle can execute

Oh, you don't say I didn't pay attention to,
Estimate your writing can be,

But the original poster is not me...

CodePudding user response:

refer to 12 floor chewinggum reply:

Quote: reference 11 floor Chen8013 response:

Upstairs, so how to define "registered capital rating" a maximum of 5?

So you should adopt the answer to the eighth floor bai
Standard statements of SQL server, access, oracle can execute

However, I still have a question:
The "results" should be an integer from 1 to 5, I see you writing, is/division, and the writing is \ 10th floor,
The result of your writing, it is an integer?

CodePudding user response:

11 references Chen8013 response:

upstairs, so how to define "registered capital rating" a maximum of 5?

With two queries:
SELECT the customer name, customer ID, (+ 200 registered capital) \ AS the registered capital of 200 grade basic information FROM the customer WHERE the registered capital & lt; 1000
SELECT the customer name, customer ID, 5 basic information AS registered capital rating FROM the customer WHERE the registered capital & gt;=1000

Can also be their portfolio as a sub queries to a nested query,

In short, the solution to a problem can be varied, there is no the only correct answer,

CodePudding user response:

Ha ha, the method to solve the problem, is certainly there will be several,
See how to choose the building Lord,

CodePudding user response:

On the 14th floor Chen8013

reference response:

Quote: refer to 12 floor chewinggum reply:

Quote: refer to the 11 floor Chen8013 response:

Upstairs, so how to define "registered capital rating" a maximum of 5?

So you should adopt the answer to the eighth floor bai
Standard statements of SQL server, access, oracle can execute

However, I still have a question:
The "results" should be an integer from 1 to 5, I see you writing, is/division, and the writing is \ 10th floor,
The result of your writing, it is an integer?

So I use the floor function, because I am not sure whether \ all database support, but I list the three databases support floor function, the other I'm not sure.