时间戳

首页 > 解决方案 > 重复值

sql - 重复值

问题描述

我有这段代码，例如，今年的 CA 和前一年的 CA_AN。问题出在 CA_AN 中，我得到了图片中显示的一些值。 在此处输入图像描述

    SELECT DATENAME(month, GL_DATEPIECE) AS 'MONTH', et_region as 'REGION', e.et_etablissement as 'STORE CODE',et_libelle as 'STORE NAME',
        CA=CONVERT(DECIMAL(15,2),sum(gl_totalttcdev)),

        CA_AN=(SELECT CONVERT(DECIMAL(15,2),sum(gl_totalttcdev))
        from piece
        left join etabliss as e1 on e1.et_etablissement= gp_etablissement

        where
CAST(GP_DATEPIECE AS DATE) BETWEEN  DATEADD(YEAR,-1,CAST(@DATED AS DATE))  and DATEADD(YEAR,-1,CAST(@DATEF AS DATE))
        group by et_etablissement, et_libelle
        ),

    from piece 
    left join etabliss as e on gp_etablissement=e.et_etablissement  
    and CAST(GP_DATEPIECE AS DATE) BETWEEN  CAST(@DATED AS DATE) and CAST(@DATEF AS DATE) 

    group by et_region,et_etablissement, et_libelle, DATENAME(month, GL_DATEPIECE)
    order by et_region,CA_AN DESC,et_etablissement

标签： sqlsql-serversubquery

解决方案

可能需要一些迭代，但这里有一个示例，您可以从中构建。您可以根据当前年份和上一年的日期范围有选择地对结果求和，而无需执行子查询。更容易阅读，并且可能表现更好。

这假定 [GP_DATEPIECE] 是日期或日期时间列。

DECLARE @StartDate date = '1/1/2019'
DECLARE @EndDate date = '4/30/2019'
DECLARE @StartDatePreviousYear date = DATEADD(YEAR, -1, @StartDate)
DECLARE @EndDatePreviousYear date = DATEADD(YEAR, -1, @EndDate)

SELECT
     DATENAME(month, [GL_DATEPIECE]) AS [MONTH]
    ,[et_region] AS [REGION]
    ,[e].[et_etablissement] AS [STORE CODE]
    ,[et_libelle] AS [STORE NAME]
    ,SUM(CASE WHEN [GP_DATEPIECE] BETWEEN @StartDate AND @EndDate THEN ISNULL([gl_totalttcdev], 0) ELSE 0 END) AS [CA]
    ,SUM(CASE WHEN [GP_DATEPIECE] BETWEEN @StartDatePreviousYear AND @EndDatePreviousYear THEN ISNULL([gl_totalttcdev], 0) ELSE 0 END) AS [CA_AN]
    ,SUM(CASE WHEN [GP_DATEPIECE] BETWEEN @StartDate AND @EndDate THEN 1 ELSE 0 END) AS [CurrentYearItemCount]
    ,SUM(CASE WHEN [GP_DATEPIECE] BETWEEN @StartDatePreviousYear AND @EndDatePreviousYear THEN 1 ELSE 0 END) AS [PreviousYearItemCount]
FROM [piece]
    LEFT OUTER JOIN [etabliss] AS [e] ON [piece].[gp_etablissement] = [e].[et_etablissement]
GROUP BY
     DATENAME(month, [GL_DATEPIECE])
    ,[et_region]
    ,[et_etablissement]
    ,[et_libelle]
ORDER BY
     [et_region] ASC
    ,[et_etablissement] ASC

推荐阅读