CITY SPORT
天津 足球
北京 篮球
天津 篮球
上海 排球
天津 足球
北京 网球
上海 篮球
北京 足球
上海 足球
上海 网球
天津 篮球在以上的表中,要统计三个城市都开展的体育运动及其数量,统计结果为足球、篮球,数量为2。
希望能提供一下代码,多谢!
天津 足球
北京 篮球
天津 篮球
上海 排球
天津 足球
北京 网球
上海 篮球
北京 足球
上海 足球
上海 网球
天津 篮球在以上的表中,要统计三个城市都开展的体育运动及其数量,统计结果为足球、篮球,数量为2。
希望能提供一下代码,多谢!
解决方案 »
- Oracle的存储过程是什么
- IMP-00041: 警告: 创建的对象带有编译警告,请高手指教
- 关于“not in”的问题
- 表名作为参数,写一个接口,把数据写到OS文件上
- oracle是否新建一个数据库,就多一个相应的服务?
- "ORA-01031:insufficient privileges"错误,不知大家有没有遇到过?
- 如何插入日期格式的数据(oracle数据库)
- Enterprise Manager Console创建的序列如何与表中一字段绑定起来????
- ORACLE数据库INTERNAL用户默认密码是多少?
- 菜鸟提问:在ORACLE下如何建立关联
- pl/sql实现多元线性回归方程
- Oracle 字符串转换成数字型
select sport,count(distinct city) from tb
group by sport
having count(distinct city)=3
估计需要2个not exists才行。
create table t(city varchar2(20),sport varchar2(20));
insert into t values('天津','足球');
insert into t values('北京','篮球');
insert into t values('天津','篮球');
insert into t values('上海','排球');
insert into t values('天津','足球');
insert into t values('北京','网球');
insert into t values('上海','篮球');
insert into t values('北京','足球');
insert into t values('上海','足球');
insert into t values('上海','网球');
insert into t values('天津','篮球');
commit;SQL> select * from t;
CITY SPORT
-------------------- --------------------
天津 足球
北京 篮球
天津 篮球
上海 排球
天津 足球
北京 网球
上海 篮球
北京 足球
上海 足球
上海 网球
天津 篮球
11 rows selectedSELECT (SELECT wmsys.wm_concat(column_value) FROM TABLE(itemset)) itemset,
support,
length,
total_tranx
FROM TABLE(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(CURSOR (SELECT * FROM t),
1e-5,
1,
20))
WHERE support>=3;ITEMSET SUPPORT LENGTH TOTAL_TRANX
-------------------- ---------- ---------- -----------
篮球 3 1 3
足球 3 1 3
篮球,足球 3 2 3WITH a AS(
SELECT (SELECT wmsys.wm_concat(column_value) FROM TABLE(itemset)) itemset,
support,
length,
total_tranx
FROM TABLE(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(CURSOR (SELECT * FROM t),
1e-5,
1,
20))
WHERE support>=3) --三个或三个城市以上
SELECT *
FROM a a0
WHERE NOT EXISTS( --过滤掉子集
SELECT 1 FROM a a1
WHERE a1.length>a0.length
AND regexp_replace(a0.itemset,REPLACE(a1.itemset,',','|')) IS NULL);ITEMSET SUPPORT LENGTH TOTAL_TRANX
-------------------- ---------- ---------- -----------
篮球,足球 3 2 3
CREATE TABLE TEST
(CITY VARCHAR2(10),
SPORT VARCHAR2(10)
);INSERT INTO TEST VALUES ('天津','足球');
INSERT INTO TEST VALUES ('北京','篮球');
INSERT INTO TEST VALUES ('天津','篮球');
INSERT INTO TEST VALUES ('上海','排球');
INSERT INTO TEST VALUES ('天津','足球');
INSERT INTO TEST VALUES ('北京','网球');
INSERT INTO TEST VALUES ('上海','篮球');
INSERT INTO TEST VALUES ('北京','足球');
INSERT INTO TEST VALUES ('上海','足球');
INSERT INTO TEST VALUES ('上海','网球');
INSERT INTO TEST VALUES ('天津','篮球');
COMMIT;--------------查询语句
SELECT M.SPORT,
MAX(M.ST_NUM) AS ST_NUM ------每个城市都出现的SPORT的最大数量
FROM (SELECT T.*,
COUNT(DISTINCT R.CITY) OVER() ALL_CITY_NUM,
COUNT(T.SPORT) OVER(PARTITION BY T.SPORT) ACTUAL_SPORT_NUM
FROM (SELECT CITY,
SPORT
FROM (SELECT DISTINCT CITY,
1 AS LINK_COL
FROM TEST) T1,
(SELECT DISTINCT SPORT,
1 AS LINK_COL
FROM TEST) T2
WHERE T1.LINK_COL = T2.LINK_COL) R, ----- CITY,SPORT的全组合
(SELECT CITY,
SPORT,
COUNT(*) AS ST_NUM
FROM TEST
GROUP BY CITY,
SPORT) T ------
WHERE R.CITY = T.CITY(+)
AND R.SPORT = T.SPORT(+)
ORDER BY R.CITY,
R.SPORT) M
WHERE M.ALL_CITY_NUM = ACTUAL_SPORT_NUM ------ 选出每个城市都出现的SPORT的记录
GROUP BY M.SPORT
-------------- 结果
SPORT ST_NUM
---------- ----------
足球 2
篮球 2
SQL> col sport for a10
SQL> ed
已写入 file afiedt.buf 1 select wmsys.wm_concat(sport) sport,count(sport) cnt
2 from
3 (select DISTINCT sport from t T1
4 where not exists
5 (select * FROM T T2 where not exists
6 (select * from t t3
7* where t2.city=t3.city and t1.sport=t3.sport))) t4
SQL> /SPORT CNT
---------- ----------
足球,篮球 2