请解释以下代码： [英] Please explain following code:

问题描述

我无法在查询中获得选择函数和grouping_id的含义。

这是sql server中的Adventures数据库。



具体来说，

I am not able to get the meaning of choose function and grouping_id in the query.
This is Adventures database in sql server.

Specifically,

CHOOSE (1+ GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
        a.City + ' Subtotal', a.StateProvince + ' Subtotal',
        a.CountryRegion + ' Subtotal', 'Total'

我的问题是关于choose子句。根据语法，它是

My question is regarding the choose clause. As per the syntax , it is

CHOOSE ( index, val_1 val_2 [, val_n ] )

此外，grouping_id应包含所有列的列表GROUP BY。



如果你甚至可以参考一些关于分组ID的文章。



我我的谷歌搜索。我的理解似乎不够。



感谢您的帮助



什么我试过了：

Furthermore, "grouping_id" should contain all list of columns which are in "GROUP BY".

If you could refer even to some article on grouping id.

I did my google search. What I understood seems insufficient.

Thanks for help

What I have tried:

SELECT a.CountryRegion, a.StateProvince, a.City,
CHOOSE (1+ GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
        a.City + ' Subtotal', a.StateProvince + ' Subtotal',
        a.CountryRegion + ' Subtotal', 'Total') AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
ORDER BY a.CountryRegion, a.StateProvince, a.City;

推荐答案

Quote：

grouping_id应包含所有内容清单GROUP BY中的列

"grouping_id" should contain all list of columns which are in "GROUP BY"

再次阅读文档：

Read the documentation again:

GROUPING_ID（Transact-SQL）| Microsoft Docs [ ^ ]：

每个GROUPING_ID参数必须是 GROUP BY列表的元素。

Each GROUPING_ID argument must be an element of the GROUP BY list.

您正在分组通过 a.CountryRegion ， a.StateProvince 和 a.City ，因此每个列名都是 GROUPING_ID 聚合函数的有效参数。

You are grouping by a.CountryRegion, a.StateProvince, and a.City, so each of those column names is a valid argument for the GROUPING_ID aggregate function.

Quote：

我的问题是关于choose子句。根据语法，它是

My question is regarding the choose clause. As per the syntax , it is

CHOOSE ( index, val_1 val_2 [, val_n ] )

这正是你所拥有的：

And that's exactly what you have:

CHOOSE (
    /* index = */  1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
    /* val_1 = */  a.City + ' Subtotal',
    /* val_2 = */  a.StateProvince + ' Subtotal',
    /* val_3 = */  a.CountryRegion + ' Subtotal', 
    /* val_4 = */  'Total'
)

要正确理解查询，您还需要了解 ROLLUP 子句：

SQL SERVER - 汇总条款简介 - 使用Pinal Dave的SQL权限 [ ^ ]

没有。

你好吗？我知道有多少工作是逐行解释代码吗？

每一行都需要一段解释！例如：

No.
Do you have any idea how much work explaining code line by line is?
Every single line needs a paragraph of explanation! For example:

int next = r.Next();

创建一个名为next的新变量，它可以包含一个整数值。从先前声明的Random实例r，调用Next方法获取一个新的随机数，并将其分配给next变量。



可以你想象我们需要多长时间才能解释一个像你的例子一样的非常短的代码片段，一行一行？



不会发生这种情况。如果您有特定问题，请询问有关它的问题。但首先考虑一下 - 您是否希望坐下45分钟并且没有充分的理由输入逐行描述？

Create a new variable called "next" which can hold a integer value. From the previously declared Random instance "r", call the "Next" method to get a new random number, and assign it to the "next" variable.

Can you imagine how long it would take us to explain even a very short code fragment like your example, line by line?

No. It is not going to happen. If you have a specific problem, then ask a question about it. But think first - would you want to sit down for 45 minutes and type up a line-by-line description for no good reason?

这篇关于请解释以下代码：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！