Golden Code SQL notes (9)

page301-354 Other Solutions---window functions-test data and table use [nb]go/****** object: Table [dbo]. [T_person2] Script Date: 08/14/2015 11:24:46 ******/set ansi_nulls ongoset quoted_identifier ongoset ANSI_PADDING ONGOCREATE T ABLE [dbo]. [T_person3] ([FName] [varchar] () NULL, [fcity] [varchar] () NULL, [Fage] INT, fsalary int) On [Primary]goset ansi_padding Offinsert to T_person3 (FName, fcity, fage,fsalary) VALUES (' Tom ', ' Beijing ', 20,3000) INS ERT into T_person3 (FName, fcity, Fage,fsalary) The VALUES (' Tim ', ' Chengdu ', 21,4000) INSERT into T_person3 (FName, fcity, Fage, Fsalary) VALUES (' Jim ', ' Beijing ', 22,3500) INSERT into T_person3 (FName, fcity, fage,fsalary) VALUES (' Lily ', ' LonDon ', 21, ) insert into T_person3 (FName, fcity, fage,fsalary) VALUES (' John ', ' newyork ', 22,1000) insert into T_person3 (FName, FC ity, Fage,fsalary) VALUES (' yaoming ', ' Beijing ', 20,3000) INSERT into T_person3 (FName, fcity, fage,fsalary) VALUES (' Swing ', ' LonDon ', 22,2000) INSERT into T_person3 (FName, fcity, fage,fsalary) VALUES (' Guo ', ' newyork ', 20,2800) INSERT into T_person3 (FName, fcity, fage,fsalary) VALUES (' Yuqian ', ' Beijing ', 24,8000) inser T into T_person3 (FName, fcity, fage,fsalary) VALUES (' Ketty ', ' London ', 25,8500) INSERT into T_person3 (FName, fcity, fage,f Salary) VALUES (' Merry ', ' Beijing ', 23,3500) INSERT into T_person3 (FName, fcity, fage,fsalary) VALUES (' Smith ', ' Chengdu ',  30,3000) insert into T_person3 (FName, fcity, Fage,fsalary) The VALUES (' Bill ', ' Beijing ', 25,2000) insert into T_person3 (FName , Fcity, Fage,fsalary) VALUES (' Jerry ', ' newyory ', 24,3300) SELECT * from t_person3--as with aggregate functions, The window function also aggregates the rowset group--but it does not return only one value per group like a normal aggregate function, and the window function can--returns multiple values for each group, because the row set group that the open window function performs the aggregation calculation is the window--port ISO SQL prescribes such functions as window functions, in Oracle The middle is called-the analytic function, and in DB2 it is called the OLAP function select COUNT (*) from t_person3--the following two expressions are a meaning select Fcity, fage,count (*) over () from T_pers On3 SELECT fcity, Fage,count (*) from T_person3where fsalary < 5000GROUP by fcity,fage--over keyword means to treat a function as an open window function instead of an aggregate function--sql The standard allows all aggregate functions to be used as window functions, using the OVER keyword--to differentiate between the two usages--above count (*) over () for each row of the query resultAll returns the number of rows with the-condition of the over () keyword is also often added in parentheses after the option to change the window range for the aggregation operation if the--option is empty in parentheses after the over () keyword, The Open-window function aggregates all rows in the result set--partition by clause-the parentheses after the open window function over () keyword, you can use the partition BY clause to define the row's distinction, thus making the aggregation calculation different from the GROUP BY clause,-- The partition created by the partition BY clause is independent of the result set, and the partition created is just--the partition that provides the aggregation calculation and the partitions created by different window functions do not affect each other--example below SQL is used to show each person's information and the number of people in their city--the same SQL Multiple window functions can be used in a statement, and these windowing functions do not--interfere with each other--Example select FName, fcity, Fage, Fsalary,count (*) over (PARTITION by fcity), COUNT (*) over  (PARTITION by Fage) From T_person3--with clause and subquery--once defined multiple times used to extract subqueries withssss as (select Fage from T_person3 WHERE fage <24) SELECT * from T _person3 as T WHERE t.fage in (SELECT * from SSSS)--374WITHCR as (select Fage from T_person3) SELECT * from T_pers On3 where Fage in (SELECT * from CR)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Golden Code SQL notes (9)