r-sqldf只返回一行，与SQL中使用的查询相同

Question

由于某些原因，当涉及到R时，我只返回一行，而在SQL Server中，我返回了正确的行数。SQLDF:

CustomerCodingChangesT <- sqldf("
SELECT c.CustID as ID 
, c.ReverseSupplier as Supplier
, c.ReverseCustomerCode as Code
, c.Name
, c.Address
, c.[From PostCode]
, c.[From Outlet]
, c.[From OutletName] 
, o.FullAddress AS [From Address]
, c.[To PostCode]
, c.[To Outlet]
, c.[To OutletName]
, o1.FullAddress AS [To Address]
, MAX(CAST(c.TotalUnits AS VARCHAR)) AS [Total Units]
, '$'+MAX(CAST(c.TotalValue AS VARCHAR)) AS [Total Value]
, '' AS Checked
, c.CustRecActive as Active
FROM CustomerCorrectionSummaryT AS c
LEFT JOIN OutletMasterT AS o 
ON  c.[From PostCode] = o.Postcode 
AND c.[From Outlet] = o.Outlet 
LEFT JOIN OutletMasterT AS o1 
ON  c.[To PostCode] = o1.Postcode 
AND c.[To Outlet] = o1.Outlet
ORDER BY c.totalvalue DESC;")

SQL:

if object_id ('tempdb..#CustomerCodingChanges') is not null drop table #CustomerCodingChanges
SELECT c.CustID as ID
, c.ReverseSupplier as Supplier
, c.ReverseCustomerCode as Code
, c.Name
, c.Address
, c.[From Postcode]
, c.[From Outlet]
, c.[From OutletName]
, o.FullAddress AS [From Address]
, c.[To Postcode]
, c.[To Outlet]
, c.[To OutletName]
, o1.FullAddress AS [To Address]
, CAST(c.TotalUnits AS VARCHAR(MAX)) AS [Total Units]
, '$'+CAST(c.TotalValue AS VARCHAR(MAX)) AS [Total Value]
, '' AS Checked 
, c.CustRecActive as Active
INTO #CustomerCodingChanges
FROM #CustomerCorrectionSummary AS c
LEFT JOIN ndf_061.IRGMaster.dbo.OutletMaster AS o 
ON  c.[From Postcode] = o.postcode 
AND c.[From Outlet] = o.outlet
LEFT JOIN ndf_061.IRGMaster.dbo.OutletMaster AS o1 
ON  c.[To Postcode] = o1.postcode 
AND c.[To Outlet] = o1.outlet
ORDER BY c.totalvalue DESC;

CustomerCorrectionSummaryT和OutletMasterT的数据帧在R和SQL Server中都有相同数量的结果，所以我不知道为什么它在R中与在SQL Server中不会显示相同数量的数据。在SQL Server中，查询返回22行，而在R中，我只得到一行，这两行都是正确的。R的sqldf并没有显示全部。我想这与我的left join函数有关，但我真的不知道。如果您需要更多信息，请告诉我！

Answer 1

如果仔细查看，这两个查询是不相同的。值得注意的是，您在第一个查询中调用了一个聚合MAX，而在第二个查询中并没有调用它。因此，第一个查询是聚合查询，它通过在指定列上返回聚合(SUM、COUNT、AVG、MIN、MAX等(的各种分组来折叠单元级行。

此外，由于不包含GROUP BY子句，因此在标准ANSI SQL中，第一个查询应该会引发错误。不幸的是，对于SQL的新手来说，一些方言(如SQLite和MySQL(允许这样的语法。默认情况下，R的sqldf运行SQLite。事实上，如果您在SQL Server中运行第一个查询，它会出错，因为您运行了一个不正确的聚合查询，因为您没有在GROUP BY子句中包括非聚合列，尽管它们是在SELECT子句中指定的。

仔细观察，您似乎正在尝试在SQLServer中翻译以下表达式

CAST(c.TotalUnits AS VARCHAR(MAX)) AS [Total Units]

这与第一个查询中的尝试行不同：

MAX(CAST(c.TotalUnits AS VARCHAR)) AS [Total Units]

SQLite实际上并没有在VARCHAR类型中保持最大长度。因此，只需保持原始行，不受聚合或大小限制：

CAST(c.TotalUnits AS VARCHAR) AS [Total Units]