表:
字段1(varchar) 字段2(number)
A 5
A 6
A 1
A 18
B 3
B 13
B 14
C 19
---------------------------
要求统计“字段2”在1-10、11-20,之间的纪录数量。按照以下方式显示
字段1 1-10 11-20
A 3 1
B 1 2
C 0 1
字段1(varchar) 字段2(number)
A 5
A 6
A 1
A 18
B 3
B 13
B 14
C 19
---------------------------
要求统计“字段2”在1-10、11-20,之间的纪录数量。按照以下方式显示
字段1 1-10 11-20
A 3 1
B 1 2
C 0 1
sum(
case
when 字段2>=1 and 字段2<=10 thne 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2<=20 thne 1
else 0
end
) "11-20",
from a
group by 字段1
sum(
case
when 字段2>=1 and 字段2<=10 thne 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2<=20 thne 1
else 0
end
) "11-20"
from a
group by 字段1
如果还需要增加列,就仿照sum的写
sum(
case
when 字段2>=1 and 字段2<=10 then 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2<=20 then 1
else 0
end
) "11-20"
from a
group by 字段1
---------- ----------
A 5
A 6
A 1
A 18
B 3
B 13
B 14
C 198 rows selected-- 1ST WAY:
SQL> SELECT FIELD1,
2 SUM(DECODE(SIGN(FIELD2 - 1 + 1) * SIGN(10 - FIELD2 + 1), 1, 1, 0)) "1-10",
3 SUM(DECODE(SIGN(FIELD2 - 10 + 1) * SIGN(20 - FIELD2 + 1), 1, 1, 0)) "10-20"
4 FROM TEST_TT
5 GROUP BY FIELD1;FIELD1 1-10 10-20
---------- ---------- ----------
A 3 1
B 1 2
C 0 1-- 2ND WAY:
SQL> SELECT FIELD1,
2 SUM(CASE
3 WHEN FIELD2 >= 1 AND FIELD2 <= 10 THEN
4 1
5 ELSE
6 0
7 END) "1-10",
8 SUM(CASE
9 WHEN FIELD2 >= 11 AND FIELD2 <= 20 THEN
10 1
11 ELSE
12 0
13 END) "10-20"
14 FROM TEST_TT
15 GROUP BY FIELD1;FIELD1 1-10 10-20
---------- ---------- ----------
A 3 1
B 1 2
C 0 1SQL>
CREATE TABLE t5(a VARCHAR2(10),b INT);
INSERT INTO T5 VALUES ('A', 5);
INSERT INTO T5 VALUES ('A', 6);
INSERT INTO T5 VALUES ('A', 1);
INSERT INTO T5 VALUES ('A', 18);
INSERT INTO T5 VALUES ('B', 3);
INSERT INTO T5 VALUES ('B', 13);
INSERT INTO T5 VALUES ('B', 14);
INSERT INTO T5 VALUES ('C', 19);
SELECT a,COUNT(x) is_1_10,COUNT(y) is_11_20 FROM
(SELECT A,
CASE
WHEN B >= 1 AND B <= 10 THEN
1
ELSE
NULL
END x,
CASE
WHEN B >= 11 AND B <= 20 THEN
1
ELSE
NULL
END y
FROM T5
)
GROUP BY a;
输出:
A IS_1_10 IS_11_20
A 3 1
B 1 2
C 0 1
sum(
case
when 字段2>=1 and 字段2<=10 then 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2<=20 then 1
else 0
end
) "11-20"
from a
group by 字段1
INSERT INTO T5 VALUES ('A', 5);
INSERT INTO T5 VALUES ('A', 6);
INSERT INTO T5 VALUES ('A', 1);
INSERT INTO T5 VALUES ('A', 18);
INSERT INTO T5 VALUES ('B', 3);
INSERT INTO T5 VALUES ('B', 13);
INSERT INTO T5 VALUES ('B', 14);
INSERT INTO T5 VALUES ('C', 19);
SELECT a,COUNT(x) is_1_10,COUNT(y) is_11_20 FROM
(SELECT A,
CASE
WHEN B >= 1 AND B <= 10 THEN
1
ELSE
NULL
END x,
CASE
WHEN B >= 11 AND B <= 20 THEN
1
ELSE
NULL
END y
FROM T5
)
GROUP BY a;
输出:
A IS_1_10 IS_11_20
A 3 1
B 1 2
C 0 1
sum(
case
when 字段2>=1 and 字段2 <=10 then 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2 <=20 then 1
else 0
end
) "11-20"
from a
group by 字段1题比较简单 可惜来晚了
sum(
case
when 字段2 between 1 and 10 then 1
else 0
end
) "1-10",
sum(
case
when 字段2 between 11 and 20 then 1
else 0
end
) "11-20"
from tablename
group by 字段1
(select count(*) from 表 where 字段2 between 11 and 20) "11-20" FROM 表
错了,用下面的select distinct 字段1,(select count(*) from 表 where 字段1=A.字段1 and 字段2 between 1 and 10) "1-10",(select count(*) from 表 where 字段1=A.字段1 and 字段2 between 11 and 20) "11-20" FROM 表 A
用子查询的,效率会低于用DECODE和CASE WHEN的
sum(case when 字段2>=1 and 字段2<=10 then 1 else 0 end) as "1-10",
sum(case when 字段2>=11 and 字段2<=20 thne 1 else 0 end) as "11-20",
from a
group by 字段1
这样写比较清楚一点!
sum(
case
when 字段2>=1 and 字段2<=10 thne 1
else 0
end
) "1-10",
sum(
case
when 字段2>=11 and 字段2<=20 thne 1
else 0
end
) "11-20",
from a
group by 字段1
SELECT FIELD1,
count(CASE
WHEN FIELD2 >= 1 AND FIELD2 <= 10 THEN
1
ELSE
NULL
END) "1-10",
count(CASE
WHEN FIELD2 >= 11 AND FIELD2 <= 20 THEN
1
ELSE
NULL
END) "10-20"
FROM TAB_A
GROUP BY FIELD1;