Home > Database > Mysql Tutorial > body text

Detailed explanation of iif statement in sql

黄舟
Release: 2017-02-20 11:50:24
Original
4280 people have browsed it

[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»)
Copy after login

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

String

IIf(«Logical Expression», «String Expression1», «String Expression2»)
Copy after login

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)
Copy after login

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
Copy after login

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 )
Copy after login


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)!



Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template