TSQL - 寻找代码澄清
这是我使用的代码，结果贴在下面:
This is the code I used and the result is posted below:
SELECT *
FROM
(SELECT
P.ProductID, PC.Name, ISNULL (P.Color, 'uncolored') AS color
FROM
SalesLT.ProductCategory AS PC
JOIN
SalesLT.Product AS P ON PC.ProductCategoryID = P.ProductCategoryID
) AS PPC
PIVOT
(COUNT (ProductID) FOR Color IN ([Red],[Blue],[Silver],[Black],[Yellow],[Grey],[Multi],[Uncolored])) AS pvt
ORDER BY
Name;
我没有要求查询提供我的名字，有人能解释一下这个名字"是如何出现在结果中的吗?如果我希望 ProductID 出现而不是名称，我该如何修改代码?
I didn't request the query to provide me the name, could someone explain me how this 'name' thing popped up in the result? How can I modify the code if I want ProductID to appear instead of name?
非常感谢任何帮助或反馈.
Any help or feedback would be greatly appreciated.
推荐答案
ProductID 不会显示，因为您在透视表时将其用作聚合列.只会显示每种颜色的产品 ID 计数.为了查看 ProductID 删除查询的数据透视部分.Name 显示在您的最终结果中，因为它存在于您的内部查询中.
ProductID won't show because you have used it as an aggregation column while pivoting the table. Only the counts of product Id for each colour will be shown. For seeing ProductID remove the pivot part of query. Name shows up in your final result because it is there in your inner query.
旋转表格将行更改为列.在您的情况下，颜色 Red、Blue、Silver、Black、Yellow、Grey、Multi、Uncolored(如在 pivot 子句中给出)的行值更改为列.在这些列的每一列下，显示了该颜色的表中的 ProductID 计数以及名称"列中的名称.因此在查询中没有任何地方显示单个 productId.内部查询的 ProductID 列仅用于计算计数，不显示在最终结果中.
Pivoting the table changes rows to columns. In your case the row values for colours Red,Blue,Silver,Black,Yellow,Grey,Multi,Uncolored ( as given inside pivot clause) are changed to columns. Under each of these columns, the counts of ProductIDs present in the table for that colour and the name in 'Name' column are shown. So nowhere in the query individual productIds are shown. The ProductID column the inner query is only used for calculating counts and not shown in final result.
相关文章