Detailed explanation of iif statement in sql

[Introduction] IIf returns one of two numeric or string values ​​determined by a logical test. Syntax NumericIIf(«Logical Expression», «Numeric Expression1», «Numeric Expression2») If «Logica

IIf
returns the one of two numeric or string values ​​determined by the logical test one.

Syntax
Number

IIf(«Logical Expression», «Numeric Expression1», «Numeric Expression2»)

If «Logical Expression» is TRUE, this function returns «Numeric Expression1», otherwise, returns «Numeric Expression2» .

String

IIf(«Logical Expression», «String Expression1», «String Expression2»)

If «Logical Expression» is TRUE, this function returns «String Expression1», otherwise, returns «String Expression2».

Notes
The expression is considered FALSE only when the value of «Logical Expression» is zero. Any other value is interpreted as TRUE.
It is not recommended to use the Iif function to create a collection of members based on search conditions. Instead, use the Filter function to evaluate each member of the specified collection against a logical expression and return a subcollection of the members.

Example
Number
The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:

IIf(IsEmpty(Measures.CurrentMember), 0, 1)

String
If Measures .CurrentMember is an empty unit, then the following string returns the string "Yes", otherwise it returns the string "No":
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No")

In Access, I can use the IIF function for statistical summary. For example, to know the number of users who should actually pay:

##Select sum(iif(amount> 0, 1,0)) as num from fee
There seems to be no corresponding function in SQL Server. I use:
select sum(case when amount>0 then 1 else 0 end) as num from fee
It seems not very intuitive, I wonder if there are other methods

case when ....then else end
Example:

select id,case when bz='1' then xx when bz='2' then yy else zz end as tt from xxx

What about MID, LEFT, etc.? How to use it in SQL?

MID is substring in SQL
LEFT is LEFT in SQL
For example, substring (field, starting position, how long to take)
left (field, how long to take)

SUBSTRING (expression, start, length)

Parameters
expression
is a string, binary string, text, image, column, or expression containing a column. Do not use expressions containing aggregate functions.

start
is an integer specifying the starting position of the substring.

length
is an integer specifying the length of the substring (number of characters or bytes to be returned).


LEFT
Returns the specified number of characters starting from the left side of the string.

Syntax

LEFT ( character_expression , integer_expression )

Parameters
character_expression

Character or binary data expression. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression
is a positive integer. If integer_expression is negative, an empty string is returned.

Return type
varchar

The above is the detailed explanation of the iif statement in sql. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!