The ninth day of SQL Learning -- SQL over

The ninth day of SQL Learning -- SQL over

In the past, we always thought that over is used in combination with row_number (). Today we suddenly found that over can also be used with Count. Now let's take a look at how it works with over!

Or understand it from the example:

Table creation ([DBO]. [orders] field Description: orderid -- order ID, customerid -- consumer ID ):

CREATE TABLE [dbo].[Orders](    [orderid] [int] NOT NULL,    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,PRIMARY KEY CLUSTERED (    [orderid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

Insert data to a table:

insert into dbo.Orders values(1,'FRNDO');insert into dbo.Orders values(2,'FRNDO');insert into dbo.Orders values(3,'KRLOS');insert into dbo.Orders values(4,'KRLOS');insert into dbo.Orders values(5,'KRLOS');insert into dbo.Orders values(6,'MRPHS');insert into dbo.Orders values(7,null);

Query the inserted data:

select * from dbo.orders

Result

The comparison between the preceding three SQL statements is clear.

SQL statement 1 (simple query of all data ):

select * from dbo.Orders

SQL statement 2 (the combination of Count and over is used ):

select orderid , customerid,count(*) over(partition by customerid) as num_ordersfrom orders

SQL statement 3 (combining count and over with conditions ):

select orderid , customerid,count(*) over(partition by customerid) as num_ordersfrom orderswhere customerid is not null and orderid%2 = 1

Result Analysis diagram:

 

After reading the graph, you may understand what is going on. For partition by, I mentioned earlier (click here for details ).