比如表里有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
这样重复的数据呢?谢谢大家
解决方案 »
- 求助,存储过程调用出错
- 菜鸟求教一条统计SQL的写法
- 有没有谁能帮我把这个C#方法改写成oracle存储过程啊,小弟感激不尽。
- 大虾们!!!!!!!如何获得一个序列号的当前值呢?
- IMP 出错
- 求一份oracle Sql命令大全,最好是pdf版
- 紧急求救:关于oracle数据库的恢复问题
- ***高手求助了!本人在用ADO.net连接Oracle8.0数据库时出现错误提示,向各位高手求助!谢谢了!!!***
- oracle9i连接时提示'无监听',请问如何解决?
- 配置odbc安装orcal驱动提示Could't find Oracle Instant Client in present directory.怎么整
- 送分100分:请问SQL SERVER2000中的master、pubs、northwind等数据库,在ORACLE中有没有同样的东西
- 这个触发器该怎么写?
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>