设价格为price select 价格,count(*) 数量 from( select case when price<100 then '<100' when price>=100 and price<500 then '100-500' when price>=500 and price<1000 then '500-1000' else '>1000' end 价格, a.* from a) gtoup by 价格
设价格为price select 价格,count(*) 数量 from( select case when price>1000 then '>1000' when price>=500 and price <1000 then '500-1000' when price>=100 and price <500 then '100-500' else '<100' end 价格, a.* from a) group by 价格
create table #(id int,price int,kind varchar(10)) go insert into # values(1,30,'A') insert into # values(2,80,'A') insert into # values(3,70,'A') insert into # values(4,130,'A') insert into # values(5,330,'A') insert into # values(6,430,'A') insert into # values(7,230,'A') insert into # values(8,830,'A') insert into # values(9,930,'A') insert into # values(10,1030,'A') insert into # values(11,2530,'A') insert into # values(12,3000,'A') goselect 价格,count(1) 数量 from ( select 价格=case when price <100 then ' <100' when price>=100 and price <500 then '100-500' when price>=500 and price <1000 then '500-1000' else '>1000' end,#.id from # )t group by 价格drop table # go
<100 3 >1000 3 100-500 4 500-1000 2
select a.价格,sum(a.数量) 数量 from ( select case when 价格<100 then '<100' when 价格>=100 and 价格<500 then '100-500' when 价格>=500 and 价格<1000 then '500-1000' else '>1000' end 价格, tb1.数量 from tb1 ) a group by a.价格 这个分不能不赚~
select 价格,count(1) 数量 from ( select 价格=case when price <100 then ' <100' when price>=100 and price <500 then '100-500' when price>=500 and price <1000 then '500-1000' else '>1000' end,#.id from # )t group by 价格 我想问一下那个t是什么意思啊 为什么我去掉就会提示错误呢
select pr,count(0) from ( SELECT case when price <100 then '<100' when price between 100 and 500 then '100-500' when price between 500 and 1000 then '500-1000' else '>1000' end pr, a.* FROM temp a ) group by pr
select 价格,count(*) 数量
from(
select
case
when price<100 then '<100'
when price>=100 and price<500 then '100-500'
when price>=500 and price<1000 then '500-1000'
else '>1000'
end 价格,
a.*
from a)
gtoup by 价格
select 价格,count(*) 数量
from(
select
case
when price>1000 then '>1000'
when price>=500 and price <1000 then '500-1000'
when price>=100 and price <500 then '100-500'
else '<100'
end 价格,
a.*
from a)
group by 价格
create table #(id int,price int,kind varchar(10))
go
insert into # values(1,30,'A')
insert into # values(2,80,'A')
insert into # values(3,70,'A')
insert into # values(4,130,'A')
insert into # values(5,330,'A')
insert into # values(6,430,'A')
insert into # values(7,230,'A')
insert into # values(8,830,'A')
insert into # values(9,930,'A')
insert into # values(10,1030,'A')
insert into # values(11,2530,'A')
insert into # values(12,3000,'A')
goselect 价格,count(1) 数量 from
(
select
价格=case
when price <100 then ' <100'
when price>=100 and price <500 then '100-500'
when price>=500 and price <1000 then '500-1000'
else '>1000'
end,#.id
from #
)t
group by 价格drop table #
go
>1000 3
100-500 4
500-1000 2
select a.价格,sum(a.数量) 数量
from
(
select
case
when 价格<100 then '<100'
when 价格>=100 and 价格<500 then '100-500'
when 价格>=500 and 价格<1000 then '500-1000'
else '>1000'
end 价格, tb1.数量
from tb1
) a
group by a.价格
这个分不能不赚~
(
select
价格=case
when price <100 then ' <100'
when price>=100 and price <500 then '100-500'
when price>=500 and price <1000 then '500-1000'
else '>1000'
end,#.id
from #
)t
group by 价格
我想问一下那个t是什么意思啊 为什么我去掉就会提示错误呢
from
(
SELECT
case
when price <100 then '<100'
when price between 100 and 500 then '100-500'
when price between 500 and 1000 then '500-1000'
else '>1000'
end pr,
a.*
FROM temp a
)
group by pr