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

求和合计

sql-query-store sql-server-2008 sql

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 年前

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'

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

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