In (transact-sql)in (transact-sql)

Часто задаваемые вопросы

Вопрос: Мне нужно сравнить две даты и вернуть количество строк поля, основанного на значении дат. Например, у меня есть в таблице поле даты, которое называется дата последнего обновления. Я должен проверить, если TRUNC (last_updated_date)> = TRUNC (Sysdate-13).


Ответ: Поскольку вы используете функцию COUNT, которая является агрегатной функцией, то мы рекомендуем использовать Oracle оператор UNION. Например, вы можете попробовать следующее:

Oracle PL/SQL

SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode) FROM cdmaster a, nmmaster b WHERE a.code = b.code AND a.status = 1 AND b.status = 1 AND b.Ncode <> ‘a10’ AND TRUNC(last_updated_date) <= TRUNC(sysdate-13) GROUP BY a.code, a.name UNION SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode) FROM cdmaster a, nmmaster b WHERE a.code = b.code AND a.status = 1 AND b.status = 1 AND b.Ncode <> ‘a10’ AND TRUNC(last_updated_date) > TRUNC(sysdate-13) GROUP BY a.code, a.name;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECTa.codeASCode,a.nameASName,COUNT(b.Ncode)

FROMcdmastera,nmmasterb

WHEREa.code=b.code

ANDa.status=1

ANDb.status=1

ANDb.Ncode<>’a10′

ANDTRUNC(last_updated_date)<=TRUNC(sysdate-13)

GROUPBYa.code,a.name UNION

SELECTa.codeASCode,a.nameASName,COUNT(b.Ncode)

FROMcdmastera,nmmasterb

WHEREa.code=b.code

ANDa.status=1

ANDb.status=1

ANDb.Ncode<>’a10′

ANDTRUNC(last_updated_date)>TRUNC(sysdate-13)

GROUPBYa.code,a.name;

Oracle оператор UNION позволит выполнить подсчет на основе одного набора критериев.

TRUNC(last_updated_date)

А также выполнить подсчет на основе другого набора критериев.

TRUNC(last_updated_date) > TRUNC(sysdate-13)

Example — Using IS NULL with the SELECT Statement


When testing for a NULL value, IS NULL is the recommended comparison operator to use in SQL. Let’s start by looking at an example that shows how to use the IS NULL condition in a SELECT statement.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
WHERE favorite_website IS NULL;

There will be 1 record selected. These are the results that you should see:

customer_id last_name first_name favorite_website
8000 Anderson Paige NULL

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Практическое упражнение №3

На основании таблиц suppliers и orders ниже, выберите поля supplier_id и supplier_name из таблицы suppliers, и выберите поле order_date из таблицы orders, где значение поля supplier_id в таблице suppliers соответствует значению поля supplier_id в таблице orders. Сортировать результаты по supplier_id в порядке убывания.

Oracle PL/SQL

—создаем таблицу suppliers CREATE TABLE suppliers ( supplier_id int NOT NULL, supplier_name char(50) NOT NULL, city char(50), state char(25), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) ); —вставляем записи в таблицу suppliers insert into suppliers values (1,’Mari’,’Houston’,’Texas’); insert into suppliers values (2,’Frida’,’ Melbourne’, ‘Florida’); insert into suppliers values (3,’Madlen’,’Phoenix’,’Arizona’); insert into suppliers values (4,’Valentina’,’San Diego’,’California’); insert into suppliers values (5,’Amba’,’Jacksonville’,’Florida’);

1 2 3 4 5 6 7 8 9 10 11 12 13 14

—создаем таблицу suppliers

CREATETABLEsuppliers (supplier_idintNOTNULL,

supplier_namechar(50)NOTNULL,

citychar(50),

statechar(25),

CONSTRAINTsuppliers_pkPRIMARYKEY(supplier_id) ); —вставляем записи в таблицу suppliers

insertintosuppliersvalues(1,’Mari’,’Houston’,’Texas’);

insertintosuppliersvalues(2,’Frida’,’ Melbourne’,’Florida’);

insertintosuppliersvalues(3,’Madlen’,’Phoenix’,’Arizona’);

insertintosuppliersvalues(4,’Valentina’,’San Diego’,’California’);

insertintosuppliersvalues(5,’Amba’,’Jacksonville’,’Florida’);

Содержимое таблицы suppliers:

supplier_id supplier_name city state
1 Mari Houston Texas
2 Frida Philadelphia Pennsylvania
3 Madlen Phoenix Arizona
4 Valentina SanDiego California
5 Amba Jacksonville Florida

Oracle PL/SQL

—создаем таблицу orders CREATE TABLE orders ( order_id int NOT NULL, supplier_id int NOT NULL, order_date date NOT NULL, quantity int, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); —вставляем записи в таблицу orders insert into orders values (1,1,’05.05.2014′,100); insert into orders values (2,3,’12.02.2015′,300); insert into orders values (3,5,’12.01.2016′,500);

1 2 3 4 5 6 7 8 9 10 11 12

—создаем таблицу orders

CREATETABLEorders (order_idintNOTNULL,


supplier_idintNOTNULL,

order_datedateNOTNULL,

quantityint,

CONSTRAINTorders_pkPRIMARYKEY(order_id) ); —вставляем записи в таблицу orders

insertintoordersvalues(1,1,’05.05.2014′,100);

insertintoordersvalues(2,3,’12.02.2015′,300);

insertintoordersvalues(3,5,’12.01.2016′,500);

Содержимое таблицы orders:

order_id supplier_id order_date quantity
1 1 05.05.2014 100
2 3 12.02.2015 300
3 5 12.01.2016 500

Example — Using IS NOT NULL with the SELECT Statement

When testing for a non-NULL value, IS NOT NULL is the recommended comparison operator to use in SQL. Let’s start by looking at an example that shows how to use the IS NOT NULL condition in a SELECT statement.

In this example, we have a table called products with the following data:

product_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

Enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

product_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Решения

MySQL

MySQL функция IFNULL() позволяет возвращать альтернативное значение, если выражение равно нулю:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

или мы можем использовать функцию COALESCE(), например:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;


SQL Server

SQL Server функция ISNULL() позволяет возвращать альтернативное значение, если выражение равно нулю:

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products;

MS Access

MS Access функция IsNull() возвращает TRUE (-1), если выражение имеет нулевое значение, в противном случае FALSE (0):

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder)) FROM Products;

Oracle

Oracle функция NVL() достигает того же результата:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;

Пример — объединение условий AND и OR

Вы также можете комбинировать условие AND с условием OR для проверки более сложных условий. Давайте для этого примера снова использовать таблицу products.

product_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

Теперь введите следующий SQL оператор.

PgSQL

SELECT * FROM products WHERE (product_id > 3 AND category_id = 75) OR (product_name = ‘Pear’);

1 2 3 4

SELECT*

FROMproducts

WHERE(product_id>3ANDcategory_id=75)

OR(product_name=’Pear’);

Будет выбрано записи. Вот результаты, которые вы должны получить.

product_id product_name category_id
1 Pear 50
5 Bread 75

В этом примере возвращаются все записи таблицы products, у которых product_id больше 3 и category_id — 75, а также записи с product_name это ‘Pear’. Скобки определяют порядок оценки условий AND и OR.

Примечание

Допустимые параметры для пространства имен ‘USERENV’: (Обратите внимание, что не все параметры действительны во всех версиях Oracle)

Parameter Explanation Oracle 9i Oracle 10g Oracle 11g
ACTION Returns the position in the module No Yes Yes
AUDITED_CURSORID Returns the cursor ID of the SQL that triggered the audit Yes Yes Yes
AUTHENTICATED_IDENTITY Returns the identity used in authentication No Yes Yes
AUTHENTICATION_DATA Authentication data Yes Yes Yes
AUTHENTICATION_METHOD Returns the method of authentication No Yes Yes
AUTHENTICATION_TYPE Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy Yes No No
BG_JOB_ID If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. Yes Yes Yes
CLIENT_IDENTIFIER Returns the client identifier (global context) Yes Yes Yes
CLIENT_INFO User session information Yes Yes Yes
CURRENT_BIND Bind variables for fine-grained auditing No Yes Yes
CURRENT_SCHEMA Returns the default schema used in the current schema Yes Yes Yes
CURRENT_SCHEMAID Returns the identifier of the default schema used in the current schema Yes Yes Yes
CURRENT_SQL Returns the SQL that triggered the audit event Yes Yes Yes
CURRENT_SQL_LENGTH Returns the length of the current SQL statement that triggered the audit event No Yes Yes
CURRENT_USER Name of the current user Yes No No
CURRENT_USERID Userid of the current user Yes No No
DB_DOMAIN Domain of the database from the DB_DOMAIN initialization parameter Yes Yes Yes
DB_NAME Name of the database from the DB_NAME initialization parameter Yes Yes Yes
DB_UNIQUE_NAME Name of the database from the DB_UNIQUE_NAME initialization parameter No Yes Yes
ENTRYID Available auditing entry identifier Yes Yes Yes
ENTERPRISE_IDENTITY Returns the user’s enterprise-wide identity No Yes Yes
EXTERNAL_NAME External of the database user Yes No No
FG_JOB_ID If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL. Yes Yes Yes
GLOBAL_CONTEXT_MEMORY The number used in the System Global Area by the globally accessed context Yes Yes Yes
GLOBAL_UID The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins. No No Yes
HOST Name of the host machine from which the client has connected Yes Yes Yes
IDENTIFICATION_TYPE Returns the way the user’s schema was created No Yes Yes
INSTANCE The identifier number of the current instance Yes Yes Yes
INSTANCE_NAME The name of the current instance No Yes Yes
IP_ADDRESS IP address of the machine from which the client has connected Yes Yes Yes
ISDBA Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. Yes Yes Yes
LANG The ISO abbreviate for the language Yes Yes Yes
LANGUAGE The language, territory, and character of the session. In the following format: language_territory.characterset Yes Yes Yes
MODULE Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI No Yes Yes
NETWORK_PROTOCOL Network protocol used Yes Yes Yes
NLS_CALENDAR The calendar of the current session Yes Yes Yes
NLS_CURRENCY The currency of the current session Yes Yes Yes
NLS_DATE_FORMAT The date format for the current session Yes Yes Yes
NLS_DATE_LANGUAGE The language used for dates Yes Yes Yes
NLS_SORT BINARY or the linguistic sort basis Yes Yes Yes
NLS_TERRITORY The territory of the current session Yes Yes Yes
OS_USER The OS username for the user logged in Yes Yes Yes
POLICY_INVOKER The invoker of row-level security policy functions No Yes Yes
PROXY_ENTERPRISE_IDENTITY The Oracle Internet Directory DN when the proxy user is an enterprise user No Yes Yes
PROXY_GLOBAL_UID The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users. No Yes Yes
PROXY_USER The name of the user who opened the current session on behalf of SESSION_USER Yes Yes Yes
PROXY_USERID The identifier of the user who opened the current session on behalf of SESSION_USER Yes Yes Yes
SERVER_HOST The host name of the machine where the instance is running No Yes Yes
SERVICE_NAME The name of the service that the session is connected to No Yes Yes
SESSION_USER The database user name of the user logged in Yes Yes Yes
SESSION_USERID The database identifier of the user logged in Yes Yes Yes
SESSIONID The identifier of the auditing session Yes Yes Yes
SID Session number No Yes Yes
STATEMENTID The auditing statement identifier No Yes Yes
TERMINAL The OS identifier of the current session Yes Yes Yes

С этим читают