Realize the order according to the classification statistics every orders and payment date (and pages)
Order list
Whether the payment for the amount of the order number order date order (0 is 1 n)
NO001 20 2020-4-1 0
NO002 20 2020-4-1 1
NO003 20 2020-4-2 0
NO004 20 2020-4-2 0
NO005 20 2020-4-2 1
NO006 20 2020-4-4 0
NO007 20 2020-4-4 1
NO008 20 2020-4-4 1

Require payment date in accordance with the statistics every day orders and orders and paging,
Implementation effect
Date orders payment
The 2020-4-1 2 1
The 2020-4-2 3 2
The 2020-4-4 3 1

The next page/back

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' dbo. [t]) IS NOT NULL 
DROP TABLE dbo. [t] 
GO 
The CREATE TABLE dbo. [t] (
[the order number] NVARCHAR (10) 
, order DATE DATE 
, [orders] INT 
Payment, [whether] BIT 
) 
GO 
SET NOCOUNT ON 
INSERT INTO dbo. [t] VALUES (N 'NO001' N '2020-4-1', N '20', N '0') 
INSERT INTO dbo. [t] VALUES (N 'NO002' N '2020-4-1', N '20', N '1') 
INSERT INTO dbo. [t] VALUES (N 'NO003' N '2020-4-2', N '20', N '0') 
INSERT INTO dbo. [t] VALUES (N 'NO004' N '2020-4-2', N '20', N '0') 
INSERT INTO dbo. [t] VALUES (N 'NO005' N '2020-4-2', N '20', N '1') 
INSERT INTO dbo. [t] VALUES (N 'NO006' N '2020-4-4', N '20', N '0') 
INSERT INTO dbo. [t] VALUES (N 'NO007' N '2020-4-4', N '20', N '1') 
INSERT INTO dbo. [t] VALUES (N 'NO008' N '2020-4-4', N '20', N '1') 
GO 
-- -- -- -- -- -- -- -- -- -- -- -- above for testing table and test data -- -- -- -- -- -- -- -- -- -- 

- 1. Create a view 
IF OBJECT_ID (' view_t) IS NOT NULL 
DROP the VIEW view_t 
GO 
The CREATE VIEW view_t 
AS 
SELECT 
[order date] 
AS orders, and COUNT (1) 
, the SUM (CASE whether the WHEN payment=0 THEN 1 ELSE 0 END) AS payment 
T 
 the FROM dbo.GROUP BY order [date] 
GO 
- 2. Look at all the records 
SELECT * FROM view_t 
/* 
Order date orders payment 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
The 2020-04-01 2 1 
The 2020-04-02 3 2 
The 2020-04-04 3 1 
*/

- 3. Paging query 
DECLARE @ pageIndex INT, @ pageSize INT 
The SET @ pageIndex=1 - page 1 
SET @ pageSize=2-2 records per page 

SELECT * FROM view_t ORDER BY ORDER [date] OFFSET @ pageSize * (@ pageIndex - 1) ROW FETCH NEXT @ pageSize rows only 
/* 
Order date orders payment 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
The 2020-04-01 2 1 
The 2020-04-02 3 2 
*/