GROUP BY几个元素

重量 最后一张表叫做产品，下面是以下栏目： 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语句应如下所示： 产品重量（千克） 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 PackageTypeI have another table called "Packages" with following columns: ID_Pack PackName WeightThe last table is called "Products" with following columns: ID_Product ProdNameHow 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_PackExample: Entries in table "RegisteredProducts":ID_Reg Name ProductType PackageType1 Sony 1 22 Asus 2 23 Asus 1 14 Albie 3 1 Entries in table "Packages":ID_Pack PackName Weight(kg)1 Internatio 202 Domesta 15 Entries in table "Products":ID_Product ProdName1 TV2 Stereo3 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 20TOTAL 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 RowResult will be something like this:product Internatio Domesta Sub Totaltv 20 15 35stereo 15 15lamp 20 20Total 70you 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_Productgroup by pr.ProdName, pa.PackName with RollupHope 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_shippedFrom RegisteredProducts INNER JOIN Products ON RegisteredProducts.ProductType = Products.ID_Product INNER JOIN Packages ON RegisteredProducts.PackageType = Packages.ID_PackGROUP BY Products.ProdName, Packages.PackName WITH ROLLUPOutputProduct Packages Weight_ShippedLampInternatio20LampSubTotal20StereoDomesta 15StereoSubTotal15TVDomesta 15TVInternatio20TVSubTotal35TotalSubTotal70 这篇关于GROUP BY几个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！