具有多个表和SUM的SQL SELECT

问题描述：

我已经在这个论坛上使用了一段时间，以找到一些与SQL有关的问题的答案.现在是时候问一个我已经尝试解决了一段时间的问题了.

I have used this forum for a while now to find answers to some SQL related questions. Now it's time to ask a question I have tried to figure out for some time now.

我有两个表(产品和来源).

I have two tables (product and source).

我想创建一个SQL SELECT来从源中检索记录列表，并从产品中检索一条附加记录(价格的总和).我想看到的表应如下所示:

I would like to create a SQL SELECT to retrieve a list of records from source and one additional record from product (a SUM of price). The table I would like to see should look something like this:

source.location和product.location已链接.

source.location and product.location are linked.

此代码有效，并给出我想要的结果:

This code works and give the result I want:

SELECT s.source_id
    , s.location
    , s.source_name
    , s.source_description
    , s.source_date
    , s.source_price
    , p2.Total
    , sum(p1.price) as SumProductSold
FROM source s
JOIN product p1
    on s.location = p1.location
JOIN
(
    SELECT location, sum(price) as Total
    FROM product
    GROUP BY location
) p2
    on s.location = p2.location
WHERE p1.quantity < 1
GROUP BY s.source_id, s.location, s.source_name
    , s.source_description, s.source_date, s.source_price, p2.Total

谢谢bluefeet！

Thank you bluefeet!!

答

没有很多细节，您可以执行以下操作:

without a whole lot of details your can do something like this:

SELECT s.source_id
    , s.location
    , s.source_name
    , s.source_description
    , s.source_date
    , s.price
    , sum(p.price) as SumProductPrice
    , sum(p.location) as SumProductLocation
FROM source S
JOIN product p
    on S.location = p.location
WHERE p.quantity < 1
GROUP BY s.source_id, s.location, s.source_name
    , s.source_description, s.source_date, s.price

如果您发布更多详细信息，则可以对查询进行微调.

if you post more details, then the query can be fine-tuned.

您可以第二次加入产品表以获取该位置的总数:

you can join on the products table a second time to get the total for the location:

SELECT s.source_id
    , s.location
    , s.source_name
    , s.source_description
    , s.source_date
    , s.price
    , sum(p1.price) as SumProductPrice
    , p2.Total
FROM source S
JOIN product p1
    on S.location = p1.location
JOIN
(
    SELECT location, sum(price) as Total
    FROM product
    WHERE quantity < 1
    GROUP BY location
) p2
    on S.location = p2.location
WHERE p1.quantity < 1
GROUP BY s.source_id, s.location, s.source_name
    , s.source_description, s.source_date, s.price, p2.Total

具有多个表和SUM的SQL SELECT

相关推荐