关于distinct的有关问题
关于distinct的问题
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 求高手帮忙
------解决方案--------------------
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 求高手帮忙
------解决方案--------------------
- SQL code
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
------解决方案--------------------
------解决方案--------------------
不知道这样可以不 反正效率会低很多
- SQL code
select distinct manager,type,lv,time, (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
------解决方案--------------------