Count function in t_ SQL

 

T_ SQLInCountFunction

 

CountFunction Definition visibleMsdn. Definition:Count ({[[All | distinct] expression] | *})

 SoCountThere are two ways to useCount (expression)AndCount (*)Returns the value of a column for a table.

1.Count (*)The number of rows in the returned table. It does not filterNullAnd duplicate rows.

2.Count (expression)Will filter outNullValue, so the valueNullThe row is not included in the count.

3.However, ifExpressionAddDistinctKeyword, which filters out duplicate rows.

 In this way, we can draw a conclusion.: Count (*)The return value is always greater than or equalCount (expression).

 Many people like to use it in applications.Count (1 ),Here1Is actuallyExpressionBecause no column name in your table is1Column

; With cte1 (C1, C2, description) (

Select 1, 1, 'This is a Fox' Union all

Select 2, null, 'Firefox 'Union all

Select null, 2, 'people consider foxes as clever but sly animals 'Union all

Select null, null, null Union all

Select 3, null, 'this is my' Union all

Select 3, 3, 'Fox on the run ')

The result is as follows:

As shown in the result,Count (*), count (2)AndCount (3)Is exactly the same. WhileCount (C1)Apparently filtered outNullValue.

Note,CountParametersExpressionCan be a constant (like the above2,3...),Table columns, functions, and statements.Msdn. This application is shown below.

If you wantCte1ColumnDescriptionContains strings'Fox'To count, the typical practice is:

Select count (*) from cte1 where patindex ('% Fox %', cte1.description) <> 0

Patindex (TRANSACT-SQL)Returns the starting position of a mode that appears for the first time in a specified expression. If this mode is not found in all valid text and character data types, zero is returned.

SyntaxPatindex ('% pattern %', expression) PatternA text string. Wildcard characters can be used,PatternBefore and after%Character (except when the first or last character is searched ).

PatternIs the expression of the string data type category.

ExpressionAn expression is usually used to search for columns in the specified mode,ExpressionIs a string data type.

 Return type IfExpressionThe data type of isVarchar (max)OrNvarchar (max), IsBigintOtherwiseInt

PatindexCompare input-based sorting rules. You can useCollateApply the explicit sorting rule to the input value.

 

This approach isWhereInExpressionDefine search conditions in:

Select count (nullif (patindex ('% Fox %', cte1.description), 0) from cte1

IfDescriptionNo string in the column'Fox'SoPatindexThe function returns0, nullifThe result of the function isNullBecauseNullDoes not participate in counting, so the column does not'Fox'Row does not

Involved in counting, to achieve the purpose of searching.

Of course, we can stillExpressionUsed inCaseExpression:

 

Select count (CaseWhen patindex ('% Fox %', cte1.description) <> 0 then 1

Else <strong> null </strong> end) from cte1

Note:ElseThe statement must be followedNull, If notNull,ElseThe statement will also participateCountCount.

2InCountFunction followed by aggregate Window FunctionOver. Note that aggregate window functions cannot containOrder,OrderCan only appear in the ranking functionOverClause.OverFor the definition of words, seeMsdn.

 

Select C. *, count (*) over (partition by C. C1) 'c1 * count ',

 

Count (C1) over (partition by C. C1) 'c1 C1 count ',

 

Count (*) over (partition by C. C2) 'c2 count ',

 

Count (Case

 

When left (C. Description, 1) in ('T') then 1

 

Else null end) over (partition by left (C. Description, 1) 'start with t ',

 

Count (Case

 

When left (C. Description, 1) in ('T', 'F', 'P') then 1

 

Else null end) over (partition by left (C. Description, 1) 'start with T, f or P'

 

From cte1 C

 Note:OverWords cannot beOver (partition by C. C1 order by C. C1 ),This is becauseCountIt is not a ranking function.

 The preceding running result is:

We can see thatOverClause,CountStill follows the most basic principle,Count (*)YesNullRow count, whileCount (expression)No.

The precedingCountOfExpressionSetting conditions in is obviously not a very optimized method, because this method will first read all the data in the table, is to scan the table, and inWhereIt is a good way to set conditions for filtering in a clause. Because logically speaking,WhereBeforeSelectExecute. All database engines only read part of the data, not all data. If you wantC1ColumnNullStatistics,You can use either of the following methods:

 

Select count (*) from cte1 where c1 is null

Or:

Select count (CaseWhen C1 is null then 'X' else null end) from cte1

Finally, let's take a look at the comparison of the execution plan. The following method adds one more step (Filter):