求一条SQL语句关于分组统计

[eyuannet]

表如下：
id name pice
1 张山 20.5
2 小白 30.5
1 张山 24.5

3 王新 66
4 张山 55.5
5 王霸 77.2
6 王新 67.4

7 小白 107
8 王小 105
9 李里 178
10 王王 189

要求得到结果 统计各个区间的人数，在同一区间内的出现两次的同一个人 算一个人 即：下列结果

小于50 50-100 100-200
2 3 4

[ws_hgo]

create table #TT
(
  id int,
  name nvarchar(50),
  pice decimal(19,2)
)
insert into #TT select 1,'张山',20.5
insert into #TT select 2,'小白',30.5
insert into #TT select 1,'张山',24.5
insert into #TT select 3,'王新',66
insert into #TT select 4,'张山',55.5
insert into #TT select 5,'王霸',77.2
insert into #TT select 6,'王新',67.4
insert into #TT select 7,'小白',107
insert into #TT select 8,'王小',105
insert into #TT select 9,'李里',178
insert into #TT select 10,'王王',189

select sum(case when pice<50 then 1 else 0 end) '小于50',
       sum(case when pice between 50 and 100 then 1 else 0 end) '大于50小于100',
       sum(case when pice>100 then 1 else 0 end) '大于100'
from #TT

[ws_hgo]

你结果有问题
小于50的有3个

create table #TT
(
  id int,
  name nvarchar(50),
  pice decimal(19,2)
)
insert into #TT select 1,'张山',20.5
insert into #TT select 2,'小白',30.5
insert into #TT select 1,'张山',24.5
insert into #TT select 3,'王新',66
insert into #TT select 4,'张山',55.5
insert into #TT select 5,'王霸',77.2
insert into #TT select 6,'王新',67.4
insert into #TT select 7,'小白',107
insert into #TT select 8,'王小',105
insert into #TT select 9,'李里',178
insert into #TT select 10,'王王',189

select sum(case when pice<50 then 1 else 0 end) '小于50',
       sum(case when pice between 50 and 100 then 1 else 0 end) '大于50小于100',
       sum(case when pice>100 then 1 else 0 end) '大于100'
from #TT

小于50        大于50小于100   大于100
----------- ----------- -----------
3           4           4

[eyuannet]

[Quote=引用 2 楼 ws_hgo 的回复:]
SQL code你结果有问题
小于50的有3个createtable #TT
(
idint,
namenvarchar(50),
picedecimal(19,2)
)insertinto #TTselect1,'张山',20.5insertinto #TTselect2,'小白',30.5insertinto #TTselect1,'张山',24.5insertinto #TTse?-
[/Quote]
请注意我的问题：“在同一区间内的出现两次的同一个人 算一个人”

[lovexilove]

up

[zzxap]

select count( distinct id) from table where pice<50

select count( distinct id) from table where pice between 50 and 100

select count( distinct id) from table where pice>100

[eyuannet]

[Quote=引用 5 楼 zzxap 的回复:]
select count( distinct id) from table where pice <50

select count( distinct id) from table where pice between 50 and 100

select count( distinct id) from table where pice>100
[/Quote]

一条语句搞定不了么？

[fwacky]

create table test_count
(
id  int ,
[name] varchar(50),
 pice  float

)

insert into test_count
select 1  ,'张山' , 20.5 union all
select 2  ,'小白' , 30.5 union all
select 1  ,'张山' , 24.5 union all

select 3  ,'王新' , 66   union all
select 4  ,'张山' , 55.5 union all
select 5  ,'王霸' , 77.2 union all
select 6  ,'王新' , 67.4 union all

select 7  ,'小白' , 107  union all
select 8  ,'王小' , 105  union all
select 9  ,'李里' , 178  union all
select 10 ,'王王' , 189  

--小于50 50-100 100-200 

select max(  case when pice < 50.0 then  [name] else null end) as '小于50' ,
max( case when pice between 50.0 and 100.0 then [name] else null end) as '50-100',
max(case when pice between 100.0 and 200.0 then [name] else null end) as '100-200' 
into # from test_count group by  [name] 

select distinct (select count(1) from # where  [小于50] is not null) as  [小于50] ,
(select count(1) from # where  [50-100] is not null) as [50-100],
(select count(1) from # where  [100-200] is not null) as [100-200]
from # 

drop table #


===========
小于50        50-100      100-200
----------- ----------- -----------
2           3           4

[lidanzi]

一句话有点麻烦，用临时表吧

[randomfeel]

一条语句当然可以搞定

[zzxap]

结果出来了你不会自己合并么？

[fwacky]

create table test_count
(
id  int ,
[name] varchar(50),
 pice  float

)

insert into test_count
select 1  ,'张山' , 20.5 union all
select 2  ,'小白' , 30.5 union all
select 1  ,'张山' , 24.5 union all

select 3  ,'王新' , 66   union all
select 4  ,'张山' , 55.5 union all
select 5  ,'王霸' , 77.2 union all
select 6  ,'王新' , 67.4 union all

select 7  ,'小白' , 107  union all
select 8  ,'王小' , 105  union all
select 9  ,'李里' , 178  union all
select 10 ,'王王' , 189  

select distinct
(select count( distinct [name]) from test_count where pice <50 )as '小于50' ,
(select count( distinct [name]) from test_count where pice between 50 and 100) as '50-100',
(select count( distinct [name]) from test_count where pice>100 )as '100-200' 
from test_count where 1=1

============

小于50        50-100      100-200
----------- ----------- -----------
2           3           4

(1 行受影响)

[eyuannet]

[Quote=引用 11 楼 fwacky 的回复:]
SQL codecreatetable test_count
(
idint ,[name]varchar(50),
picefloat

)insertinto test_countselect1 ,'张山' ,20.5unionallselect2 ,'小白' ,30.5unionallselect1 ,'张山' ,24.5unionallselect3 ,'王新' ,66union?-
[/Quote]
这个办法到可以得到正确的答案，可是我的数据量特别大 test_count 表是 关联出来的，这个办法的效率就太低了吧 总超时

[ptpa]

select
(select count( distinct [a.id]) from test_count a where a.pice <50 )as '小于50' ,
(select count( distinct [b.id]) from test_count b where b.pice between 50 and 100) as '50-100',
(select count( distinct [c.id]) from test_count c where c.pice>100 )as '100-200'


效率慢 看看pice 有没有索引

[eyuannet]

总觉的应该group having