之前写了一条,select city,sum(cnt) from table where cnt >= 1000 group by city这个是知道了用户数大于1000的,但是现在同时要知道用户数小于1000的,两个结果都要算出来,该怎么办,自己先顶了。
这个虽然一个SQL就能做到,但是性能堪忧,不是很建议这么做。用CASE计算后的结果来作为分组依据。CASE WHEN CNT >=100 THEN 'high' ELSE 'low' END level
>1000和<1000一次都选出来,那不就是!=1000的都选吗? 可以加个flag以示区别就好了 select a.city, a.cnt (case a.cnt > 1000 then '高' else '低' end) as flag from table a where a.cnt != 1000 order by a.cnt
哦,是大于等于1000 select a.city, a.cnt (case a.cnt >= 1000 then '高' else '低' end) as flag from table a order by a.cnt
阿宝,其实他要的是分组统计,不过似乎还带上了行列转换要求,所以大概是:Select city, COUNT(Case cnt >= 1000 Then 1 Else null End) As higCount, COUNT(Case cnt < 1000 Then 1 Else null End) As lowCount, From table Gourp By city
这个统计好像意义不大,因为每个city都会选出来(group by和直接选的城市是一样的),只是字段上多了1或null,其实和我的加上flag没有区别,只是去掉了cnt(人数信息)如果是要统计有多少个城市高了,多少个低了,那么可以这样 select sum(case when cnt>=1000 then 1 else 0 end) as '高的城市个数', sum(case when cnt<1000 then 1 else 0 end) as '低的城市个数' from table如果只是为了区别城市的是否高或者是否地,之前的就可以了,去掉cnt人数信息 select city, (case cnt >= 1000 then '高' else '低' end) as '高低区分' from table order by cnt
select city, sum(case when cnt >= 1000 then cnt else 0 end) as high, sum(case when cnt <1000 then cnt else 0 end) as low from table group by city
CREATE TABLE tab_test( ciyt VARCHAR2(100), cnt NUMBER(6) ) INSERT INTO tab_test VALUES('usr_1',100); INSERT INTO tab_test VALUES('usr_2',600); INSERT INTO tab_test VALUES('usr_3',1000); INSERT INTO tab_test VALUES('usr_4',1200); INSERT INTO tab_test VALUES('usr_5',1200); INSERT INTO tab_test VALUES('usr_6',1200);SELECT COUNT(CASE WHEN cnt>=1000 THEN 1 ELSE NULL END) high_performance,COUNT(CASE WHEN cnt<1000 THEN 1 ELSE NULL END) low_performance FROM tab_test;把1000换为pi_cnt即可使用
SQL报错啊,你们写的都报 ora-00907 : missing right parenthesis
总金额个报错么?Select city, COUNT(Case When cnt >= 1000 Then 1 Else null End) As higCount, COUNT(Case When cnt < 1000 Then 1 Else null End) As lowCount, From table Gourp By city
还有个number字段?select city, sum(case when [number] >= 1000 then cnt else 0 end) as hUserCnt, sum(case when [number] < 1000 then cnt else 0 end) as lUserCnt from table group by city
sql 2005CREATE TABLE tab_test( id bigint identity(1,1), city varchar(100), username varchar(100), cnt numeric(18, 0) ) INSERT INTO tab_test VALUES('北京','usr_1',100); INSERT INTO tab_test VALUES('北京','usr_2',600); INSERT INTO tab_test VALUES('北京','usr_3',1000); INSERT INTO tab_test VALUES('北京','usr_4',1200); INSERT INTO tab_test VALUES('北京','usr_5',1200); INSERT INTO tab_test VALUES('北京','usr_6',1200); INSERT INTO tab_test VALUES('杭州','usr_7',900); INSERT INTO tab_test VALUES('杭州','usr_8',1000); INSERT INTO tab_test VALUES('杭州','usr_9',1200); INSERT INTO tab_test VALUES('杭州','usr_10',1200);select * from tab_test;select city, sum(case when cnt >= 1000 then 1 else 0 end) as highUserCount, sum(case when cnt< 1000 then 1 else 0 end) as lowUserCount from tab_test group by city
select a.city, a.cnt (case a.cnt > 1000 then '高' else '低' end) as flag
from table a where a.cnt != 1000
order by a.cnt
select a.city, a.cnt (case a.cnt >= 1000 then '高' else '低' end) as flag
from table a
order by a.cnt
COUNT(Case cnt >= 1000 Then 1 Else null End) As higCount,
COUNT(Case cnt < 1000 Then 1 Else null End) As lowCount,
From table
Gourp By city
select sum(case when cnt>=1000 then 1 else 0 end) as '高的城市个数',
sum(case when cnt<1000 then 1 else 0 end) as '低的城市个数'
from table如果只是为了区别城市的是否高或者是否地,之前的就可以了,去掉cnt人数信息
select city, (case cnt >= 1000 then '高' else '低' end) as '高低区分'
from table
order by cnt
select city,
sum(case when cnt >= 1000 then cnt else 0 end) as high,
sum(case when cnt <1000 then cnt else 0 end) as low
from table
group by city
ciyt VARCHAR2(100),
cnt NUMBER(6)
)
INSERT INTO tab_test VALUES('usr_1',100);
INSERT INTO tab_test VALUES('usr_2',600);
INSERT INTO tab_test VALUES('usr_3',1000);
INSERT INTO tab_test VALUES('usr_4',1200);
INSERT INTO tab_test VALUES('usr_5',1200);
INSERT INTO tab_test VALUES('usr_6',1200);SELECT COUNT(CASE WHEN cnt>=1000 THEN 1 ELSE NULL END) high_performance,COUNT(CASE WHEN cnt<1000 THEN 1 ELSE NULL END) low_performance FROM tab_test;把1000换为pi_cnt即可使用
有张表TABLE,字段: CIYT,CNT,numberCNT为Number类型,表示用户数
number为流量,Number类型是要求能查询出,流量>=1000和流量<1000 时候的,高性能的用户数和低性能用户数,按地市分组的。
COUNT(Case When cnt >= 1000 Then 1 Else null End) As higCount,
COUNT(Case When cnt < 1000 Then 1 Else null End) As lowCount,
From table
Gourp By city
sum(case when [number] >= 1000 then cnt else 0 end) as hUserCnt,
sum(case when [number] < 1000 then cnt else 0 end) as lUserCnt
from table
group by city
id bigint identity(1,1),
city varchar(100),
username varchar(100),
cnt numeric(18, 0)
)
INSERT INTO tab_test VALUES('北京','usr_1',100);
INSERT INTO tab_test VALUES('北京','usr_2',600);
INSERT INTO tab_test VALUES('北京','usr_3',1000);
INSERT INTO tab_test VALUES('北京','usr_4',1200);
INSERT INTO tab_test VALUES('北京','usr_5',1200);
INSERT INTO tab_test VALUES('北京','usr_6',1200);
INSERT INTO tab_test VALUES('杭州','usr_7',900);
INSERT INTO tab_test VALUES('杭州','usr_8',1000);
INSERT INTO tab_test VALUES('杭州','usr_9',1200);
INSERT INTO tab_test VALUES('杭州','usr_10',1200);select * from tab_test;select city,
sum(case when cnt >= 1000 then 1 else 0 end) as highUserCount,
sum(case when cnt< 1000 then 1 else 0 end) as lowUserCount
from tab_test
group by city