SQL Learning has a filter grouping

1, SQL In addition to the group by grouping data, SQL also allows filtering groups, rules include those groups, exclude those groups. For example, you might want to list all customers with at least two orders. To do this, the filter must be based on a complete grouping instead of individual rows.

Based on the above example, the first time we think about is to filter the data by using where, to get the results we want, but in this sake where the task is not completed because the where filter specifies rows, not groupings. In fact, where there is no concept of grouping.

Therefore, SQL provides a clause that is similar to the WHERE clause, which is specifically used to filter the groupings, having clauses, in fact, almost all types of where clauses can be replaced with having. The only difference is that where filters the row data, having the having filter grouping data.

The following code:

Select *  from dbo. T_unit_equipment

Now there is a reporting system that needs to show each unit, with the number of each device, this time with where is cumbersome (if the amount of data is less than the case), the following is the solution code:

Select Unitid,equipmentname,COUNT(*as from thegroupbyorder  by ASC

OK, complete the demand!

Now, to change the requirements, you now need to retrieve a record of the same device that each unit owns, greater than or equal to 2, and the following is the workaround code:

Select Unitid,equipmentname,COUNT(*as fromgroup by has COUNT (*) >= 2 Order  by ASC

OK, complete the demand!

The above code is grouped by Unitid,equipmentname two fields, then the SELECT statement retrieves Unitid,equipmentname, and equipments (equipments here is a calculated field, The total number of records per grouping is computed through the count () function, and then the HAVING clause tells the SELECT statement to retrieve only grouped records that have a total record count greater than or equal to 2 in all groupings. The Count (*) >=2 here filters the grouping of less than 2 of the same device owned by each unit!

2, where and having the difference:

Where it is filtered before the data is grouped, having to filter after the data is grouped is an important difference, where excluded rows are not included in the grouping. This may change the computed value, thereby affecting the filtered groupings based on those values in the HAVING clause, which, depending on the difference, can determine where clause and GROUP BY clauses are located: thewhere clause filters the row data before the data is grouped. Here's an example of how the HAVING clause differs from the WHERE clause.

Select *  from dbo. T_unit_equipment

Now you need to retrieve a record for each unit that has the same device greater than or equal to 2, provided that the device must have a maintainer (the person column value in the corresponding table cannot be empty), and the following is the workaround code:

Select Unitid,equipmentname,COUNT(*as fromwhere -person!="  Groupby hasCOUNT(*)>=1  OrderbyASC

SQL Learning has a filter grouping