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

• 10/29/2020
• • 
  • 

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

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

语法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

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

StormEvents 
| where State in~ ("Florida", "Georgia", "New York") 
| count

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

StormEvents 
| where State !in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count

使用动态数组Use dynamic array

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

子查询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

其他示例中的 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 

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

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

函数定义。The function definition.

.show function InterestingStates