时间戳

首页 > 解决方案 > 如何在 SQL Server 中编写我的 MS Access 查询？

sql - 如何在 SQL Server 中编写我的 MS Access 查询？

问题描述

我有一个运行良好的 MS Access 查询，但我不知道如何在 SQL Server 中编写此查询。

MS Access 查询：-

SELECT lp.PortfolioID, 
       Count(IIf(lp.Data_Status = 'Prelim Collection', 1, Null)) AS Prelim_Collection_Cnt, 
       Count(IIf(lp.Data_Status = 'Final Collection', 1, Null)) AS Final_Collection_Cnt, 
       Count(IIf(lp.Data_Status = 'Prelim Entry', 1, Null)) AS Prelim_Entry_Cnt, 
       Count(IIf(lp.Data_Status = 'Final Entry', 1, Null)) AS Final_Entry_Cnt, 
       Count(IIf(lp.Data_Status = 'Prelim QC', 1, Null)) AS Prelim_QC_Cnt, 
       Count(IIf(lp.Data_Status = 'Final QC', 1, Null)) AS Final_QC_Cnt
FROM (
      SELECT DISTINCT p.PortfolioID, p.Data_Status 
      FROM Late_Portfolios AS p 
      WHERE p.TimeStamp BETWEEN #10/01/2018# AND #10/31/2018#)  AS lp
GROUP BY lp.PortfolioID;

标签： sqlsql-server

解决方案

你最好的选择是切换到 CASE 语句，因为这些语句几乎可以在阳光下的每个 RDBMS 中工作（除了 Access ......我无法想象为什么它仍然不支持这个，但那是你的 MS）。除此之外，您希望将日期文字转换为 ISO8601 格式YYYY-MM-DD并将它们封装在单引号中而不是散列中。

SELECT lp.PortfolioID,  
    COUNT(CASE WHEN lp.Data_Status = 'Prelim Collection' THEN 1 END) AS Prelim_Collection_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final Collection' THEN 1 END) AS Final_Collection_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Prelim Entry' THEN 1 END) AS Prelim_Entry_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final Entry' THEN 1 END) AS Final_Entry_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Prelim QC' THEN 1 END) AS Prelim_QC_Cnt, 
    COUNT(CASE WHEN lp.Data_Status = 'Final QC' THEN 1 END) AS Final_QC_Cnt
FROM
    (
        SELECT DISTINCT 
            p.PortfolioID, 
            p.Data_Status 
        FROM Late_Portfolios AS p 
        WHERE p.TimeStamp BETWEEN '2018-10-01' AND '2018-10-31'
    )  AS lp
GROUP BY lp.PortfolioID;

推荐阅读