php - 从三个表和一个序列化列中选择数据的 SQL 查询

Question

我有三个表:

销售

sale_id    buyer    grand_total   payment_status
  42        1         1000        [{"vendor":"3","status":"paid"}] 
  43        1         2000        [{"vendor":"1","status":"paid"}]
  44        1         6000        [{"vendor":"3","status":"paid"}]
  45        2         3000        [{"vendor":"3","status":"paid"}]
  46        8         3000        [{"vendor":"3","status":"paid"}]
  47        8         5000        [{"vendor":"1","status":"paid"}]

用户

user_id                      
  1     
  2
  8

供应商

vendor_id  
  1      
  2         
  3                 
  46                

我想要一个查询，该查询将选择从所有特定供应商处购买商品的所有用户。

例如。我怎样才能让所有从 vendor_id 3 购买过的用户？如果用户进行了多次购买，我如何获得购买的总金额？

Answer 1

在 MySQL 5.7.9 及更高版本中:

SELECT buyer, SUM(grand_total) AS total 
FROM sale 
WHERE payment_status->"$[0].vendor" = 3

Docs