DROP TABLE IF EXISTS tb; CREATE TABLE tb( 订单号 varchar(10), 商品 varchar(10)); INSERT tb VALUES ('订单1', '苹果'), ('订单1', '香蕉'), ('订单1', '西红柿'), ('订单2', '西红柿'), ('订单2', '香蕉'), ('订单3', '香蕉'), ('订单3', '苹果');SELECT 商品.商品1, 商品.商品2, COUNT(DISTINCT D1.订单号) as 订单数 FROM( SELECT A.商品 as 商品1, B.商品 as 商品2 FROM (SELECT DISTINCT 商品 FROM tb) A, (SELECT DISTINCT 商品 FROM tb) B WHERE A.商品 < B.商品 ) 商品 INNER JOIN tb D1 ON D1.商品 = 商品.商品1 WHERE EXISTS( SELECT * FROM tb D2 WHERE D2.订单号 = D1.订单号 AND D2.商品 = 商品.商品2 ) GROUP BY 商品.商品1, 商品.商品2;
select count(*) from ( select column1 from tb group by column1 having count(distinct column2)=2 )T
select a.商品,b.商品,count(*) from table1 a , table1 b where a.订单=b.订单 and a.商品>b.商品 group by a.商品,b.商品
CREATE TABLE tb( 订单号 varchar(10), 商品 varchar(10));
INSERT tb VALUES
('订单1', '苹果'),
('订单1', '香蕉'),
('订单1', '西红柿'),
('订单2', '西红柿'),
('订单2', '香蕉'),
('订单3', '香蕉'),
('订单3', '苹果');SELECT 商品.商品1, 商品.商品2, COUNT(DISTINCT D1.订单号) as 订单数
FROM(
SELECT A.商品 as 商品1, B.商品 as 商品2
FROM (SELECT DISTINCT 商品 FROM tb) A,
(SELECT DISTINCT 商品 FROM tb) B
WHERE A.商品 < B.商品
) 商品
INNER JOIN tb D1 ON D1.商品 = 商品.商品1
WHERE EXISTS(
SELECT * FROM tb D2
WHERE D2.订单号 = D1.订单号
AND D2.商品 = 商品.商品2 )
GROUP BY 商品.商品1, 商品.商品2;
select column1
from tb
group by column1
having count(distinct column2)=2
)T
from table1 a , table1 b
where a.订单=b.订单
and a.商品>b.商品
group by a.商品,b.商品