将MS Access SQL语句重写为SQL Server [英] Rewrite MS Access SQL statement to SQL Server

问题描述

我希望将以下MS Access SQL语句转换为SQL Server.我所有的尝试都导致与旧的&原始数据.

I wish to convert the following MS Access SQL statement to SQL Server. All of my attempts are resulting in different results from the old & original data.

SELECT 
    Sum(ADA_LAST.MA) AS MA, 
    Sum(ADA_LAST.DA) AS DA, 
    ADA_LAST.ID_BAS, 
    ADA_LAST.PRO_NUMBER, 
    ADA_LAST.ACC_NUMBER, 
    ADA_LAST.DATA, 
    "" AS Q,
     "" AS P, 
    Last(ADA_LAST.Date) AS [DATE], 
    "" AS UNIT, 
    0 AS ID, 
    [MA]-[DA] AS R
FROM ADA_LAST
GROUP BY 
    ADA_LAST.ID_BAS, 
    ADA_LAST.PRO_NUMBER, 
    ADA_LAST.ACC_NUMBER, 
    ADA_LAST.DATA, 
    "", 
    0, 
    [MA]-[DA],
    "", 
    ""
;

新查询为:

SELECT 
    MA = Sum([ADA_LAST].[MA]), 
    DA = Sum([ADA_LAST].[DA]), 
    [ADA_LAST].[ID_BAS],
    [ADA_LAST].[PRO_NUMBER], 
    [ADA_LAST].[ACC_NUMBER], 
    [ADA_LAST].[DATA], 
    Q = '', 
    P = '', 
    [DATE] = ADA_LAST.[Date],
    UNIT = '', 
    ID = 0, 
    Sum([ADA_LAST].[MA]) - Sum([ADA_LAST].[DA]) AS R
FROM [ADA_LAST](@PRO_NAME,@SDAY)
GROUP BY 
    [ADA_LAST].[ACC_NUMBER],
    [ADA_LAST].[Date],
    [ADA_LAST].[PRO_NUMBER],
    [ADA_LAST].[ID_BAS], 
    [ADA_LAST].[DATA]

由新语句中的日期列分组引起的问题，但在旧版本中，它在上一个函数中用于避免对其进行分组，并且仍然存在于select语句中，我该怎么做.

The problem caused by grouping date column in new statement, but in old one it is used in Last function to avoid grouping it and still exists in the select statement, How can I do like this.

推荐答案

您可以尝试以下查询.更改为原始版本:

You can try the below query. Changes to the original:

• 空字符串记为''，而不是""
• 我将LAST替换为MAX();因为您正在使用聚合，所以这很可能会满足您的要求
• 常量列不需要在GROUP BY子句中列出

• empty string is note as '' instead of ""
• I replace LAST with MAX(); this is likely to do what you want, since you are using aggregation
• constant columns do not need to be listed in the GROUP BY clause

代码:

SELECT 
    SUM(ADA_LAST.MA) AS MA, 
    SUM(ADA_LAST.DA) AS DA, 
    ADA_LAST.ID_BAS, 
    ADA_LAST.PRO_NUMBER, 
    ADA_LAST.ACC_NUMBER, 
    ADA_LAST.DATA, 
    '' AS Q, 
    '' AS P, 
    MAX(ADA_LAST.Date) AS [DATE], 
    '' AS UNIT, 
    0 AS ID, 
    [MA] - [DA] AS R
FROM ADA_LAST
GROUP BY 
    ADA_LAST.ID_BAS, 
    ADA_LAST.PRO_NUMBER, 
    ADA_LAST.ACC_NUMBER, 
    ADA_LAST.DATA, 
    [MA] - [DA]
;

这篇关于将MS Access SQL语句重写为SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！