Find a SQL statement to get the result set

教单于折箭

There is a table with the following contents:
     field1 field2 field3
        a 1 dd
        a 2 ee
        a 3 ee
        a 4 ee
        a 1 c
        a 2 e
        a 3 null
        a 1 8
        a 2 5
        a 2 4

The content of field3 doesn't matter.
I want it to be divided into three result sets according to the field field2. The first result set is
        a 1 dd
        a 2 ee
        a 3 ee
        a 4 ee
The second result set is:
        a 1 c
        a 2 e
        a 3 null
The third result set is
        a 1 8
        a 2 5
        a 2 4
How do I achieve it? Or how do I achieve one of the result sets?

教单于折箭

Sorry to correct it:
There is a table with the following contents:
     field1 field2 field3
        a 1 dd
        a 2 ee
        a 3 ee
        a 4 ee
        a 1 c
        a 2 e
        a 3 null
        a 1 8
        a 2 5
        a 3 4

The content of field3 doesn't matter.
I want it to be divided into three result sets according to the field field2. The first result set is
        a 1 dd
        a 2 ee
        a 3 ee
        a 4 ee
The second result set is:
        a 1 c
        a 2 e
        a 3 null
The third result set is
        a 1 8
        a 2 5
        a 3 4
How do I achieve it? Or how do I achieve one of the result sets?

fanfan1980

What are the conditions that separate each result set?

教单于折箭

The condition is field2, which is to divide the group by field2.
For example, the above three result sets are three groups.

wdh66f

Inadequate conditions

wdh66f

----------- quote --------------
The condition is field2, which is to divide the group by field2.
For example, the above three result sets are three groups.

If so, you get 4 sets
a 1 dd
a 1 c
a 1 8

a 2 ee
a 2 e
a 2 5

a 3 ee
a 3 null
a 3 4

a 4 ee

教单于折箭

Oh. This is not the result set I want

prajna

--- Create environment, temporary table #, content omitted

-Establish a unique incremental ID, if any, do not need to establish
select id = identity (int, 1,1), * into ## from #

--Group by increasing sequence
select *, (select isnull (max (id), 0) from ## where id <t.id and field2> t.field2) as page into ### from ## t

--Inquire
declare @page int
set @ page = 1-set the page number
exec ('select * from ### where page = (select top 1 page from (select distinct top' + @ page + 'page from ### order by page) tt order by page desc)')


--result
1 a 1 dd 0
2 a 2 ee 0
3 a 3 ee 0
4 a 4 ee 0


-When set @ page = 2 results
5 a 1 c 4
6 a 2 e 4
7 a 3 NULL 4

-When set @ page = 3 results
8 a 1 8 7
9 a 2 5 7
10 a 2 4 7

dazhuzhu1h

If the result is
    field1 field2 field3
        a 1 dd
        a 2 ee
        a 3 ee
        a 1 c
        a 2 e
        a 3 null
        a 1 8
        a 3 4
        a 2 5
        a 4 ee
What did the result look like?