SQL服务器：创建从2个表

问题描述：

我有2个表，一个具有料斗并在它们的成分（记录一次，每天，或当成分改变）SQL服务器：创建从2个表

实施例的图：

Hoppers | Ingredients | Timestamp 
--------+---------------+---------------------- 
Hop_1 | Ing_A  | 8/22/2016 06:00:00 
Hop_2 | Ing_B  | 8/22/2016 06:00:00

等...

而且我有了从各料斗中使用总计记录每小时第二台

例子：

Name    | Value | Timestamp 
-------------------+----------+--------------------- 
Hop_1 Daily Total | 100  | 8/22/2016 11:00:00 
Hop_1 Run Total | 30  | 8/22/2016 11:00:00

等等

我想创建一个视图，显示每个料斗生产量和名字说。

例子：

Hop_1 Ingredient | Hop_1 Daily | Hop_1 Run | Timestamp 
-----------------+-------------+-----------+------------------- 
Ing_A   |  100 |  30 | 8/22/2016 11:00:00

很抱歉，如果它看起来并不好，我是新的格式化

所以没有列加入2表，除了切割名称列的第一部分？如果有超过1种成分，您将如何确定哪种成分与哪种成分相符？如果你想要一个具有专门针对漏斗的列名称的枢轴，当多于一个漏斗存在时，你会做什么？添加其他列？ – Matt

是的，决赛桌将会是所有的跳球，以及所有的日常总数，等等。我只是不想把它全部输出......我希望我可以按照每张桌子上的时间标记将它们分组......不过，时间戳不会等于eachother，我的想法是加载每日总数的值，并运行总计，然后使用该时间戳，然后查找另一个表中的最新时间戳。我只是不知道如何完成这一点。我试图这样做的原因是因为我只是试图在先前创建的数据库中组织信息 – Neal

对不起，尼尔您的评论没有解决我的问题，也许我没有很好地指出它们。 1）你如何在表格之间进行关联？那么如果有超过1种成分，你怎么知道Ing_A会进入价值？ 2）料斗将如何在决赛桌上呈现，每个料斗的附加栏位？你没有太在意网站太多的关注可能是因为你的问题没有回答一些未知的问题，也因为你没有表现出试图得到你想要的东西，即使这种尝试是失败的 – Matt

答

你可以使用数据透视表为您的需要。这里有一个链接：https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

答

对于记录我真的不认为这个结构是最好的主意，因为它需要动态的sql来将它关闭几个循环和大量的左自联接。但在这里你去：

IF OBJECT_ID('tempdb..#Ingredients') IS NOT NULL 
    BEGIN 
     DROP TABLE #Ingredients 
    END 

IF OBJECT_ID('tempdb..#Totals') IS NOT NULL 
    BEGIN 
     DROP TABLE #Totals 
    END 

CREATE TABLE #Ingredients (Hoppers VARCHAR(25), Ingredeients VARCHAR(25), [Timestamp] DATETIME) 
CREATE TABLE #Totals (Name VARCHAR(50), Value INT, [Timestamp] DATETIME) 

INSERT INTO #Ingredients (Hoppers, Ingredeients, [Timestamp]) 
VALUES ('Hop_1','Ing_A','8/22/2016 06:00:00'),('Hop_2','Ing_B','8/22/2016 06:00:00'),('Hop_3','Ing_C','8/22/2016 06:00:00') 
INSERT INTO #Totals (Name, Value, [Timestamp]) 
VALUES ('Hop_1 Daily Total',100,'8/22/2016 11:00:00'),('Hop_1 Run Total',30,'8/22/2016 11:00:00'),('Hop_1 Run Total',60,'8/22/2016 09:00:00') 
,('Hop_2 Daily Total',500,'8/22/2016 11:00:00'),('Hop_2 Run Total',10,'8/22/2016 11:00:00'),('Hop_2 Run Total',5,'8/22/2016 10:00:00') 
,('Hop_3 Daily Total',400,'8/22/2016 11:00:00'),('Hop_3 Run Total',85,'8/22/2016 11:00:00'),('Hop_3 Run Total',65,'8/22/2016 10:00:00') 


DECLARE @HopperCount INT 
SELECT @HopperCount = COUNT(DISTINCT i.Hoppers) 
FROM 
    #Ingredients i 
    INNER JOIN #Totals t 
    ON t.Name LIKE i.Hoppers + '%' 


DECLARE @I INT = 1 
DECLARE @Sql NVARCHAR(MAX) 

SET @Sql = ' 
;WITH cte AS (
    SELECT 
     i.Hoppers 
     ,i.Ingredeients 
     ,Value 
     ,t.[Timestamp] 
     ,CASE WHEN t.Name LIKE ''%Daily%'' THEN 1 ELSE 0 END as IsDaily 
     ,ROW_NUMBER() OVER (PARTITION BY i.Hoppers, t.name ORDER BY t.[Timestamp] DESC) as RunRowNum 
     ,DENSE_RANK() OVER (PARTITION BY 1 ORDER BY i.Hoppers) as HooperNumber 
    FROM 
     #Ingredients i 
     INNER JOIN #Totals t 
     ON t.Name LIKE i.Hoppers + ''%'' 
     AND i.[Timestamp] <= t.[Timestamp] 
) 

SELECT 
    c.Ingredeients AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Ingredient] 
    ,SUM(CASE WHEN c.IsDaily = 1 THEN c.Value END) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Daily] 
    ,SUM(CASE WHEN c.IsDaily = 0 THEN c.Value END) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Run] 
    ,MAX(c.[Timestamp]) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Timestamp] ' 


SET @I = 2 
WHILE @I <= ISNULL(@HopperCount,0) 
BEGIN 
    SET @Sql = @Sql + ' 
    ,c'+ CAST(@I AS VARCHAR(10)) + '.Ingredeients AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Ingredient] 
    ,SUM(CASE WHEN c'+ CAST(@I AS VARCHAR(10)) + '.IsDaily = 1 THEN c' + CAST(@I AS VARCHAR(10)) + '.Value END) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Daily] 
    ,SUM(CASE WHEN c' + CAST(@I AS VARCHAR(10)) + '.IsDaily = 0 THEN c' + CAST(@I AS VARCHAR(10))+ '.Value END) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Run] 
    ,MAX(c' + CAST(@I AS VARCHAR(10)) + '.[Timestamp]) AS [Hop_' + CAST(@I AS VARCHAR(10)) + ' Timestamp] ' 

    SET @I = @I + 1 
END 


SET @Sql = @Sql + ' 
FROM 
    cte c ' 


SET @I = 2 
WHILE @I <= ISNULL(@HopperCount,0) 
BEGIN 
    SET @Sql = @Sql + ' 
    LEFT JOIN cte c' + CAST(@I AS VARCHAR(10)) + ' 
    ON c.HooperNumber + ' + CAST(@I - 1 AS VARCHAR(10)) + ' = c' + CAST(@I AS VARCHAR(10)) + '.HooperNumber 
    AND c' + CAST(@I AS VARCHAR(10))+ '.RunRowNum = 1 
    AND c.IsDaily = c' + CAST(@I AS VARCHAR(10)) + '.IsDaily ' 

    SET @I = @I + 1 
END 

SET @Sql = @Sql + ' 
WHERE 
    c.HooperNumber = 1 
    AND c.RunRowNum = 1 
GROUP BY 
    c.Ingredeients 
' 

SET @I = 2 
WHILE @I <= ISNULL(@HopperCount,0) 
BEGIN 
    SET @Sql = @Sql + ',c' + CAST(@I AS VARCHAR(10)) + '.Ingredeients 
    ' 

    SET @I = @I + 1 
END 

EXECUTE (@sql)

SQL服务器：创建从2个表

相关推荐