哪三种商品组合满足的订单数最多，即求客户同时买了任意3种商品的订单数，用SQL语...

buguiize

哪三种商品组合满足的订单数最多，即求客户同时买了任意3种商品的订单数，用SQL语句。
即求客户既买了A商品又买了B商品也买了C商品的订单数

buguiize

得到的类似这样的结果！

buguiize

不要沉不要沉不要沉不要沉

zxb7679

SELECT B.三组合, B.订单号之计算
FROM (SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELECT 订单.订单号, 订单.商品, T1.商品, T2.商品, 订单.商品 & T1.商品 & T2.商品 AS 三组合
FROM (订单 INNER JOIN 订单 AS T1 ON 订单.订单号=T1.订单号) INNER JOIN 订单 AS T2 ON T1.订单号=T2.订单号
WHERE ((订单.商品>T1.商品) AND (T1.商品>T2.商品)))  AS A
GROUP BY A.三组合)  AS B
WHERE (((B.订单号之计算)=(SELECT Max(B.订单号之计算)
FROM
(SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELECT 订单.订单号, 订单.商品, T1.商品, T2.商品, 订单.商品 & T1.商品 & T2.商品 AS 三组合
FROM (订单 INNER JOIN 订单 AS T1 ON 订单.订单号=T1.订单号) INNER JOIN 订单 AS T2 ON T1.订单号=T2.订单号
WHERE ((订单.商品>T1.商品) AND (T1.商品>T2.商品)))  AS A
GROUP BY A.三组合) as B)));

buguiize

zxb7679 发表于 2017-3-25 18:35
SELECT B.三组合, B.订单号之计算
FROM (SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELEC ...

请教如果取出最大值前10项，语法如何写？

zxb7679

SELECT B.三组合, B.订单号之计算
FROM (SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELECT 订单.订单号, 订单.商品, T1.商品, T2.商品, 订单.商品 & T1.商品 & T2.商品 AS 三组合
FROM (订单 INNER JOIN 订单 AS T1 ON 订单.订单号=T1.订单号) INNER JOIN 订单 AS T2 ON T1.订单号=T2.订单号
WHERE ((订单.商品>T1.商品) AND (T1.商品>T2.商品)))  AS A
GROUP BY A.三组合)  AS B
WHERE (((B.订单号之计算)in (SELECT TOP 10 B.订单号之计算
FROM
(SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELECT 订单.订单号, 订单.商品, T1.商品, T2.商品, 订单.商品 & T1.商品 & T2.商品 AS 三组合
FROM (订单 INNER JOIN 订单 AS T1 ON 订单.订单号=T1.订单号) INNER JOIN 订单 AS T2 ON T1.订单号=T2.订单号
WHERE ((订单.商品>T1.商品) AND (T1.商品>T2.商品)))  AS A
GROUP BY A.三组合
ORDER BY Count(A.订单号) DESC) as B)));

buguiize

zxb7679 发表于 2017-3-29 16:41
SELECT B.三组合, B.订单号之计算
FROM (SELECT A.三组合, Count(A.订单号) AS 订单号之计算
FROM (SELEC ...

在mysql中提示[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 B.订单号之计算
FROM
(SELECT A.三组合, Count(A.订单号) AS 订' at line 7    可以加QQ吗？

buguiize

buguiize 发表于 2017-3-30 13:58
在mysql中提示[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds  ...

我查了一下资料，MYSQL不支持TOP语句，能否修改成MYSQL支持的语句？

buguiize

buguiize 发表于 2017-3-30 14:23
我查了一下资料，MYSQL不支持TOP语句，能否修改成MYSQL支持的语句？

musql 中改了 TOP语句，出现下述错误
[Err] 1060 - Duplicate column name '商品'