mysql - 将列中的项目计为行 MYSQL

Question

尊敬的开发人员和程序员，

我有一个包含 SHOP_ID、PROD_ID、ipadres 和更多表格的表格，但这些都是关于...

看起来像这样:

id ip number        timestamp   shop_id prod_id
--------------------------------------------------
42  81.69.205.25    1319488326  2       3   
43  81.205.141.48   1319492649  2       3   
44  193.58.10.10    1319520579  14      17  
45  84.28.22.226    1319529529  11      19  
46  88.15.81.188    1319543745  2       1   
47  178.17.241.191  1319563031  14      7   
48  87.28.107.171   1319563038  2       6   
49  80.156.47.144   1319572818  14      7   
50  82.76.241.175   1319577506  11      1   
51  82.76.241.175   1319577584  13      1   
52  82.76.241.175   1319577785  14      1   
53  82.76.241.175   1319577860  4       1   
54  62.94.133.153   1319579221  14      1   
55  62.94.133.153   1319579281  2       3   
56  77.70.175.221   1319617238  11      1   
57  77.70.175.221   1319621845  13      1   
58  77.70.175.221   1319621848  2       1   
59  77.70.175.221   1319621850  11      1 
.... more
--------------------------------------------------------

有没有办法查看每个 prod_id 每个商店 id 存在多少个 ip 号码？

输出示例

        1        2        3        4        5
---------------------------------------------
1       18       5        51       8        4
2       58       5        45       3        4
3       7        6        31       9        2

其中水平是 prod_id，垂直是 shop_id

我已经这样做了:

select 

shop_id,
count(distinct(ipadres)) amount

from table

GROUP BY    shop_id

order by amount desc

但这只会给我所有 prod_id 的组合结果。

我希望 prod_id 在列中单独显示。

希望有解决办法!

亲切的问候

Answer 1

试试这个

select `prod_id`,shop_id,
    count(distinct(ipadres)) amount        
    from table        
    GROUP BY shop_id,prod_id        
    order by amount desc