比如表里有3项
milk
bear
bread如何只输出
milk bear
milk bread
bear bread而不会输出
milk bear
milk bread
bear bread
bear milk
bread milk
bread bear
这样重复的数据呢?谢谢大家
milk
bear
bread如何只输出
milk bear
milk bread
bear bread而不会输出
milk bear
milk bread
bear bread
bear milk
bread milk
bread bear
这样重复的数据呢?谢谢大家
where a.name<>b.name
where a.name<>b.name
用distinct后输出的还是有位置对调的重复的
where a.name<>b.name
where a.name<>b.name
当然按你的规则,如果基表条数是n,那么实际输出的条数为n*(n-1)/2条
已写入 file afiedt.buf 1 with tb as
2 (select 'milk' name1,'bear' name2 from dual union all
3 select 'milk','bread' from dual union all
4 select 'bear','bread' from dual union all
5 select 'bear','milk' from dual union all
6 select 'bread','milk' from dual union all
7 select 'bread','bear' from dual)
8 select distinct case when name1>name2 then name1 else name2 end v1,
9 case when name1>name2 then name2 else name1 end v2
10* from tb
SQL> /V1 V2
----- -----
milk bear
bread bear
milk bread
SELECT DISTINCT
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM1
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM2
ELSE
ONE.ITEM3
end,
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM3
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM2
ELSE
ONE.ITEM1
end,
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM2
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM1
ELSE
ONE.ITEM3
end,
COUNTER
FROM ONE
WHERE (ONE.ITEM1!=ONE.ITEM2) AND (ONE.ITEM2!=ONE.ITEM3) AND (ONE.ITEM1!=ONE.ITEM3);
with temp as
(select 'milk' name
from dual
union all
select 'bear' name
from dual
union all
select 'bread' name from dual
union all
select 'aaaa' name from dual
)
select t.name || t1.name from (select rownum rm , name from temp) t , (select rownum rm , name from temp) t1
where t1.rm > t.rm
应该可以
CREATE TABLE BASKET(
B# NUMBER(12) NOT NULL,
ITEM VARCHAR(50) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );INSERT INTO BASKET VALUES( 1, 'MILK');
INSERT INTO BASKET VALUES( 1, 'BUTTER');
INSERT INTO BASKET VALUES( 1, 'BREAD');
INSERT INTO BASKET VALUES( 2, 'BEER');
INSERT INTO BASKET VALUES( 2, 'BREAD');
INSERT INTO BASKET VALUES( 3, 'MILK');然后我先建个view,列出3种的所有可能性,这时候三种组合里面是没有重复的,就是说一个组合里有3项item,每个都不一样,然后有很多的组合,但是各个组合中间会有顺序不一样的重复
CREATE VIEW ONE(ITEM1, ITEM2, ITEM3, COUNTER) AS
(
SELECT B1.ITEM, B2.ITEM, B3.ITEM, COUNT(*) FROM
BASKET B1 JOIN BASKET B2
ON B1.B# = B2.B#
AND B1.ITEM != B2.ITEM
JOIN BASKET B3
ON B3.B# = B1.B#
AND B3.ITEM != B1.ITEM
AND B3.ITEM != B2.ITEM
GROUP BY B1.ITEM, B2.ITEM, B3.ITEM
);
然后又建个view来筛选前一个view,但是这样过后组合里面的东西都有重复的了,就是一个组合里3项item,有可能两项是一样的CREATE VIEW THREE(ITEM1, ITEM2, ITEM3, COUNTER) AS
(
SELECT DISTINCT
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM1
when (ONE.ITEM1<ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM2
ELSE
ONE.ITEM3
end,
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM3
when (ONE.ITEM1<ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM2
ELSE
ONE.ITEM1
end,
case
when (ONE.ITEM1>ONE.ITEM2) AND (ONE.ITEM2 > ONE.ITEM3) then ONE.ITEM2
when (ONE.ITEM1<ONE.ITEM2) AND (ONE.ITEM2 < ONE.ITEM3) then ONE.ITEM1
ELSE
ONE.ITEM3
end,
COUNTER
FROM ONE
WHERE (ONE.ITEM1!=ONE.ITEM2) AND (ONE.ITEM2!=ONE.ITEM3) AND (ONE.ITEM1!=ONE.ITEM3)
);
with temp as
(select 'a' name from dual
union all
select 'b' name from dual
union all
select 'c' name from dual
union all
select 'd' name from dual
)
select t.name || t1.name || t2.name result
from (select rownum rm, name from temp) t,
(select rownum rm, name from temp) t1,
(select rownum rm, name from temp) t2
where t1.rm > t.rm and t2.rm > t1.rm order by result;呵呵,加了个,好像可以
BREAD 3
BUTTER 2
MILK 1
BREAD, BUTTER 2
BREAD, MILK 1
BUTTER, MILK 1
BREAD, BUTTER, MILK 1也就是统计3项一下每种组合的出现次数只有一项和两项已经解决了,3项的不知道应该怎么解决
with temp as
(select 'a' name from dual
union all
select 'b' name from dual
union all
select 'c' name from dual
union all
select 'd' name from dual
union all
select 'd' name from dual
union all
select 'c' name from dual
union all
select 'e' name from dual
union all
select 'a' name from dual
union all
select 'd' name from dual
union all
select 'e' name from dual
)
select distinct t1.name || t2.name || t3.name result
from (select rownum rm ,t.name from (select distinct name from temp) t) t1,
(select rownum rm ,t5.name from (select distinct name from temp) t5) t2,
(select rownum rm ,t4.name from (select distinct name from temp) t4) t3
where t2.rm > t1.rm and t2.rm > t3.rm order by result
试试
ID NAME
----------- ----------
53 bread
53 milk
53 butter
54 bread
54 butter
55 bread
6 rows selected
SQL>
SQL> with t1 as ( select id,name,row_number()over(partition by id order by name asc) rn ,count(*)over(partition by id) cnt from tbl_sort),
2 t2 as (select rownum rm from dual connect by rownum <=(select max(cnt) from t1)),
3 t3 as (select rownum rm from dual connect by rownum <=(select max(cnt) from t1)),
4 t4 as (select t2.rm,t3.rm rm1 ,t1.id,wm_concat(name) name from t1,t2,t3 where t1.rn between t2.rm and t3.rm group by t2.rm,t3.rm ,t1.id),
5 t5 as (select id,name from t4 group by id,name )
6 select name,count(*) from t5 group by name;
NAME COUNT(*)
-------------------------------------------------------------------------------- ----------
bread 3
bread,butter 2
bread,butter,milk 1
bread,milk,butter 1
butter 2
butter,milk 1
milk 1
7 rows selected
SQL>
SQL> with t1 as ( select id,name,row_number()over(partition by id order by name asc) rn ,count(*)over(partition by id) cnt from tbl_sort),
2 t2 as (select rownum rm from dual connect by rownum <=(select max(cnt) from t1)),
3 t3 as (select rownum rm from dual connect by rownum <=(select max(cnt) from t1)),
4 t4 as (select distinct t2.rm,t3.rm rm1 ,t1.id, name from t1,t2,t3 where t1.rn between t2.rm and t3.rm ),
5 t5 as (select rm,rm1,id,wm_concat(name)over(partition by id,rm,rm1 order by name asc) name from t4),
6 t6 as (select distinct id,name from t5)
7 select name,count(*) from t6 group by name;
NAME COUNT(*)
-------------------------------------------------------------------------------- ----------
bread 3
bread,butter 2
bread,butter,milk 1
butter 2
butter,milk 1
milk 1
6 rows selected
SQL>