我的表数据是类似下方:
orderno product1 product2 product3 ......123123 A B C ......234234 B C D ......345345 B C D ......上面的数据我想要查出如商品B和商品C出现在了订单123123和234234 这样是算出现两次,
还有B、C、D三件商品出现在订单234234和345345中,也是出现了两次
orderno product1 product2 product3 ......123123 A B C ......234234 B C D ......345345 B C D ......上面的数据我想要查出如商品B和商品C出现在了订单123123和234234 这样是算出现两次,
还有B、C、D三件商品出现在订单234234和345345中,也是出现了两次
select product1 as goods,count(1) as num from tb group by product1
union all
select product2,count(1) from tb group by product2
union all
select product3,count(1) from tb group by product3
...
)
group by goods
(
OrderID VARCHAR2(20),
Prod1 VARCHAR2(20),
Prod2 VARCHAR2(20),
Prod3 VARCHAR2(20),
Prod4 VARCHAR2(20)
);INSERT INTO T119 VALUES('01', 'A', 'B', 'C', NULL);
INSERT INTO T119 VALUES('02', 'B', 'C', 'D', 'E');
INSERT INTO T119 VALUES('03', 'B', 'C', 'D', NULL);
实测结果:
(
OrderID VARCHAR2(20),
Prod1 VARCHAR2(20),
Prod2 VARCHAR2(20),
Prod3 VARCHAR2(20),
Prod4 VARCHAR2(20)
);INSERT INTO T119 VALUES('01', 'A', 'B', 'C', NULL);
INSERT INTO T119 VALUES('02', 'B', 'C', 'D', 'E');
INSERT INTO T119 VALUES('03', 'B', 'C', 'D', NULL);CREATE VIEW ViewT119 AS
SELECT prod1 || ',' || Prod2 || ',' || Prod3 || ',' || Prod4 AS Goods
FROM T119;
SELECT SUBSTR(Goods, 1, INSTR(Goods, ',', 1)-1) AS Goods1, COUNT(1) cn
FROM ViewT119
GROUP BY SUBSTR(Goods, 1, INSTR(Goods, ',', 1)-1)
HAVING COUNT(1) >= 2UNION SELECT SUBSTR(Goods, 1, INSTR(Goods, ',', 3) - 1) AS Goods1, COUNT(1) cn
FROM ViewT119
GROUP BY SUBSTR(Goods, 1, INSTR(Goods, ',', 3) - 1)
HAVING COUNT(1) >= 2UNION SELECT SUBSTR(Goods, 1, INSTR(Goods, ',', 5) - 1) AS Goods1, COUNT(1) cn
FROM ViewT119
GROUP BY SUBSTR(Goods, 1, INSTR(Goods, ',', 5) - 1)
HAVING COUNT(1) >= 2
实测结果:
'01', 'A', 'B', 'C', NULL);
'02', 'B', 'C', 'D', 'E');
'03', 'B', 'C', 'D', NULL);想要的结果就是:B、C 3
B、C、D 2
1,先将不同productid 查询出来
2,将版主提供的那张表的所有的productid连起来
3,在2的那个字符串中搜索productid 第二次出现的位置,不等于0,出现2次
with tb as(select '123123' orderno, 'A' product1, 'B' product2, 'C' product3 from dual union all
select '234234', 'B', 'C', 'D' from dual union all
select '345345', 'B', 'C', 'D' from dual )
select productid from (select product1 productid from tb
union
select product2 productid from tb
union
select product3 productid from tb)tb1
where instr((select replace(wm_concat(product1||product2||product3),',','')from tb),tb1.productid,1,2)<>0
使用WM_CONCAT会提示“操作数值超出系统的限制”,还是不能实现,实在麻烦各位了!!!!
orderno 的productid 是不同的