GROUP BY几个元素 [英] GROUP BY several elements

问题描述

我有一个名为RegisteredProducts的表，其中包含以下列：

ID_Reg

名称

ProductType

PackageType



我有另一个名为Packages的表，其中包含以下列：

ID_Pack

PackName
重量



最后一张表叫做产品，下面是以下栏目：

ID_Product

产品名称



如何编写一个查询，从已注册的产品中选择所有条目，按ProductType和PackageType进行分组，并总计精选产品？在表包中，包装类型之间有一个重量列。



表之间的链接：

ProductType - > ; ID_Product

PackageType - > ID_Pack





示例：



表RegisteredProducts中的条目：

 ID_Reg名称ProductType PackageType 
  1  Sony  1   2  
  2 华硕 2   2  
  3 华硕 1   1  
  4  Albie  3   1  

表包中的条目：

 ID_Pack PackName重量（kg）
  1  Internatio  20  
  2  Domesta  15  

表产品中的条目：

 ID_Product产品名称
  1  TV 
  2 立体声
  3 灯泡

因此，select语句应如下所示：< br $>


<前lang =SQL>产品重量（千克）
TV
- 国际 20
- Domesta 15
- 小计 35
立体声
- Domesta 15
- 小计 15
Lamp
- Internatio 20
- 小计 20

总计 70

所以基本上，只需要计算每种产品和包装类型的总重量，可以使用小计和总计。



（用例子更新帖子）

解决方案

在sql查询中使用pivot来获取所需的结果。请参考此链接 SQL - Pivot with Grand Total Column and Row



结果将是这样的：

产品Internatio Domesta Sub总计
 tv 20 15 35 
立体声15 15 
灯20 20 
总计70 

你也可以看一下此链接 [ ^ ]



lemme知道你是否遇到任何问题以及是否有效：）

仅供参考 - 你的表格没有遵循一些基本标准，例如使用相同列名称的内容相同（例如RegisteredProducts.PackageType和Packages.PackageType）。

小心。

，你想看到的结果集看起来更像是我的一个Excel支点。

类似的结果是可能，但不是逐字的。

 选择 pr.ProdName ，pa.PackName，SUM（pa.Weight） as  TotalWeight 
 来自 RegisteredProducts rp 
 内部 加入软件包pa   rp .PackageType = pa.ID_Pack 
  inner   join 产品pr  on  rp.ProductType = pr.ID_Product 
  group   by  pr。 ProdName，pa.PackName  with  Rollup 

希望这对你也有帮助。您可以使用 ROLLUP运算符和 GROUPING 来获得您提到的类似输出。这与您的输出不完全相同但是这可能会对你有所帮助。

  SELECT   CASE   WHEN （GROUPING（Products.ProdName）=  1 ） 那么 ' 总计' 
  ELSE  ISNULL（Products.ProdName，'  UNKNOWN'）
  END   AS 产品，
  CASE   WHEN （GROUPING（Packages.PackName）=  1 ）那么 '  SubTotal' 
  ELSE  ISNULL（Packages.PackName， '  UNKNOWN'）
  END   AS 包，
 SUM（Packages.weight） AS  Weight_shipped 
 来自已注册产品 INNER   JOIN 产品 ON  RegisteredProducts.ProductType = Products.ID_Product 
  INNER   JOIN 包 ON  RegisteredProducts.PackageType = Packages.ID_Pack 
  GROUP   BY 产品。 ProdName，Packages.PackName  WITH  ROLLUP 

输出

产品包装Weight_Shipped 
 Lamp Internatio  20  
 Lamp SubTotal  20  
 Stereo Domesta  15  
 Stereo SubTotal  15  
 TV Domesta  15  
 TV Internatio  20  
电视小计 35  
总小计 70  

I have a table called "RegisteredProducts" with following columns:
ID_Reg
Name
ProductType
PackageType

I have another table called "Packages" with following columns:
ID_Pack
PackName
Weight

The last table is called "Products" with following columns:
ID_Product
ProdName

How do I write a query that will select all entries from "Registered Products", group them by ProductType and PackageType, and also sum the total weight of the selected products? In table "Packages", there is a column "Weight" that differs between package types.

Links between tables:
ProductType -> ID_Product
PackageType -> ID_Pack


Example:

Entries in table "RegisteredProducts":

ID_Reg Name ProductType PackageType
1    Sony       1           2
2    Asus       2           2
3    Asus       1           1
4    Albie      3           1

Entries in table "Packages":

ID_Pack PackName     Weight(kg)
1       Internatio      20
2       Domesta         15

Entries in table "Products":

ID_Product ProdName
1             TV
2           Stereo
3            Lamp

So, the select statement shall be like this:

Product                 Weight shipped (kg)
  TV
   - Internatio                20
   - Domesta                   15
  - Subtotal                   35
  Stereo
   - Domesta                   15
  - Subtotal                   15
  Lamp
   - Internatio                20
  - Subtotal                   20

TOTAL                          70

So basically, just sum the total weight for each product and package type, with subtotals and totals of possible.

(Updated the post with an example)

解决方案

Use pivot in sql query to get the required result. Please refer this link SQL - Pivot with Grand Total Column and Row

Result will be something like this:

product Internatio Domesta  Sub Total
tv      20         15         35
stereo             15         15
lamp    20                    20
Total                         70

you can also look up this link[^]

lemme know if you get any problem and also if it works :)

FYI - Your tables are not following some basic standards, such as using the same column name for things that are meant to be the same (e.g. RegisteredProducts.PackageType and Packages.PackageType).
Be careful with that.
also, the result set you want to see looks more like an Excel pivot to me.
A similar result is possible, but not a verbatim one.

select pr.ProdName, pa.PackName, SUM(pa.Weight) as TotalWeight 
from  RegisteredProducts rp
      inner join Packages pa on rp.PackageType = pa.ID_Pack
      inner join Products pr on rp.ProductType = pr.ID_Product
group by pr.ProdName, pa.PackName with Rollup

Hope this will help you too. You can use ROLLUP operator and GROUPING to get the similar output as you mentioned.This is not exactly same as your output.But this might help you.

SELECT CASE WHEN (GROUPING(Products.ProdName) = 1) THEN 'Total'
            ELSE ISNULL(Products.ProdName, 'UNKNOWN')
       END AS Product,
       CASE WHEN (GROUPING(Packages.PackName) = 1) THEN 'SubTotal'
            ELSE ISNULL(Packages.PackName, 'UNKNOWN')
       END AS Packages,
       SUM(Packages.weight) AS Weight_shipped
From RegisteredProducts INNER JOIN Products ON RegisteredProducts.ProductType = Products.ID_Product
    INNER JOIN Packages ON RegisteredProducts.PackageType = Packages.ID_Pack
GROUP BY Products.ProdName, Packages.PackName WITH ROLLUP

Output

Product Packages        Weight_Shipped
Lamp	Internatio	20
Lamp	SubTotal	20
Stereo	Domesta	        15
Stereo	SubTotal	15
TV	Domesta 	15
TV	Internatio	20
TV	SubTotal	35
Total	SubTotal	70

这篇关于GROUP BY几个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！