The ninth day of SQL Learning

On the ninth day of SQL study, SQL always considers over to be used in combination with row_number (). Today, we suddenly find that over can be combined with count. Now let's take a look at how it works with over! In this example, we will understand it: Create a table ([dbo]. [Orders] field Description: orderid -- order id, customerid -- consumer I

On the ninth day of SQL study, SQL always considers over to be used in combination with row_number (). Today, we suddenly find that over can be combined with count. Now let's take a look at how it works with over! In this example, we will understand it: Create a table ([dbo]. [Orders] field Description: orderid -- order id, customerid -- consumer I

The ninth day of SQL Learning -- SQL over

In the past, I always thought that over is used in combination with row_number (), website space, and U.S. space. Today I suddenly found that it can be combined 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 ):

. (, (5) COLLATE Chinese_PRC_CI_AS NULL ,())

Insert data to a table:

); Insert into dbo. Orders values (7, null );

Query the inserted data:

Dbo. orders

Result

Directly compare the preceding three SQL statements, such as the Virtual Host.

SQL statement 1 (simple query of all data ):

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 ordersorderid

Result Analysis diagram:

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