T-SQL学习中--PIVOT和UNPIVOT

最新推荐文章于 2024-06-14 14:50:17 发布

weixin_33804582

最新推荐文章于 2024-06-14 14:50:17 发布

阅读量167

点赞数

CC 4.0 BY-SA版权

原文链接：https://segmentfault.com/a/1190000002741742

本文深入探讨了SQL中的PIVOT操作符如何定义分组元素，以及其在查询表中的应用。同时解释了转秩操作符在结果集中如何处理目标值的行数据类型，并提供了常见错误及解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

几道习题
1. How does the PIVOT operator determine what the grouping element is?
PIVOT操作符如何定义分组元素是什么？
A. It's the element specified as input to the GROUPING function.
它是通过GRPUPING函数输入指定的元素。
B. It's determined by elimination--the element(s) from the queried table that were not specified as the spreading or aggregation elements.
它是通过排除来定义--来自查询表而且没有列入扩展或者聚合元素的元素
C. It's the element specified in the GROUP BY clause.
它是通过GROUP BY子句指定的元素
D. It's the primary key
它是主键
答案：
A. Incorrect: The GROUPING function is related to grouping sets--not to prvoting.
错误：GROUPING 函数是相对于分组集合的，不是相对于转秩。
B. Correct: The PIVOT operator determines the grouping element by elimination-- it's what's left besides the spreading and aggregation elements.
正确：PIVOT运算符通过排除来定义分组元素--它就是去掉扩展以及聚合元素之外的元素。
C. Incorrect: When using the PIVOT operator, the grouping for pivoting happens as part of the PIVOT operator-before the GROUP BY clause gets evaluated.
错误：当使用了PIVOT操作符，对转秩进行编组作为PIVOT操作符的一部分，发生在GROUP BY子句开始求值以前。
D. Incorrect: The PIVOT operator doesn't look at constraint definitions to determine the grouping element.
错误：转秩运算符不根据约束定义来确定分组元素的。
2.Which of the following are not allowed in the PIVOT operator's specification? (Choose all that apply.)
A. Specifying a computation as input to the aggregate function.
指定一个计算结果作为集合函数的输入
B. Specifying a computation as the spreading element.
指定一个计算结果作为扩展元素
C. Specifying a subquery in the IN Clause.
在IN分句中指定一个子查询
D. Specifying multiple aggregate functions.
指定多重集合函数
答案
A. Correct: You cannot specify a computation as input to the aggregate function.
B. Correct: You cannot specify a computation as the spreading element, rather just a name of a column from the input table.
C. Correct: You canot specify a subquery in the IN clause, rather just a static list.
D. Correct: You cannot specify multiple agreegate functions, rathor just one
3.What is the data type of the target values columns in the result of an UNPIVOT operator?
在反转秩操作符的结果中，目标植的行的数据类型是什么？
A. INT
B. NVARCHAR(128)
C. SQL_VARINT
D. The data type of the source columns that you unpivot
你反转轶的源行的数据类型