PC SOFT

WINDEV的在线文档

  • Overview
  • Sub-query as selection condition
  • Sub-query as file of the main query
文档页面当前正在翻译。对于此延迟我们深表歉意。
您可以自愿地帮助我们翻译您想要的文档段落,然后只需通过以下地址与我们联系:info@cn.windev.com
产品
WinDevWebDev-服务器代码WebDev-浏览器代码WinDev Mobile报表和查询
平台
WindowsLinuxWindows MobileWindows PhoneWindows Store appsAndroidAndroid Widget iPhone/iPad
语言
JavaPHPAjax用户代码(MCU)外部语言
数据库
HFSQLHFSQL Client/Server存储过程HF 5.5OLE DBODBC本地访问

Overview
A sub-query is a query whose result is used by another query.

For example, you want to select the orders whose total amount is greater than the average amount of orders. The average amount of orders is not directly known. This average amount will be returned by another query (called sub-query).

A sub-query can be used:

  • as selection condition.
  • as file of the main query.
Limits:
  • The ORDER BY statement is not allowed in the sub-queries.
  • The sub-queries cannot be created in the query editor.

Sub-query as selection condition
A sub-query can be used as selection condition.

The authorized syntaxes are as follows:

SELECT Item1 [, Item2]
FROM File1 [, File2]
WHERE Item1 Operator (SELECT Item1[, Item2]
FROM File1 [, File2]
[WHERE ...])

SELECT Item1 [, Item2]
FROM File1 [, File2]
WHERE [NOT] EXISTS (SELECT Item1[, Item2]
FROM File1 [, File2]
[WHERE ...])

In this syntax, <Operator> can correspond to:
  • ALL, ANY (or SOME), IN or NOT IN
    • ALL: compare the value selected in the main query with all the values selected in the sub-query.
    • NOT IN: checks whether the value selected by the main query does not belong to the list of values selected in the sub-query.
    • IN: checks whether the value selected by the main query belongs to the list of values selected in the sub-query.
    • ANY (or SOME): compare the value selected by the main query with any value selected in the sub-query.
    • ALL: compare the value selected in the main query with all the values selected in the sub-query.
  • a comparison operator (=, <, <=, >, >=, <> or!=).
  • the combination between ALL, ANY (or SOME) and a comparison operator (=, <, <=, >, >=, <> or!=).
    • ALL: compare the value selected in the main query with all the values selected in the sub-query.
    • ANY (or SOME): compare the value selected by the main query with any value selected in the sub-query.
    • ALL: compare the value selected in the main query with all the values selected in the sub-query.
Example: The following SQL code is used to select the number of the orders whose total amount is greater than the average amount of the orders:
SELECT OrderNum
FROM ORDERS
WHERE Amount > (SELECT AVG(Amount)
FROM ORDERS)

Sub-query as file of the main query
A sub-query can be used as file of the main query.

The authorized syntax is as follows:

SELECT Item1 [, Item2]
FROM (SELECT Item1 FROM File1 [WHERE ...])