关于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 dualunion allselect '20120411181706','9999','0','104' from dualunion allselect '20120411181706','9999','0','107' from dualunion allselect '20120411181706','9999','0','104' from dualunion allselect '20120411181734','9999','1','104' from dualunion allselect '20120411181734','9999','1','104' from dualunion allselect '20120411181734','9999','1','107' from dualunion allselect '20120411181734','9999','1','104' from dual)select manager,type,lv,time,count(*) from t group by time,manager,type,lvMANAGER TYPE LV  TIME             COUNT(*)------- ---- --- -------------- ----------9999    0    104 20120411181706          39999    0    107 20120411181706          19999    1    104 20120411181734          39999    1    107 20120411181734          1
[解决办法]
探讨

我还需要根据9999找出对应的姓名
104之类的也是 要去别的表找出姓名 怎么办啊 那这个分组吗？

[解决办法]
不知道这样可以不  反正效率会低很多
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_numfrom t
[解决办法]
探讨


有不用group by 用前面distinct的写法吗