doumaque6551
2017-11-23 22:18
浏览 101
已采纳

MySQL pivot将行放入列中

I have three tables, products, customers, order

Product:

id | name |
 1 | milk |
 2 | bread|
 3 | Pea  |

Customer:

id | name  | category
1  | James | retailer
2  | Paul  | vendor
3  | Dave  | retailer

Order:

id  | product_id | customer_id | qty | price
 1  | 1          | 2           | 23  | 50 
 2  | 2          | 2           | 4   | 30
 3  | 3          | 2           | 6   | 10
 4  | 2          | 1           | 9   | 30
 5  | 3          | 1           | 2   | 10
 6  | 1          | 3           | 6   | 50
 7  | 3          | 3           | 7   | 10

When i do a query to show transactions by customers with category of vendor like

SELECT customer.name, product.name as pname, order.qty, order.price FROM customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "vendor"

i will get something like:

name |  pname | qty | price
Paul |  milk  | 23  | 50
Paul |  bread | 4   | 30
Paul |  pea   | 6   | 10

I want this instead:

name | milk  | bread  | pea  | total
Paul | 23    | 4      | 6    | 90

While that of retailers will look like this:

SELECT customer.name, product.name as pname, order.qty, order.price FROM 
customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "retailer"

I will get a table like this:

 name |  pname | qty | price
James |  bread | 9   | 30
James |  pea   | 2   | 10
 Dave |  milk  | 6   | 50
 Dave |  pea   | 7   | 10

But i want this instead:

name  | milk  | bread  | pea  | total
James | 0     |  9     | 2    | 40
Dave  | 6     |  0     | 7    | 60
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanri1985 2017-11-24 04:14
    已采纳

    Simply use conditional aggregation for pivoting columns. And be sure to use explicit joins instead of the deprecated implicit join as former has been the standard for 25 years in ANSI-92.

    SELECT c.name,  
           SUM(CASE WHEN p.name = 'milk' THEN o.qty ELSE 0 END) as milk,
           SUM(CASE WHEN p.name = 'bread' THEN o.qty ELSE 0 END) as bread,
           SUM(CASE WHEN p.name = 'pea' THEN o.qty ELSE 0 END) as pea,
           SUM(o.price) AS Total 
    FROM `customer` c
    INNER JOIN `order` o
      ON c.id = o.customer_id 
    INNER JOIN `product` p
      ON p.id = o.product_id 
    WHERE c.category = 'vendor'   -- same for retailer
    GROUP BY c.name
    
    评论
    解决 无用
    打赏 举报
  • 查看更多回答(1条)

相关推荐 更多相似问题