問題描述

這是我使用的代碼，結果貼在下面:

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.

這篇關于TSQL - 尋找代碼澄清的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！