in 和 !in 运算符in and !in operators

根据提供的值集筛选记录集。Filters a record set based on the provided set of values.

Table1 | where col in ('value1', 'value2')

备注

  • 向运算符添加“~”会使值的搜索不区分大小写:x in~ (expression)x !in~ (expression)Adding '~' to the operator makes values' search case-insensitive: x in~ (expression) or x !in~ (expression).
  • 在表格表达式中,会选择结果集的第一列。In tabular expressions, the first column of the result set is selected.
  • 表达式列表最多可生成 1,000,000 个值。The expression list can produce up to 1,000,000 values.
  • 嵌套数组将平展为单个值列表。Nested arrays are flattened into a single list of values. 例如,x in (dynamic([1,[2,3]])) 重命名为 x in (1,2,3)For example, x in (dynamic([1,[2,3]])) becomes x in (1,2,3).

语法Syntax

区分大小写的语法Case-sensitive syntax

T | where col in (list of scalar expressions) T | where col in (list of scalar expressions)
T | where col in (tabular expression)T | where col in (tabular expression)

T | where col !in (list of scalar expressions)T | where col !in (list of scalar expressions)
T | where col !in (tabular expression)T | where col !in (tabular expression)

不区分大小写的语法Case insensitive syntax

T | where col in~ (list of scalar expressions) T | where col in~ (list of scalar expressions)
T | where col in~ (tabular expression)T | where col in~ (tabular expression)

T | where col !in~ (list of scalar expressions)T | where col !in~ (list of scalar expressions)
T | where col !in~ (tabular expression)T | where col !in~ (tabular expression)

参数Arguments

  • T:其记录待筛选的表格输入。T - The tabular input whose records are to be filtered.
  • col - 要筛选的列。col - The column to filter.
  • list of expressions - 以逗号分隔的表格、标量或文本表达式的列表。list of expressions - A comma-separated list of tabular, scalar, or literal expressions.
  • tabular expression - 包含一组值的表格表达式。tabular expression - A tabular expression that has a set of values. 如果表达式包含多个列,则使用第一列。If the expression has multiple columns, the first column is used.

返回Returns

其谓词为 true 的 T 中的行。Rows in T for which the predicate is true.

示例Examples

使用“in”运算符Use 'in' operator

StormEvents 
| where State in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count
计数Count
47754775

使用“in~”运算符Use 'in~' operator

StormEvents 
| where State in~ ("Florida", "Georgia", "New York") 
| count
计数Count
47754775

使用“!in”运算符Use '!in' operator

StormEvents 
| where State !in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count
计数Count
5429154291

使用动态数组Use dynamic array

let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents 
| where State in (states)
| count
计数Count
32183218

子查询Subquery

// Using subquery
let Top_5_States = 
StormEvents
| summarize count() by State
| top 5 by count_; 
StormEvents 
| where State in (Top_5_States) 
| count

同一查询可以编写为:The same query can be written as:

// Inline subquery 
StormEvents 
| where State in (
    ( StormEvents
    | summarize count() by State
    | top 5 by count_ )
) 
| count
计数Count
1424214242

其他示例中的 TopTop with other example

let Lightning_By_State = materialize(StormEvents | summarize lightning_events = countif(EventType == 'Lightning') by State);
let Top_5_States = Lightning_By_State | top 5 by lightning_events | project State; 
Lightning_By_State
| extend State = iif(State in (Top_5_States), State, "Other")
| summarize sum(lightning_events) by State 
状态State sum_lightning_eventssum_lightning_events
ALABAMAALABAMA 2929
威斯康星州WISCONSIN 3131
德克萨斯TEXAS 5555
佛罗里达州FLORIDA 8585
佐治亚州GEORGIA 106106
其他Other 415415

使用函数返回的静态列表Use a static list returned by a function

StormEvents | where State in (InterestingStates()) | count

计数Count
47754775

函数定义。The function definition.

.show function InterestingStates
名称Name parametersParameters 正文Body 文件夹Folder DocStringDocString
InterestingStatesInterestingStates ()() { dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) }{ dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) }