获取详细信息总和表

Question

>我需要检索主记录及其详细信息的总和。以下是两个表的定义：

主表(主表(

ID
Name

示例记录：

10 Product

详细信息表(详细信息(

master_id
type (values can only be red or blue)
quantity

示例记录：

10 red 2
10 red 5
10 red 6
10 blue 7
10 blue 9

我需要这样的结果：

10 Product 13 (sum for red) 16 (sum for blue)

数据库是SQL Server 2017。如何在不使用存储过程的情况下编写单个查询？

---更新---

根据VenkataramanR的输入，以下是解决方案：

SELECT m.id, m.name ,
SUM(CASE when type='red' then quantity end) as redsum,
SUM(CASE when type='blue' then quantity end) as bluesum
from mydetails as t
inner join mymaster as m
on m.id = t.master_id
GROUP BY m.id, m.name

Answer 1

您可以将 CONCAT 函数与分组依据一起使用，以单个语句的形式获取结果。

DECLARE @table table(master_id int, typev varchar(10), quantity int)
DECLARE @master table(id int, name varchar(50))
insert into @master values(10, 'Product10')
insert into @table values
(10,'red',  2),
(10,'red',  5),
(10,'red',  6),
(10,'blue', 7),
(10,'blue', 9);
SELECT m.name ,
SUM(CASE when typev='red' then quantity end) as redsum,
SUM(CASE when typev='blue' then quantity end) as bluesum
from @table as t
inner join @master as m
on m.id = t.master_id
GROUP BY m.name

+-----------+--------+---------+
|   name    | redsum | bluesum |
+-----------+--------+---------+
| Product10 |     13 |      16 |
+-----------+--------+---------+

Answer 2

如果我正确理解了，那么您正在寻找枢轴，在这种情况下，您可以使用case表达式，如下所示

select
master_id,
sum(case when type = 'red' then quantity end) as sum_of_red,
sum(case when type = 'blue' then quantity end) as sum_of_blue
from yourTable
group by
master_id

如果您只想按类型划分总数量，请使用以下内容

select
master_id,
type,
sum(quantity) as total_quantity
from yourTable
group by
master_id,
type

Answer 3

只需使用一个组

Select master_id, type, sum(quantity)
From details group by master_id, type

并使用透视在一行中显示每个产品

With data_cte as 
(Select master_id, type, sum(quantity)
From details group by master_id, type)
Select * from data_cte pivot (
For type in ('red', 'black'))