SQL 查询 : how to add "sum" along with the corresponding data

Question

我有一个多对多关系，我想选择特定字段的总和以及所有必要的数据。我将通过代码解释我的案例。

首先是相关的表结构:

CREATE TABLE `products` (
    `id`    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  text,
    `description`   text,
    `price` float,
    `picture`   varchar(255)
);

CREATE TABLE `orders_products` (    
        `id`    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
        `quantity`  integer,
        `status`    boolean,
        `product_id`    integer,
        `order_id`  integer,
        FOREIGN KEY(`product_id`) REFERENCES "products" ( "id" ),
        FOREIGN KEY(`order_id`) REFERENCES "orders" ( "id" )
    );

CREATE TABLE `orders` (
    `id`    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    `created_at`    datetime,
    `updated_at`    datetime,
    `user_id`   integer,
    FOREIGN KEY(`user_id`) REFERENCES "users" ( "id" )
);

基本上我现在所做的是:

select 
"products".name,
"products".price,
"orders_products"."order_id",
"orders_products"."product_id"
from "products" 
inner join "orders_products" on "orders_products"."product_id" = "products"."id" 
where "orders_products"."order_id" in (1, 2)

这会产生以下结果:

name            price   order_id    product_id
"coca cola"     "2.35"      "1"     "1"
"snickers"      "1.25"      "1"     "2"
"snickers"      "1.25"      "2"     "2"
"popcorn"       "1.19"      "2"     "3"

但是，如果我想“求和”价格，并根据 order_id 进行排序:

select 
sum ("products".price) as _sum,
"products".name,
"products".price,
"orders_products"."order_id",
"orders_products"."product_id"
from "products" 
inner join "orders_products" on "orders_products"."product_id" = "products"."id" 
where "orders_products"."order_id" in (1, 2)
group by "orders_products"."order_id"

我得到以下结果:

_sum    name        price   order_id    product_id
"3.6"   "snickers"  "1.25"      "1"     "2"
"2.44"  "popcorn"   "1.19"      "2"     "3"

我真正想要的是:

name            price   order_id    product_id    _sum
"coca cola"     "2.35"      "1"     "1"            "3.6"
"snickers"      "1.25"      "1"     "2"            "3.6"
"snickers"      "1.25"      "2"     "2"            "2.44"
"popcorn"       "1.19"      "2"     "3"            "2.44"

这可能吗？

Answer 1

您可以做的是将每个 order id 的 sums 投影为派生表，然后将您的原始查询连接回派生表，这将允许您将 sum 显示为每个订单的列:

select 
  p.name,
  p.price,
  op.order_id,
  op.product_id,
  Totals.theSum
from products p
  inner join orders_products op on op.product_id = p.id
  inner join
  (select sum(p.price) as theSum,
    op.order_id
   from products p 
     inner join orders_products op on op.product_id = p.id 
   where op.order_id in (1, 2)
   group by op.order_id) AS Totals
on Totals.order_id = op.order_id;

内部派生表已经完成过滤，不再重复。

SqlFiddle here