T-SQL (a) group by, distinct, subquery

I collected online materials and added my own SQL.

1. Group by is a group query. Generally, group by isUsed with aggregate Functions

The principle of group by is that all columns after select do not use aggregate functions and must appear after group.

2. Having

The where clause is used inBefore grouping query resultsRemove rows that do not meet the where condition, that is, filter data before grouping,The condition cannot contain clustering functions., Use the where ConditionShow specific rows.

The having clause is used to filter groups that meet the conditions, that isFilter data After grouping,Conditions often contain clustering functions., Using the having ConditionDisplay a specific group, You can also use multiple grouping standards for grouping.

The having clause is limited to columns and aggregate expressions defined in the SELECT statement. Generally, you need to repeat the aggregate function expression in the having clause to reference the aggregate value, just as you did in the SELECT statement.

3. A subquery is a SELECT statement nested in a select, select... into statement, insert... into statement, delete statement, or update statement, or nested in another subquery.

EM:

-- Group by, having, Where
Select top 10 Userid, count ( * ) As Psize from produce group by userid order by psize DESC -- Query the product table from large to small > Companies with the largest number of products released
Select * From produce Where Userid = 755
Select top 10 Userid, procpic, count ( * ) As Psize from produce group by userid, procpic order by psize DESC
Select top 10000 Userid, count ( * ) As Psize from produce group by userid having userid > = 700 Order by psize DESC
Select userid, count ( * ) As Psize from produce Where Userid > = 700 Group by userid order by count ( * ) DESC
Select userid, count ( * ) As Psize from produce group by userid having userid > = 700 Order by count ( * ) DESC
Select top 1000 * From userinfo
Select top 1000 * From produce
Select userid, count ( * ) From
Select * From bizservice where bizservicetype = ' 101 '
Select top 10 Userid, count ( * ) As Psize from produce group by userid having count ( * ) > 900 Order by psize DESC -- Having is used to filter groups.
Select top 10 Userid, count ( * ) As Psize from produce Where Userid > 900 Group by userid order by psize DESC -- Where Used to filter Fields

Select top 100   * From produce
-- Subquery
Select * From DBO. bizservicetype1
Select * From DBO. bizservicetype2
Select * From userinfo Where ID = 2627
Select top 10 * From bizservice Where Bizservicetype In (Select typeid from bizservicetype2)
Select * From bizinfo Where Userid = (Select ID from userinfo Where Userid = ' Food20090729041156 ' )

Select distinct userpwd from userinfo

There is a better way to limit queries than using having statements. Generally, having is less effective than where, because having restricts the result set only after the data group, while where restricts the result set first. The following example uses the having statement incorrectly.

-- Bad SQL

Select userinfo. ID, userinfo. userid, count (*) as usersize
From userinfo
Group by userinfo. ID, userinfo. userid
Having userinfo. ID> 100

The correct method should be to list the filter criteria of the query in the where statement, as follows:

Select userinfo. ID, userinfo. userid, count (*) as usersize
From userinfo
Where userinfo. ID> 100
Group by userinfo. ID, userinfo. userid

(T-SQL authoritative guide)

 

Comparison between distinct and group

 

The structure of table T3 is as follows:

Select * from T3
Id Edu age
1 book 20
2 book 25
3 Book 30
4 Book 30
5 book 25
Medium 6 15
Medium 7 20
Medium 8 20
9 expert 20
10 major 20
11 major 20
12 major 30
13 expert 30
--------------------------------------
Select distinct Edu, age
From T3
Order by age

Edu age
Medium 15
Book 20
Medium 20
Tutorial 20
Book 25
Ben 30
Tutorial 30

Summary: 1. Distinct edu. Age regards the EDU and age fields as one. If EDU and age fields are the same,
It is regarded as a duplicate record;
2. Only one distinct or all can be used in the SELECT statement;
3. If distinct is used in select, all cannot be used. If all is used, distinct cannot be used;
4. If select distinct is specified, the items in the order by clause must appear in the selection list;
---------------------------------------
Select Edu, age, count (*) as 'number'
--
From T3
-- Where id <10
-- Filter condition for records before where grouping
Group by Edu, age
-- Having: filtering conditions for groups after grouping
Order by age

Edu age count
Medium 15 1
Book 20 1
Medium 20 2
Tutorial 20 3
Book 25 2
Book 30 2
Tutorial 30 2

Summary: 1. Distinct edu. Age regards the EDU and age fields as one. If EDU and age fields are the same,
It will be treated as the same group;
2. The column name in the select clause must be a group column or column function;
3. The column name in the WHERE clause can be any field in the table;
4. The column names in the having clause must be group columns or column functions;