SQL Server - 使用聚集索引时如何存储数据页

Question

Andy K

Asked: 2017-10-14 07:07:37 +0800 CST2017-10-14 07:07:37 +0800 CST 2017-10-14 07:07:37 +0800 CST

为什么 msaccess 中的最后一个函数允许我绕过 group by？

772

我对 msaccess 有以下查询

SELECT Trim([T13_RefSupplier_France_List].[Supplier_code]) AS Supplier_code,
       Trim([T13_RefSupplier_France_List].[Art]) AS Internal_reference,
       Trim([fp_rcli]) AS Supplier_reference,
       Last(Trim([ar_fami])) AS Family_code,
       Last(Trim([fp_upri])) AS Purchasing_unit,
       Last(Nz([fp_pcde],0)) AS Purchasing_price,
       Last(Supplier_currency_France.Recode) AS Purchasing_currency,
       Last("") AS Consigned,
       Last(0) AS Eco_order_qty,
       Last(CDbl(Nz(Nz([fp_cond],[ar_qcdi]),0))) AS Pack_order_qty,
       Last(IIf([fp_minc]=0,Nz([fp_cond],[ar_qcdi]),[fp_minc])) AS Min_order_qty,
       0 AS Min_order_value,
       0 AS Product_grossweight,
       0 AS Product_grosscube,
       Last(gpfprodu_France.fp_dela) AS Leadtime_days,
       Nz([Localisation_France].[Site],"Poitiers") AS Site,
       CDbl(Nz([Active],-1)) AS Supplier_active,
       Max(IIf([ar1_pdanz]=0,NULL,[ar1_pdanz])) AS Ref_price
FROM ((T13_RefSupplier_France_List
       LEFT JOIN ((gparticl_France
                   LEFT JOIN Localisation_France ON gparticl_France.ar_loco=Localisation_France.Localisation)
                  LEFT JOIN [*gpartic1_France] ON gparticl_France.ar_code=[*gpartic1_France].ar1_code) ON T13_RefSupplier_France_List.Art=gparticl_France.ar_code)
      LEFT JOIN ((gpfprodu_France
                  LEFT JOIN gpfourni_France ON gpfprodu_France.fp_four=gpfourni_France.fo_code)
                 LEFT JOIN Supplier_currency_France ON gpfourni_France.fo_monn=Supplier_currency_France.fo_monn) ON (T13_RefSupplier_France_List.Supplier_code=gpfprodu_France.fp_four)
      AND (T13_RefSupplier_France_List.Art=gpfprodu_France.fp_arti))
LEFT JOIN T13_RefSupplier_France_SupplierActive ON (T13_RefSupplier_France_List.Art=T13_RefSupplier_France_SupplierActive.Art)
AND (T13_RefSupplier_France_List.Supplier_code=T13_RefSupplier_France_SupplierActive.Supplier_code)
GROUP BY Trim([T13_RefSupplier_France_List].[Supplier_code]),
         Trim([T13_RefSupplier_France_List].[Art]),
         Trim([fp_rcli]),
         Nz([Localisation_France].[Site],"Poitiers"),
         CDbl(Nz([Active],-1))
HAVING (((Trim(T13_RefSupplier_France_List.Supplier_code))<>"FG0002")
        AND ((Trim(T13_RefSupplier_France_List.Art))<>"A60101000000000"));

我可以在GROUP BY不使用具有LAST函数的列的情况下执行操作，但是当我删除 LAST 函数时，我必须添加所有列GROUP BY（见下文）。

SELECT Trim([T13_RefSupplier_France_List].[Supplier_code]) AS Supplier_code,
       Trim([T13_RefSupplier_France_List].[Art]) AS Internal_reference,
       Trim([fp_rcli]) AS Supplier_reference,
       Trim([ar_fami]) AS Family_code,
       Trim([fp_upri]) AS Purchasing_unit,
       Nz([fp_pcde],0) AS Purchasing_price,
       Supplier_currency_France.Recode AS Purchasing_currency,
       "" AS Consigned,
       0 AS Eco_order_qty,
       CDbl(Nz(Nz([fp_cond],[ar_qcdi]),0)) AS Pack_order_qty,
       IIf([fp_minc]=0,Nz([fp_cond],[ar_qcdi]),[fp_minc]) AS Min_order_qty,
       0 AS Min_order_value,
       0 AS Product_grossweight,
       0 AS Product_grosscube,
       gpfprodu_France.fp_dela AS Leadtime_days,
       Nz([Localisation_France].[Site],"Poitiers") AS Site,
       CDbl(Nz([Active],-1)) AS Supplier_active,
       Max(IIf([ar1_pdanz]=0,NULL,[ar1_pdanz])) AS Ref_price
FROM ((T13_RefSupplier_France_List
       LEFT JOIN ((gparticl_France
                   LEFT JOIN Localisation_France ON gparticl_France.ar_loco=Localisation_France.Localisation)
                  LEFT JOIN [*gpartic1_France] ON gparticl_France.ar_code=[*gpartic1_France].ar1_code) ON T13_RefSupplier_France_List.Art=gparticl_France.ar_code)
      LEFT JOIN ((gpfprodu_France
                  LEFT JOIN gpfourni_France ON gpfprodu_France.fp_four=gpfourni_France.fo_code)
                 LEFT JOIN Supplier_currency_France ON gpfourni_France.fo_monn=Supplier_currency_France.fo_monn) ON (T13_RefSupplier_France_List.Supplier_code=gpfprodu_France.fp_four)
      AND (T13_RefSupplier_France_List.Art=gpfprodu_France.fp_arti))
LEFT JOIN T13_RefSupplier_France_SupplierActive ON (T13_RefSupplier_France_List.Art=T13_RefSupplier_France_SupplierActive.Art)
AND (T13_RefSupplier_France_List.Supplier_code=T13_RefSupplier_France_SupplierActive.Supplier_code)
GROUP BY Trim([T13_RefSupplier_France_List].[Supplier_code]),
         Trim([T13_RefSupplier_France_List].[Art]),
         Trim([fp_rcli]),
         Nz([Localisation_France].[Site],"Poitiers"),
         Trim([ar_fami]),
         Trim([fp_upri]),
         Nz([fp_pcde],0),
         Supplier_currency_France.Recode,
         "",
         0,
         CDbl(Nz(Nz([fp_cond],[ar_qcdi]),0)),
         IIf([fp_minc]=0,Nz([fp_cond],[ar_qcdi]),[fp_minc]),
         0,
         0,
         0,
         gpfprodu_France.fp_dela,
         CDbl(Nz([Active],-1))
HAVING (((Trim(T13_RefSupplier_France_List.Supplier_code))<>"FG0002")
        AND ((Trim(T13_RefSupplier_France_List.Art))<>"A60101000000000"));

问题有两个：

函数的行为是什么LAST？
假设我想将LAST函数转置到 SQLServer：我应该怎么做？

谢谢

2 个回答

Voted

Paul White · Answer 1 · 2017-10-15T00:56:04+08:00

函数的行为是什么LAST？

从文档中：

这些函数分别返回查询返回的结果集中第一条或最后一条记录中指定字段的值。如果查询不包含 ORDER BY 子句，则这些函数返回的值将是任意的，因为记录通常没有按特定顺序返回。

这并没有说明子句出现LAST时的行为GROUP BY，但从测试来看，似乎从每个组中第一个或最后一个遇到的FIRST行LAST返回值。

在没有ORDER BY子句的情况下，（每组）由FIRSTand选择的行LAST基本上是任意的。重要的一点是，多个FIRST和LAST函数选择的值将来自同一行。

最后一点意味着您不能仅将FIRSTor替换LAST为MINor MAX（除了不同的语义），因为最小值和最大值通常不会来自同一源行。

假设我想将LAST函数转置到 SQL Server：我应该怎么做？

基本上不可能完全复制它，因为访问行为没有精确定义；除了最简单的情况外，不可能预测将选择哪一行FIRST或LAST在所有情况下都选择哪一行。

也就是说，如果您可以改进查询语义，以便为每个组中的行FIRST或LAST行提供确定性选择，一般的翻译是对每行进行编号（每组升序或降序），然后从编号为 1 的行中选择值。

行编号可以用ROW_NUMBER. 在OVER子句中，GROUP BY列位于部分中，并在PARTITION BY部分中提供确定性排序ORDER BY。您将需要编写子查询或使用公用表表达式 (CTE) 将行号过滤为 1。

例如：

DECLARE @Table1 table
(
    ID integer IDENTITY NOT NULL,
    GroupID integer NOT NULL,
    [Data] integer NOT NULL
);

INSERT @Table1
    (GroupID, [Data])
VALUES
    (1, 3),
    (1, 2),
    (1, 1),
    (2, 6),
    (2, 5),
    (2, 4);

-- FIRST (ordered by ID ASC within GroupID)
WITH Numbered AS
(
    SELECT
        T.GroupID,
        T.[Data],
        rn = ROW_NUMBER() OVER (
                PARTITION BY T.GroupID
                ORDER BY T.ID ASC)
    FROM @Table1 AS T
)
SELECT
    N.GroupID,
    N.[Data]
FROM Numbered AS N
WHERE
    N.rn = 1;

-- LAST (ordered by ID DESC within GroupID)
WITH Numbered AS
(
    SELECT
        T.GroupID,
        T.[Data],
        rn = ROW_NUMBER() OVER (
                PARTITION BY T.GroupID
                ORDER BY T.ID DESC)
    FROM @Table1 AS T
)
SELECT
    N.GroupID,
    N.[Data]
FROM Numbered AS N
WHERE
    N.rn = 1;

演示：db<>fiddle

在SQL Server 2012或更高版本中，这也可以使用FIRST_VALUE和LAST_VALUE窗口函数来完成，但执行计划可能效率较低。此外，这些窗口函数不是聚合，因此您需要编写表达式，以便它为每组的每一行返回相同的值，然后应用任意聚合。例如（使用非确定性排序只是为了多样性）：

DECLARE @Table1 table
(
    ID integer IDENTITY NOT NULL,
    GroupID integer NOT NULL,
    [Data] integer NOT NULL
);

INSERT @Table1
    (GroupID, [Data])
VALUES
    (1, 3),
    (1, 2),
    (1, 1),
    (2, 6),
    (2, 5),
    (2, 4);

WITH Windowed AS
(
    SELECT
        T.GroupID,
        fv = FIRST_VALUE(T.[Data]) OVER (
                PARTITION BY T.GroupID
                ORDER BY T.GroupID
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING)
    FROM @Table1 AS T
)
SELECT
    W.GroupID,
    [Data] = MIN(W.fv)   -- arbitrary aggregate
FROM Windowed AS W
GROUP BY
    W.GroupID;

WITH Windowed AS
(
    SELECT
        T.GroupID,
        lv = LAST_VALUE(T.[Data]) OVER (
                PARTITION BY T.GroupID
                ORDER BY T.GroupID
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING)
    FROM @Table1 AS T
)
SELECT
    W.GroupID,
    [Data] = MAX(W.lv)   -- arbitrary aggregate
FROM Windowed AS W
GROUP BY
    W.GroupID;

演示：db<>fiddle

第三个选择是编写 SQLCLR 用户定义聚合 (UDA)。目前无法保证这些的确定性排序，但实现可能更接近于 Access 所做的。您需要注意所有 UDA 结果均由同一运算符计算，以确保多个 UDA 调用的结果都来自同一源行。

RDFozz · Answer 2 · 2017-10-14T13:32:35+08:00

概括

使用GROUP BY，您只能包括GROUP BY子句中的列、常量和聚合值。
SQL Server 也有类似的窗口函数，LAST_VALUE()但它只存在于 SQL Server 2012 或更高版本中。

详细说明

LAST()就是所谓的聚合函数。聚合函数为查询中的所有行计算一个值（如果没有GROUP BY，或者为子句中的列具有相同值的所有行计算一个值GROUP BY。在 SQL Server 中（我假设在 Access 中），如果您使用SELECT在没有子句的查询中列表（从查询返回的数据列列表）中的聚合函数GROUP BY，则返回的所有内容必须是：

不依赖于表数据的值：
- 查询中的Consigned常量值Min_order_value
- 不对表数据进行操作的函数的结果，例如NOW()在 Access 或GETDATE()SQL Server 中
聚合函数的结果（通常对查询中表中的列进行操作），如LAST()第一个查询或Max(IIf([ar1_pdanz]=0,NULL,[ar1_pdanz]))两个查询中的函数。

如果您有一个GROUP BY子句，那么除了上面的内容之外还有一个补充：该列表还可以包括子句SELECT中列出的列（或计算）。GROUP BY通常，它们必须在两个地方出现完全相同（例如，如果你GROUP BY SUBSTRING(field1,1,20)，你不能只field1在SELECT列表中）。

当你Last(Trim([ar_fami]))输入你的SELECT列表时，你基本上是在告诉 Access：“我的组中会有多个值ar_fami，但我并不关心这些值是什么；只要给我你发生的最后一行的值查看。”

如果您包括Trim([ar_fami])在SELECT列表和中GROUP BY，您是在告诉 Access 您希望在输出中查看每个唯一值，以及这些值的小计/平均值/任何内容，以及GROUP BY.

当您尝试包含Trim([ar_fami])在SELECT列表中但不包含在中时GROUP BY，Access 会感到困惑：对于列的每种可能组合，您可能有多个值GROUP BY，但它无法判断您希望看到这些值中的哪一个。

请注意我们人类：实际上，对于您的每个组中的所有行，实际上所有值都可能完全相同。但是，数据库通常需要在实际开始获取数据之前决定如何获取数据；并且，此时，它无法知道ar_fami每个组中的所有值是否都相同。因此，它必须事先知道如何处理不同的值。

GROUP BY顺便说一句 - 你应该能够在你的第二个查询（the""和 multiple 0s）中从子句中取出常量。由于它们始终相同，因此它们无法更改行的分组。

至于你的第二个问题，从 SQL Server 2012 开始，你可以在那里访问类似的功能。名称有点不同：LAST_VALUE()而不是LAST(). 另外，LAST_VALUE()是一个“窗口函数”；它需要一个OVER()子句，定义可以使用的组以及它将使用的顺序，并且计算结果集中每一行的结果，而不是整个组。

您将不得不创建一个复杂的查询（或者更可能是一组嵌套查询），以便从每个组中看到的最后一条记录中捕获未分组的值。

实际上，这些值似乎用途有限。如果您汇总四个不同地点的销售人员的总销售额，最后一位销售人员的名字是“Tony Warner”真的很重要吗？特别是因为您可能会在两天后使用相同的基础数据运行相同的查询，从而为总计和小计生成相同的组结果 - 但注意到最后一位销售人员的名字是“Frank Northrup”？至少在 SQL Server 中，SQL 会将数据分类到不同的组中以获得聚合值，并将应用ORDER BY您指定的任何内容；但这取决于你的顺序是什么。如果ORDER BY不包括您获得最后一个值的列，或者与特定值相关的列，“最后”行在您每次调用该函数时可能不同，即使是在同一组数据上也是如此。

不过，很明显，有些人认为它值得使用。我可能怀疑那些人并没有真正理解他们得到了什么，或者（更糟的是）人们看到他们的结果会假设什么，但也许有一些我根本没有看到的明确的命令价值。对我来说，如果结果基本上是随机的，我看不出有什么价值。

为什么 msaccess 中的最后一个函数允许我绕过 group by？

概括

详细说明

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

为什么 msaccess 中的最后一个函数允许我绕过 group by？

2 个回答

概括

详细说明

相关问题