SQL topic collection

Question 1:

The table structure is as follows:
Mytable:
Id quantity
1 3.3333
2 4.2222
3 1.5555
4 9.8888
5 .........

The following query must be generated using SQL statements:
Id quantity
1 3.3333 3.3333
2 4.2222 7.5555
3 1.5555 9.0000
4 9.8888 18.8888
5 .........
Tip: 2-day cumulative Nissan = 1-day cumulative Nissan + 2-day cumulative Nissan
3-day cumulative Nissan = 2-day cumulative Nissan + 3-day cumulative Nissan

Answer: Select ID, quantity, (select sum (Quantity) from mytable where ID<=T. ID)AsAcount from mytable t

Question 2:

Table:
Field: ID (primary key) List
1 Domestic
2 Foreign
3 Domestic
4 foreign
5 foreign
6 Domestic
7 foreign
8. Domestic
9 Domestic
10 domestic
11 foreign countries
Can it be realized as follows:
Field: ID list
1 Domestic
3 Domestic
6 Domestic
2 Foreign
4 foreign
5 foreign
The difference between China and other countries is shown in terms of the meaning. First, the domestic and foreign regions are displayed,
Then, three domestic and foreign

-- Create a test environment
Create Table Tb (ID Int , List varchar ( 10 ))
Insert Tb (ID, list)
Select ' 1 ' , ' China ' Union all
Select ' 2 ' , ' Foreign ' Union all
Select ' 3 ' , ' China ' Union all
Select ' 4 ' , ' Foreign ' Union all
Select ' 5 ' , ' Foreign ' Union all
Select ' 6 ' , ' China ' Union all
Select ' 7 ' , ' Foreign ' Union all
Select ' 8 ' , ' China ' Union all
Select ' 9 ' , ' China ' Union all
Select ' 10 ' , ' China ' Union all
Select ' 11 ' , ' Foreign '
Go

-- Paging definition table
Create Table tb_page (
List varchar ( 10 ) Primary key, -- Category name, associated with the list of TB tables
Records Int , -- Number of records displayed per page
Orders Int ) -- Display order on the page
Insert tb_page select ' China ' , 3 , 1
Union all select ' Foreign ' , 3 , 2

Go

-- Stored Procedure for paging
Create proc p_pageview
@ pagecurrent int = 1 -- current page number to be displayed
as
set nocount on
-- get the number of records per page
declare @ pagesize int
select @ pagesize = sum (Records) from tb_page
If isnull (@ pagesize, 0 ) 0 return

-- Display by PAGE
Set @ pagecurrent = @ Pagecurrent * @ Pagesize
Set rowcount @ pagecurrent
Select Sid = Identity ( Int , 1 , 1 ), ID
Into # From (
Select top 100 Percent A. ID
From TB
Left join tb_page B on A. List = B. List
Order by case when B. List is null then 1 Else 0 End, -- The undefined category is displayed at the end.
(Select count ( * ) From TB
Where list = A. List
And (ID < A. ID or ID = A. ID and ID <= A. ID )) - 1 )
/ B. Records,
B. Orders, A. ID)
If @ pagecurrent > @ Pagesize
Begin
Set @ pagecurrent = @ Pagecurrent - @ Pagesize
Set rowcount @ pagecurrent
Delete from #
End
Select. * From tb a, # B
Where a. ID = B. ID
Order by B. Sid
Go

--Call
Exec p_pageview1
Go
--Delete test environment
Drop table TB, tb_page
Drop proc p_pageview
Go

/* -- Test Result
ID list
----------- ----------
1 Domestic
3 Domestic
6 Domestic
2 Foreign
4 foreign
5 foreign
*/