有一个表有mac和accountnumber字段,大概取值如下:
mac accountnumber
001 101
001 102
002 103
002 103
003 104
005 105
006 105
006 104
007 空
我现在需要sql语句或者存储过程呀,游标都可以,需要实现1个MAC地址只用过1个帐号情况的MAC地址数量,
1个MAC地址只用过2个帐号情况的MAC地址数量,
1个MAC地址只用过6-9个帐号情况的MAC地址数量。
求解
mac accountnumber
001 101
001 102
002 103
002 103
003 104
005 105
006 105
006 104
007 空
我现在需要sql语句或者存储过程呀,游标都可以,需要实现1个MAC地址只用过1个帐号情况的MAC地址数量,
1个MAC地址只用过2个帐号情况的MAC地址数量,
1个MAC地址只用过6-9个帐号情况的MAC地址数量。
求解
解决方案 »
- insert 语句问题
- 这样的要求怎么去实现呢?
- 请问:在oracle的存储过程中,如果获得查询语句的列数和各个列名?
- 奇怪的ora-12514
- odp.net 连接到oracle: “Oracle.DataAccess.Client.OracleConnection”的类型初始值设定项引发异常。
- 高手请看:这两个SQL语句效率上有什么区别?
- 怎么将一个库的数据导到同样结构的新一个库里去,急急急,解决马上结帐!!!
- oracle在linux下安装出错.
- 我如何管理配置我的第二个数据库?急!!在线等
- EXP-IMP問題,老問題還是解決不了.
- 关于ProC 的语法一点问题
- 配置数据源的问题。。。。。。
SELECT COUNT(MAC)
FROM (SELECT MAC, COUNT(ACCOUNTNUMBER)
FROM TABLE_NAME
GROUP BY MAC
HAVING COUNT(MAC) = 1);--
SELECT COUNT(MAC)
FROM (SELECT MAC, COUNT(ACCOUNTNUMBER) OVER(PARTITION BY MAC) CNT
FROM TABLE_NAME)
WHERE CNT = 1;
COUNT(CASE WHEN CNT = 2 THEN MAC ELSE NULL END) CNT2,
COUNT(CASE WHEN CNT >= 6 AND CNT <= 9 THEN MAC ELSE NULL END) CNT6_9
FROM (SELECT MAC, COUNT(DISTINCT ACCOUNTNUMBER) CNT
FROM TABLE_NAME
GROUP BY MAC);
这个打印出来的结果和SELECT COUNT(MAC)
FROM (SELECT MAC, COUNT(ACCOUNTNUMBER)
FROM TABLE_NAME
GROUP BY MAC
HAVING COUNT(MAC) = 1);
打印出来的结果不一样。当1个MAC地址只用过1个帐号情况的MAC地址数量,2个sql语句出来的结果不一样,第一个sql语句要比第二种sql语句汇总的结果要少
mac accountnumber
001 101
001 101
mac accountnumber
001 101
001 101用distinct 为1,不用distinct 为0,用的自然比不用多。
原因是本身因为重复不算到1个里面的,因为剃重而算了,自然要多了。
with t as
(
select '001' as mac, '101' as accountnumber from dual
union all
select '001' as mac, '102' as accountnumber from dual
union all
select '002' as mac, '103' as accountnumber from dual
union all
select '002' as mac, '103' as accountnumber from dual
union all
select '003' as mac, '104' as accountnumber from dual
union all
select '001' as mac, '105' as accountnumber from dual
union all
select '005' as mac, '105' as accountnumber from dual
union all
select '006' as mac, '104' as accountnumber from dual
union all
select '006' as mac, '101' as accountnumber from dual
union all
select '007' as mac, null as accountnumber from dual
union all
select '001' as mac, '111' as accountnumber from dual
union all
select '001' as mac, '112' as accountnumber from dual
union all
select '001' as mac, '113' as accountnumber from dual
union all
select '001' as mac, '114' as accountnumber from dual
union all
select '001' as mac, '115' as accountnumber from dual
)
select '一个MAC地址只用过2个账号的MAC数量' as type, count(*) as icount
from (select mac, count(distinct accountnumber)
from t where accountnumber is not null
group by mac
having count(distinct accountnumber) = 2)
union all
select '一个MAC地址只用过6-9个账号的MAC数量' as type, count(*) as icount
from (select mac, count(distinct accountnumber)
from t where accountnumber is not null
group by mac
having count(distinct accountnumber) between 6 and 9)
然后就返回数量就OK了。