问在sql server中，数据总是显示在一个炉子里。
EN

Stack Overflow用户

提问于 2019-02-15 20:09:09

回答 1查看 52关注 0票数 0

我正在处理一个sql查询，其中我将给出一个日期，从该日期开始，将确定月份和年份。然后，将从任何炉子的Lab_Analysis和Lab_CSAnalysis表以及产品ID中选择记录。

    Declare @Furnace varchar(50)='FUR-A'
    Declare @Product bigint=1
    Declare @sd date
    Declare @ed date
    Declare @Date date='02-02-2019'
    SET @sd=(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,@Date),0)))
    SET @ed=(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0)))

    ;WITH dates AS (
        SELECT @sd as theDate
        UNION ALL
        SELECT DATEADD(day, 1, theDate)
        FROM dates 
        WHERE DATEADD(day, 1, theDate) <= @ed
    )

    SELECT     DATEPART(dd, D.theDate) AS 'Day', ISNULL(LS.QtyMT, 0) AS QtyMt, ISNULL(SUM(L.Mn), 0) AS Mn, ISNULL(SUM(L.Si), 0) AS Si, ISNULL(SUM(L.P), 0) AS P, 
                  ISNULL(LS.Carbon, 0) AS C, ISNULL(LS.Sulphur, 0) AS S, ISNULL(SUM(L.MnO), 0) AS MnO, ISNULL(SUM(L.CaO), 0) AS CaO, ISNULL(SUM(L.AI2O3), 0) 
                  AS AI2O3, ISNULL(SUM(L.MgO), 0) AS MgO, ISNULL(SUM(L.Fe2O3), 0) AS Fe2O3, ISNULL(SUM(L.SiO2), 0) AS SiO2, ISNULL(SUM(L.Basicity), 0) AS Basicity
FROM         Lab_Product AS LP INNER JOIN
                  Lab_Analysis AS L ON LP.ID = L.Product RIGHT OUTER JOIN
                  dates AS D ON L.Date = theDate LEFT OUTER JOIN
                  Lab_CSAnalysis AS LS ON LS.Date = L.Date AND (LP.ID = 1 AND L.Furnace = @Furnace AND LS.Furnace=@Furnace)
GROUP BY D.theDate, LS.QtyMT, LS.Carbon, LS.Sulphur

这里我想查看Furnace='A‘和ProductID=1的记录，那么上面的查询就是正确的输出。除了炉子'A‘和产品ID=1没有记录。但是当我想查看炉子'B’的记录时，它仍然显示炉子'A‘的记录。如何解决这个问题？

sql

sql-server

tsql

回答 1

Stack Overflow用户

发布于 2019-02-15 20:14:33

因为您在左连接上使用了过滤器。我对你的查询做了一些修改

Declare @Furnace varchar(50)='FUR-A'
    Declare @Product bigint=1
    Declare @sd date
    Declare @ed date
    Declare @Date date='02-02-2019'
    SET @sd=(SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,@Date),0)))
    SET @ed=(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0)))

    ;WITH dates AS (
        SELECT @sd as theDate
        UNION ALL
        SELECT DATEADD(day, 1, theDate)
        FROM dates 
        WHERE DATEADD(day, 1, theDate) <= @ed
    )

SELECT  DATEPART(dd, D.theDate) AS 'Day', 
        ISNULL(LS.QtyMT, 0) AS QtyMt, 
        ISNULL(SUM(L.Mn), 0) AS Mn, 
        ISNULL(SUM(L.Si), 0) AS Si, 
        ISNULL(SUM(L.P), 0) AS P, 
        ISNULL(LS.Carbon, 0) AS C, 
        ISNULL(LS.Sulphur, 0) AS S, 
        ISNULL(SUM(L.MnO), 0) AS MnO, 
        ISNULL(SUM(L.CaO), 0) AS CaO, 
        ISNULL(SUM(L.AI2O3), 0) AS AI2O3, 
        ISNULL(SUM(L.MgO), 0) AS MgO, 
        ISNULL(SUM(L.Fe2O3), 0) AS Fe2O3, 
        ISNULL(SUM(L.SiO2), 0) AS SiO2, 
        ISNULL(SUM(L.Basicity), 0) AS Basicity
FROM 
    dates 
    INNER JOIN Lab_Analysis AS L ON L.Date = dates.theDate AND L.Furnace = @Furnace
    LEFT JOIN Lab_Product AS LP ON LP.ID = L.Product AND LP.ID = 1 
    LEFT JOIN Lab_CSAnalysis AS LS ON LS.Date = L.Date AND (L.Furnace = LS.Furnace)
GROUP BY 
    D.theDate, LS.QtyMT, LS.Carbon, LS.Sulphur

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/54709065

复制

相似问题

问在sql server中，数据总是显示在一个炉子里。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在sql server中，数据总是显示在一个炉子里。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在sql server中，数据总是显示在一个炉子里。
EN