orderID productID Number
aaaa aaa 2
aaaa bb 1
bbbb aaa 1
bbbb cccc 1
bbbb ddd 1
bbbb eeee 1
cccc aaa 1
dddd aaa 1
dddd ddd 1求 语句 统计一下 aaa 商品一起销售其他商品 按次数排序 ,商品一起销售其他商品 总销售个数排序 ,
aaaa aaa 2
aaaa bb 1
bbbb aaa 1
bbbb cccc 1
bbbb ddd 1
bbbb eeee 1
cccc aaa 1
dddd aaa 1
dddd ddd 1求 语句 统计一下 aaa 商品一起销售其他商品 按次数排序 ,商品一起销售其他商品 总销售个数排序 ,
productID 关联次数
ddd 2
bb 1
cccc 1
eeee 1
bbbb cccc 1
bbbb ddd 1
bbbb eeee 1dddd aaa 1
dddd ddd 1出现两次 即销售了 aaa 又销售了 ddd
SELECT * FROM aa1 WHERE productID ='aaa') b ON a.`orderID`=b.`orderID`
WHERE a.`productID`<>'aaa'
GROUP BY a.`productID` ORDER BY 2 DESC
+---------+-----------+--------+
| orderID | productID | Number |
+---------+-----------+--------+
| aaaa | aaa | 2 |
| aaaa | bb | 1 |
| bbbb | aaa | 1 |
| bbbb | cccc | 1 |
| bbbb | ddd | 1 |
| bbbb | eeee | 1 |
| cccc | aaa | 1 |
| dddd | aaa | 1 |
| dddd | ddd | 1 |
+---------+-----------+--------+
9 rows in set (0.00 sec)mysql> SELECT a.`productID`,COUNT(*) as 关联次数 FROM aa1 a INNER JOIN (
-> SELECT * FROM aa1 WHERE productID ='aaa') b ON a.`orderID`=b.`orderID`
-> WHERE a.`productID`<>'aaa'
-> GROUP BY a.`productID` ORDER BY 2 DESC;
+-----------+----------+
| productID | 关联次数 |
+-----------+----------+
| ddd | 2 |
| bb | 1 |
| cccc | 1 |
| eeee | 1 |
+-----------+----------+
4 rows in set (0.02 sec)mysql>
b where a.orderID=b.orderID and a.productID<>'aaa' group by a.productID order by 2 desc;
+-----------+----------+
| productID | count(*) |
+-----------+----------+
| ddd | 2 |
| bb | 1 |
| cccc | 1 |
| eeee | 1 |
+-----------+----------+
4 rows in set (0.00 sec)