代码之家  ›  专栏  ›  技术社区  ›  Shivang

求和合计

  •  0
  • Shivang  · 技术社区  · 9 年前

    我写这个查询是为了找到网络总和。但我得到了一个错误。你们能帮帮我吗?

    SELECT SUM(net) AS net
    FROM (select SUM(FB.[Net]) as net  FROM  (([table1 AS FB INNER JOIN
    Date ON FB.DateKey =  Date.DateKey)
    inner join [Store] on [Store].StoreKey = FB.StoreKey) WHERE (FullDate BETWEEN DATEADD(WEEK, -12, CONVERT(date, GETDATE())) 
    AND DATEADD(day, -1, CONVERT(date, GETDATE()))
    AND SName = 'XYZ'
    AND DayNameOfWeek = 'Monday')
    GROUP BY FB.[Net])
    

    谨致问候

    2 回复  |  直到 9 年前
        1
  •  0
  •   HoneyBadger    9 年前

    格式化代码是关键:

    SELECT  SUM(net) AS net
    FROM    (
                SELECT  SUM(FB.[Net]) as net  
                FROM    ((
                            [table1 AS FB 
                            INNER JOIN  Date 
                                    ON  FB.DateKey =  Date.DateKey
                        )
                        inner join [Store] 
                                on [Store].StoreKey = FB.StoreKey
                        ) 
                        WHERE   (FullDate BETWEEN DATEADD(WEEK, -12, CONVERT(date, GETDATE())) 
                            AND DATEADD(day, -1, CONVERT(date, GETDATE()))
                            AND SName = 'XYZ'
                            AND DayNameOfWeek = 'Monday')
                        GROUP BY FB.[Net]
                        )
    

    我现在可以看到很多错误。我认为这可以解决大多数语法错误,但我不确定它是否能给出您想要的结果:

    SELECT  SUM(net) AS net
    FROM    (
                SELECT  SUM([Net]) as net  
                FROM    (
                            SELECT      FB.net
                            FROM        table1 AS FB 
                            INNER JOIN  [Date] 
                                    ON  FB.DateKey =  [Date].DateKey                    
                            inner join  [Store] 
                                    on  [Store].StoreKey = FB.StoreKey                  
                            WHERE   FullDate BETWEEN DATEADD(WEEK, -12, GETDATE()) AND DATEADD(day, -1, GETDATE())
                                AND SName = 'XYZ'
                                AND DayNameOfWeek = 'Monday'
                            GROUP BY FB.[Net]
                        ) A
            ) B
    

    编辑

    SELECT      SUM(DISTINCT FB.net)
    FROM        table1 AS FB 
    INNER JOIN  [Date] 
            ON  FB.DateKey =  [Date].DateKey                    
    INNER JOIN  [Store] 
            ON  [Store].StoreKey = FB.StoreKey                  
    WHERE       FullDate BETWEEN DATEADD(WEEK, -12, GETDATE()) AND DATEADD(day, -1, GETDATE())
            AND SName = 'XYZ'
            AND DayNameOfWeek = 'Monday'
    

    你确定这就是你想要的吗?

        2
  •  0
  •   StackUser    9 年前

    尝试这样做,删除不必要的括号并添加适当的别名。请将别名添加到“Where”子句筛选器。

    SELECT SUM(net) AS net
    FROM (
        SELECT SUM(FB.[Net]) AS net
        FROM [table1] FB
        INNER JOIN [Date] D ON FB.DateKey = D.DateKey
        INNER JOIN [Store] S ON S.StoreKey = FB.StoreKey
        WHERE FullDate BETWEEN DATEADD(WEEK, - 12, CONVERT(DATE, GETDATE()))
                AND DATEADD(day, - 1, CONVERT(DATE, GETDATE()))
            AND SName = 'XYZ'
            AND DayNameOfWeek = 'Monday'
        GROUP BY FB.[Net]
        ) t