求一个语句？

Question

CITY SPORT
天津 足球
北京 篮球
天津 篮球
上海 排球
天津 足球
北京 网球
上海 篮球
北京 足球
上海 足球
上海 网球
天津 篮球在以上的表中，要统计三个城市都开展的体育运动及其数量，统计结果为足球、篮球，数量为2。
希望能提供一下代码，多谢！

Answer 1

select sport,count(distinct city) from tb
group by sport
having count(distinct city)=3

Answer 2

这个有点难度
估计需要２个not exists才行。

Answer 3

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

Answer 4

不用exists也可以的
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

Answer 5

我的看看符合楼主的要求不？
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