我有这个查询,它给了我想要的结果 postgres
SELECT
t.*,
ROW_NUMBER() OVER (PARTITION BY t."Internal_reference", t."Movement_date" ORDER BY t."Movement_date") AS "cnt"
FROM (SELECT
"Internal_reference",
MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
INNER JOIN dw."LO-D4_Movements" t
ON t."Movement_date" = r.maxtime
AND t."Internal_reference" = r."Internal_reference"
问题是我必须Access
在分析函数不存在的地方翻译上面的查询......
我用这个答案来建立下面的查询
SELECT
t."Internal_reference",
t.from_code,
t.to_code,
t."Movement_date",
t.shipment_number,
t."PO_number",
t."Quantity",
t."Movement_value",
t."Site",
t."Import_date",
COUNT(*) AS "cnt"
FROM (
SELECT "Internal_reference",
MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
LEFT OUTER JOIN dw."LO-D4_Movements" t
ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
GROUP BY
t.from_code,
t.to_code,
t."Movement_date",
t.shipment_number,
t."PO_number",
t."Quantity",
t."Movement_value",
t."Site",
t."Import_date",
t."Internal_reference"
ORDER BY t.from_code
问题是我只有1
在cnt
列中。
我试图通过删除internal_reference
(见下文)来调整它
SELECT
t.from_code,
t.to_code,
t."Movement_date",
t.shipment_number,
t."PO_number",
t."Quantity",
t."Movement_value",
t."Site",
t."Import_date",
COUNT(*) AS "cnt"
FROM (
SELECT "Internal_reference",
MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
LEFT OUTER JOIN dw."LO-D4_Movements" t
ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
GROUP BY
t.from_code,
t.to_code,
t."Movement_date",
t.shipment_number,
t."PO_number",
t."Quantity",
t."Movement_value",
t."Site",
t."Import_date"
ORDER BY t.from_code
然而,结果更糟。在cnt
不断增长,但它给我的错cnt
任何帮助都非常受欢迎,因为我正在慢慢失去理智。
谢谢
编辑:请找到sqlfiddle
我认为 Gordon-Linoff 的代码接近你想要的,但有一些错别字我不能不重写就更正,所以这是我的尝试
SELECT
t1.Internal_reference,
t1.Movement_date,
t1.PO_Number as Combination_Of_Columns_Which_Make_This_Unique,
t1.Other_columns,
Count(1) AS Cnt
FROM
([LO-D4_Movements] AS t1
INNER JOIN [LO-D4_Movements] AS t2 ON
t1.Internal_reference = t2.Internal_reference AND
t1.Movement_date = t2.Movement_date)
INNER JOIN (
SELECT
t3.Internal_reference,
MAX(t3.Movement_date) AS Maxtime
FROM
[LO-D4_Movements] AS t3
GROUP BY
t3.Internal_reference
) AS r ON
t1.Internal_reference = r.Internal_reference AND
t1.Movement_date = r.Maxtime
WHERE
t1.PO_Number>=t2.PO_Number
GROUP BY
t1.Internal_reference,
t1.Movement_date,t1.PO_Number,
t1.Other_columns
ORDER BY
t1.Internal_reference,
t1.Movement_date,
Count(1);
除了max(movement_date)
子查询内,主表被带入两次。一个版本用于在您的结果中显示,另一个版本用于计算记录以生成序列号。
Gordon 说你需要为每一行一个唯一的 id 列。如果“列”的意思是还包括派生列,那就是对的。此外,它只需要在“internal_reference”和“Movement_date”的任意组合中是唯一的。
我可能错误地假设 PO_Number 就足够了。如果没有,请与该(和一些分隔符)其他字段连接,这将使其独一无二。需要对where子句进行更新,以比较t1和t2的“使此唯一的列组合”。
如果没有合适的组合可用,我不确定它可以在没有 The-Gambill 建议的 VBA 和/或临时表的情况下完成。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句