求解！！！！

显示全部楼层 · 2013-2-25 14:51:24

表T
ab
10
11
22
25
23
32
37
44
49
41
40
我想知道表T里a字段有多少个值，每个值有多少条数据？？求sql！！！！！！！！！！！！！！！！！！

千问 · 2013-2-25 14:51:24

count(*)
group by a

千问 · 2013-2-25 14:51:24

新手路过 .
WITH T AS(
SELECT '1' A,'0' B FROM DUAL
UNION ALL
SELECT '1' A,'1' B FROM DUAL
UNION ALL
SELECT '1' A,'2' B FROM DUAL
UNION ALL
SELECT '2' A,'2' B FROM DUAL
UNION ALL
SELECT '2' A,'5' B FROM DUAL
UNION ALL
SELECT '2' A,'3' B FROM DUAL
UNION ALL
SELECT '3' A,'2' B FROM DUAL
UNION ALL
SELECT '3' A,'7' B FROM DUAL
UNION ALL
SELECT '4' A,'4' B FROM DUAL
UNION ALL
SELECT '4' A,'9' B FROM DUAL
UNION ALL
SELECT '4' A,'1' B FROM DUAL
UNION ALL
SELECT '4' A,'0' B FROM DUAL
)
select a, count(a) from t group by a;

千问 · 2013-2-25 14:51:24

select count(distinct a)from t;
select distinct a, count(b) over (partition by a) from t;

千问 · 2013-2-25 14:51:24

b是没有用的？

千问 · 2013-2-25 14:51:24

select owner, count(*) over (), count(*) from t group by owner;

千问 · 2013-2-25 14:51:24

udfrog 发表于 2013-2-5 16:28
select owner, count(*) over (), count(*) from t group by owner;
这个写法是对的
不过4#分开的做法更好，尤其是当a值较多的时候

千问 · 2013-2-25 14:51:24

就是个分组而已

千问 · 2013-2-25 14:51:24

dingjun123 发表于 2013-2-16 20:19
就是个分组而已
版主，你要教教新人哈，呵呵
在公司里带新人真的是手把手教呀