如何让数据分组显示？

游园密语 2010-12-29 02:18:25

数据如下
mid name num count status
1 apple 2 4 0
1 orange 4 12 0
1 bananas 3 12 0
2 orange 3 9 0
2 cocoa 3 15 1
3 apple 2 4 1
3 orange 2 6 1
4 apple 2 4 0
我想select出来的时候能够这样子显示
-------------------------------------------------------------
mid name num count status
-------------------------------------------------------------
1 apple 2 4 0
orange 4 12 0
bananas 3 12 0
----------------------------------------------------------------
2 orange 3 9 0
cocoa 3 15 1
-------------------------------------------------------------
3 apple 2 4 1
orange 2 6 1
----------------------------------------------------------------
4 apple 2 4 0
-----------------------------------------------------
该如何去筛选呢？

...全文

191 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

wsh236 2010-12-29

打赏
举报

select mid = (case when name = (select top 1 name from tb where mid = t.mid) then ltrim(mid) else '' end),

       name ,num,[count],status

 from tb t

sych888 2010-12-29

打赏
举报

group by.....

AcHerat 元老 2010-12-29

打赏
举报



--修改7楼

SELECT 

    (CASE A.ID WHEN 1 THEN LTRIM(mid) ELSE '' END)mid,

    [name],

    [count],

    [status]

FROM

(

    SELECT ID = ROW_NUMBER() OVER (PARTITION BY mid ORDER BY GETDATE()),*

    FROM TB

) A



mid          name                      count       status

------------ ------------------------- ----------- -----------

1            apple                     4           0

             orange                    12          0

             bananas                   12          0

2            orange                    9           0

             cocoa                     15          1

3            apple                     4           1

             orange                    6           1

4            apple                     4           0



(8 行受影响)

dawugui 2010-12-29

打赏
举报

create table tb

(

   mid int,

   name varchar(25),

   num int, 

   count int,

   status int

)

insert into tb 

select 1,'apple',2,4,0 union all

select 1,'orange',4,12,0 union all

select 1,'bananas',3,12,0 union all

select 2,'orange',3,9,0 union all

select 2,'cocoa',3,15,1 union all

select 3,'apple',2,4,1 union all

select 3,'orange',2,6,1 union all

select 4,'apple',2,4,0

go



select mid = (case when name = (select top 1 name from tb where mid = t.mid) then ltrim(mid) else '' end),

       name ,num,[count],status

 from tb t



drop table tb



/*

mid          name                      num         count       status      

------------ ------------------------- ----------- ----------- ----------- 

1            apple                     2           4           0

             orange                    4           12          0

             bananas                   3           12          0

2            orange                    3           9           0

             cocoa                     3           15          1

3            apple                     2           4           1

             orange                    2           6           1

4            apple                     2           4           0



（所影响的行数为 8 行）

*/

Shawn 2010-12-29

打赏
举报

SELECT 

	mid = LTRIM(CASE A.ID WHEN 1 THEN mid ELSE '' END),

	[name],

	[count],

	[status]

FROM

(

	SELECT ID = ROW_NUMBER() OVER (PARTITION BY mid ORDER BY GETDATE()),*

	FROM TB

) A

飘零一叶 2010-12-29

打赏
举报

;with cte as

(

  select ROW_NUMBER()over(PARTITION by mid order by getdate()) as rn,mid,name,num,count,status from tb

)

select case when rn=1 then LTRIM(mid) else '' end as mid,name,count,status from cte

----------------------

mid	name	count	status

1	apple	4	0

	orange	12	0

	bananas	12	0

2	orange	9	0

	cocoa	15	1

3	apple	4	1

	orange	6	1

4	apple	4	0

飘零一叶 2010-12-29

打赏
举报

if OBJECT_ID('tb') is not null drop table tb

go

create table tb

(

   mid int,

   name varchar(25),

   num int, 

   count int,

   status int

)

insert into tb 

select 1,'apple',2,4,0 union all

select 1,'orange',4,12,0 union all

select 1,'bananas',3,12,0 union all

select 2,'orange',3,9,0 union all

select 2,'cocoa',3,15,1 union all

select 3,'apple',2,4,1 union all

select 3,'orange',2,6,1 union all

select 4,'apple',2,4,0

;with cte as

(

  select ROW_NUMBER()over(PARTITION by mid order by getdate()) as rn,mid,name,num,count,status from tb

)

select case when rn=1 then LTRIM(mid) else '' end as mid,* from cte

----------------------

mid	rn	mid	name	num	count	status

1	1	1	apple	2	4	0

	2	1	orange	4	12	0

	3	1	bananas	3	12	0

2	1	2	orange	3	9	0

	2	2	cocoa	3	15	1

3	1	3	apple	2	4	1

	2	3	orange	2	6	1

4	1	4	apple	2	4	0

飘零一叶 2010-12-29

打赏
举报

if OBJECT_ID('tb') is not null drop table tb

go

create table tb

(

   mid int,

   name varchar(25),

   num int, 

   count int,

   status int

)

insert into tb 

select 1,'apple',2,4,0 union all

select 1,'orange',4,12,0 union all

select 1,'bananas',3,12,0 union all

select 2,'orange',3,9,0 union all

select 2,'cocoa',3,15,1 union all

select 3,'apple',2,4,1 union all

select 3,'orange',2,6,1 union all

select 4,'apple',2,4,0

;with cte as

(

  select ROW_NUMBER()over(PARTITION by mid order by getdate()) as rn,name,num,count,status from tb

)

select case when rn=1 then '1' else '' end as mid,* from cte

--------------

mid	rn	name	num	count	status

1	1	apple	2	4	0

	2	orange	4	12	0

	3	bananas	3	12	0

1	1	orange	3	9	0

	2	cocoa	3	15	1

1	1	apple	2	4	1

	2	orange	2	6	1

1	1	apple	2	4	0