我有下面的db表,我希望能够统计每个销售人员的特定产品的销售情况.
|------------|------------|------------|
|id |user_id |product_id |
|------------|------------|------------|
|1 |1 |2 |
|2 |1 |4 |
|3 |1 |2 |
|4 |2 |1 |
|------------|------------|------------|
我希望能够创建如下结果集:;
|------------|-------------|------------|------------|------------|
|user_id |prod_1_count |prod_2_count|prod_3_count|prod_4_count|
|------------|-------------|------------|------------|------------|
|1 |0 |2 |0 |1 |
|2 |1 |0 |0 |0 |
|------------|-------------|------------|------------|------------|
我正在用这些数据创建图表,再一次(像今天早些时候一样)我无法计算列总数.我试过了;
SELECT user_id,
(SELECT count(product_id) FROM sales WHERE product_id = 1) AS prod_1_count,
(SELECT count(product_id) FROM sales WHERE product_id = 2) AS prod_2_count,
(SELECT count(product_id) FROM sales WHERE product_id = 3) AS prod_3_count,
(SELECT count(product_id) FROM sales WHERE product_id = 4) AS prod_4_count
FROM sales GROUP BY user_id;
我可以理解为什么这不起作用,因为对于每个括号中的SELECT,用户id与主SELECT语句中的外部用户id不匹配.