Golden Code SQL notes (9)

page301-354 Other Solutions---open window function--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 is also the aggregation of the rowset group--but it is not like a normal aggregate function, each group only returns a value, the window function can--for each group to return multiple values, because the window function is run aggregate computed rowset group is the window--port ISO SQL stipulates that this function is the window function, in the Oracle The middle is called-the analytic function, and in DB2 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--overkeyword means the function as a window function instead of an aggregate function- The-sql standard agrees to use all aggregate functions as window functions, using over keyword-to differentiate between the two methods of use-above count (*) over ()For each row of the query result, the number of rows that match the-condition is returned. The parentheses in the keyword are often added as an option to change the scope of the form for the aggregation operation assuming that the option is empty in parentheses after the keyword. The window function aggregates all the rows in the result set--partition by clause--the open window function over () keyword The parentheses, you can use the partition BY clause--to define the row distinction, so that the aggregation calculation and the GROUP BY clause is different,- The partitions created by the-partition by clause are independent of the result set, and the partitions created are simply--providing aggregate calculations and partitions created by different window functions--Example the following 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)

Golden Code SQL notes (9)