一道比較難的sql題，篩選出重復字段的行數

select * from 導入數據表;?? ?

id?? ?city_column
1?? ?北京,上海,廣州
2?? ?上海,上海,深圳
3?? ?北京,杭州,北京
4?? ?上海,廣州,深圳

select substring_index(khmc,',',1), * ?from 導入數據表?? ?

truncate table 導入數據表?? ?

select count(distinct khmc) from 導入數據表;?? ?

select count(*) ?? ?
from 導入數據表?? ?
where length(khmc)-length(replace(khmc),',','') +1?? ?

select ?trim(substring_index(substring_index(khmc,',',n),',',-1)), * from 導入數據表?? ?
select ?length(khmc), length(khmc)-length(REGEXP_REPLACE(khmc,',','')) +1 from 導入數據表?? ?

-----?? ?

select xh, city, count(*)?? ?
from(?? ?
select /*+ MAPJOIN ( t1 ) */?? ?
xh,?? ?
trim(substring_index(substring_index(khmc,',',n),',',-1)) as city?? ?
from 導入數據表 t1 ?? ?
join ?? ?
(select 1 as n union select 2 union select 3) numbers?? ?
on numbers.n <= length(t1.khmc)-length(REGEXP_REPLACE(t1.khmc,',','')) +1?? ?
) group by xh, city ?? ?
having count(*) > 1?? ?

select sum(cnt) from?? ?
(?? ?
select count(distinct xh) as cnt ?? ?
from(?? ?
select /*+ MAPJOIN ( t1 ) */?? ?
xh,?? ?
trim(substring_index(substring_index(khmc,',',n),',',-1)) as city?? ?
from 導入數據表 t1 ?? ?
join ?? ?
(select 1 as n union select 2 union select 3) numbers?? ?
on numbers.n <= length(t1.khmc)-length(REGEXP_REPLACE(t1.khmc,',','')) +1?? ?
) group by xh, city ?? ?
having count(*) > 1

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/95124.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/95124.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/95124.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！