在 tsql 中加入表和 LEFT JOIN 作为新列 [英] Joining tables and LEFT JOIN as new Columns in tsql

问题描述

在此示例中，我有两个表，其中包含我的客户和办公用品商店拥有的产品的数据.如下所示:

In this sample I have two tables containing data for my Customer and Products that a office supply shop has. which is seen below:

上表主要用于为第三个表即订单表提供所需的数据

The Above tables primarily functions to supply the required data for the third table which is the Orders table

使用订单表，我可以从客户和产品表中获取我需要的报告的快速摘要.当我想查看每个客户对每件商品的购买数量时，问题就开始了.

Using the orders table, I can get a quick summary for reports that I need from customer and products table. The problem starts when I want to see the purchase count of each customer per item.

以这些表格为例.

使用上面的表格，我需要创建一个报告，显示每个客户对每件商品的购买数量.

Using the tables above, I need to create a report that shows the purchase count of each customer per item.

像这样.

我已经使用 CTE 按 Row_number 显示项目，但我现在拥有的代码太长了，我目前正在测试一些最有效的方法和最简洁的方式.如果有人能教我如何以更短的方式做到这一点，那就太好了.

I have used CTE to show items by Row_number but the code I have now is quite too long, I'm currently testing some way to do this the most efficient and most concise way. If someone can teach me how to do this the shorter way that would be great.

我不太确定我的问题的标题是否正确，如果看起来不正确，我会更改它.

I'm not quite sure if my question's title is correct, I'll change it if it seems incorrect.

推荐答案

所以第一步是从您的基本查询开始.这会为您提供最终报告信息，但格式错误.现在所需要做的就是旋转它.

So Step one would be to start off with your base query. This gives you the final report information but in the wrong format. All that is needed now is to pivot it.

Select cus_Name, prod_Name, SUM(ord_Qty)
from Orders o
inner join Customers c on c.cus_ID = o.cus_ID
inner join Products p on p.prod_ID = o.Prod_ID
GROUP BY cus_Name, prod_Name

更新

旋转这个更有趣，因为大概产品的数量不是固定的.因此，您需要编写一个动态数据透视表来确定基于产品的列！请注意，我尚未测试以下内容，但您应该明白了.有关 Pivot 的更多问题，请参阅 动态枢轴 上的大量帖子:

Pivoting this is more interesting as presumably the number of Products is not fixed. You therefore need to write a dynamic pivot to determine the columns based on the products ! Please note I haven't tested the below, but you should get the idea. Please refer to the plethora of posts on dynamic pivots for further questions on Pivots:

参见 SQLFiddle

DECLARE @colsForSelect VARCHAR(2000)
DECLARE @colsForPivot VARCHAR(2000)
DECLARE @query VARCHAR(4000)

SELECT  @colsForSelect = ISNULL(@colsForSelect + ',','') + ' ISNULL([' + prod_Name + '],0) AS [' + prod_Name + ']',
        @colsForPivot = ISNULL(@colsForPivot + ',','') + ' [' + prod_Name + ']'
FROM    Products

SET @query = N'SELECT cus_Name,'+ @colsForSelect +' 
FROM (    
     Select cus_Name, prod_Name, SUM(ord_Qty) as sum_ord_Qty
     from Orders o
     inner join Customers c on c.cus_ID = o.cus_ID
     inner join Products p on p.prod_ID = o.Prod_ID
     GROUP BY cus_Name, prod_Name
) p 
PIVOT (MAX([sum_ord_Qty]) FOR prod_Name IN ( '+ @colsForPivot +' )) 
AS pvt;'

EXECUTE(@query);

这篇关于在 tsql 中加入表和 LEFT JOIN 作为新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！