如何通过MySQL的

  • Z时代
  • 2024-01-10
  • 分类：问答

与内连接来使用多组我有两个表一个是订单和第二是order_product中，我必须找到对应的购买者ID我们的订单和产品总数的总数。我已经找到正确的产品总数，但我无法找到订单总数。我的表格结构是下面的。如何通过MySQL的

订单：

........................ 
order_id or_buyer_id 
....................... 
    1   21

order_product

................................. 
op_id op_order_id op_buyer_id 
................................. 
    1  1   21 
    2  1   21

我想这个输出为了这个，我已申请下面的查询，但它返回产品的 权值和错误值命令。

.................................. 
or_buyer_id orders product 
................................. 
    21   1  2

但我从下面的查询得到这个

............................... 
or_buyer_id orders product 
............................... 
    21   2  2

我的查询：

SELECT o.`or_buyer_id`, count(o.`or_buyer_id`) as orders, count(op.op_buyer_id) as product FROM `orders` as o inner JOIN order_product as op on op.op_order_id=o.order_id and o.or_buyer_id = op.op_buyer_id group by o.`or_buyer_id`

回答：

你只需要通过它来选择or_buyer_id和组，并添加额外的连接条件相匹配or_buyer_id两张桌子之间：

SELECT o.`or_buyer_id`, 
    count(DISTINCT o.`or_buyer_id`) as orders, 
    count(op.op_id) as product 
FROM `orders` as o 
inner JOIN order_product as op on op.op_order_id=o.order_id 
           and o.or_buyer_id = op.op_buyer_id 
group by o.`or_buyer_id`;

demo

| or_buyer_id | orders | product | 
|-------------|--------|---------| 
|   21 |  1 |  2 |

回答：

请查看以下链接介绍了如何完成这件事：

的MySQL INNER与论坛加入BY子句

http://www.mysqltutorial.org/mysql-inner-join.aspx

SELECT 
    T1.orderNumber, 
    status, 
    SUM(quantityOrdered * priceEach) total 
FROM 
    orders AS T1 
     INNER JOIN 
    orderdetails AS T2 ON T1.orderNumber = T2.orderNumber 
GROUP BY orderNumber;

所以答案您的查询将如下所示：

SELECT 
    ord.or_buyer_id, 
    count(DISTINCT ord.or_buyer_id) as orders, 
    count(op.op_id) as product 
FROM 
    orders as ord 
inner JOIN 
    order_product as op 
ON 
    op.op_order_id=ord.order_id 
and 
    ord.or_buyer_id = op.op_buyer_id 
group by 
    ord.or_buyer_id

以上是 如何通过MySQL的 的全部内容， 来源链接： utcz.com/qa/265370.html

回到顶部