Explain explain SQL queries

The histogram is used to generate a dataset of the report, that is, to convert all values of an original field into columns. Then, based on certain grouping conditions, make summary statistics (average, sum, Count, etc.) on the data of a group whose values match the values of this column. Online, we all say that using limit is more concise than case, however, I personally think it is more difficult to understand it. However, from the query analyzer, we can see that the feature is actually implemented in case mode.

For example, the following query:

Declare @ btime datetime, @ etime datetime
Set @ btime = '2017-4-1'
Set @ etime = '2017-5-1'

-- The field here is the column after the grouping field and row-to-column Conversion
-- The field value here is the group field value. After the row is converted to the column, the summary statistics of this column in the group
Select orgid, orgname, employeid, employename, [1] As month1, [2] As mon22. [3] As month3, [4] As month4, [5] As month5, [6] As month6, [7] As month7, [8] As month8, [9] As month9, [10] As month10, [11] As month11, [12] As month12
From (
-- The data source for Row-to-column conversion can be any query that can generate a dataset.
Select O. orgid, O. orgname, E. employeid, E. Name as employename, datepart (month, C. createtime) as timepoint, C. moneyamount as amount
From employe e inner join organ o on O. orgid = E. orgid
Inner join compact C on C. employeid = E. employeid
Where C. Status = 2 and C. authtype = 1 and C. moneyamount> 10 and C. createtime >=@ btime and C. createtime <@ etime

) As DS

Partition (

-- Statistical functions
Count (amount)

-- Row-to-column allocation basis
For timepoint in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) As PVT
Order by employeid

The above query shows the contract signing status of sales personnel in 20xx, Which is grouped by employees and converted by month (createtime) to display the monthly contract (contract compact) of employees in 20xx) status

Specific execution process:

Above statisticsCodeObtain the signing records for 20XX years based on the specified conditions, and then group the records by employees. The statistical summary in the group is based on count (case when [month1] = (1) then compact]. [moneyamount] as [C]. [moneyamount] else null end ),.... to form multiple columns.

The essential implementation is the grouping and case statement. For details, refer:

Complete Syntax:

Select <Non-pivot columns>,

[First pivot column] As <column Name>,

[Second pivot column] As <column Name>,

...

[Last pivot column] As <column Name>,

From

(<SELECT query for generated data>)

As <Source Query alias>

Bytes

(

<Aggregate function> (<column to be aggregated>)

For

[<Column containing the value of the column title>]

In ([the first pivot column], [the second pivot column],

... [Last pivot column])

) As <Pivot table alias>

<Optional order by clause>;