time manager type LV
20120411181706 9999 0 104
20120411181706 9999 0 104
20120411181706 9999 0 107
20120411181706 9999 0 104
20120411181734 9999 1 104
20120411181734 9999 1 104
20120411181734 9999 1 107
20120411181734 9999 1 104
我希望得到这样的结果9999 0 104 20120411181706 3
9999 0 107 20120411181706 1
9999 1 104 20120411181706 3
9999 1 107 20120411181706 1最后一列是count distinct后 还是组合在了一起 不能每个都distinct 求高手帮忙
20120411181706 9999 0 104
20120411181706 9999 0 104
20120411181706 9999 0 107
20120411181706 9999 0 104
20120411181734 9999 1 104
20120411181734 9999 1 104
20120411181734 9999 1 107
20120411181734 9999 1 104
我希望得到这样的结果9999 0 104 20120411181706 3
9999 0 107 20120411181706 1
9999 1 104 20120411181706 3
9999 1 107 20120411181706 1最后一列是count distinct后 还是组合在了一起 不能每个都distinct 求高手帮忙
with t as(
select '20120411181706' time,'9999' manager,'0' type,'104' LV from dual
union all
select '20120411181706','9999','0','104' from dual
union all
select '20120411181706','9999','0','107' from dual
union all
select '20120411181706','9999','0','104' from dual
union all
select '20120411181734','9999','1','104' from dual
union all
select '20120411181734','9999','1','104' from dual
union all
select '20120411181734','9999','1','107' from dual
union all
select '20120411181734','9999','1','104' from dual
)select manager,type,lv,time,count(*) from t group by time,manager,type,lv
MANAGER TYPE LV TIME COUNT(*)
------- ---- --- -------------- ----------
9999 0 104 20120411181706 3
9999 0 107 20120411181706 1
9999 1 104 20120411181734 3
9999 1 107 20120411181734 1
直接用这个关联姓名表就可以了 借用1L的select manager,a.name,type,lv,time,count(*)
from t,a where a.?=t.type
group by time,manager,type,lv
distinct效率不怎么高 要做的话也可以 只能前面distinct 后面子查询来关联一次表 查询出相关的数量
(select count(1)
from t t1
where t1.manager=t.manager and t1.type=t.type and t1.lv=t.lv and t1.time=t.time) t_num
from t
换汤不换药的
with t as(
select '20120411181706' time,'9999' manager,'0' type,'104' LV from dual
union all
select '20120411181706','9999','0','104' from dual
union all
select '20120411181706','9999','0','107' from dual
union all
select '20120411181706','9999','0','104' from dual
union all
select '20120411181734','9999','1','104' from dual
union all
select '20120411181734','9999','1','104' from dual
union all
select '20120411181734','9999','1','107' from dual
union all
select '20120411181734','9999','1','104' from dual
)
select distinct time,manager,type,lv,count(*) over(partition by time,manager,type,lv ) from t