我有以下查询：
SELECT sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount, 'rma' as
"creditType", "Clients"."company" as "client", "Clients".id as "ClientId", "Rmas".*
FROM "Rmas" JOIN "EsnsRmas" on("EsnsRmas"."RmaId" = "Rmas"."id")
JOIN "Esns" on ("Esns".id = "EsnsRmas"."EsnId")
JOIN "EsnsSalesOrderItems" on("EsnsSalesOrderItems"."EsnId" = "Esns"."id" )
JOIN "SalesOrderItems" on("SalesOrderItems"."id" = "EsnsSalesOrderItems"."SalesOrderItemId")
JOIN "Clients" on("Clients"."id" = "Rmas"."ClientId" )
WHERE "Rmas"."credited"=false AND "Rmas"."verifyStatus" IS NOT null
GROUP BY "Clients".id, "Rmas".id;
问题是表"EsnsSalesOrderItems"可以有相同的EsnId在不同的条目中。我想限制查询仅提取最后一个条目"EsnsSalesOrderItems"具有相同的"EsnId".
我所说的“最后”条目是指以下内容：
表中最后出现的那个"EsnsSalesOrderItems"。例如如果"EsnsSalesOrderItems"有两个条目"EsnId" = 6 and "createdAt" = '2012-06-19' and '2012-07-19'分别它应该只给我来自的条目'2012-07-19'.
SELECT (count(*) * sum(s."price")) AS amount
, 'rma' AS "creditType"
, c."company" AS "client"
, c.id AS "ClientId"
, r.*
FROM "Rmas" r
JOIN "EsnsRmas" er ON er."RmaId" = r."id"
JOIN "Esns" e ON e.id = er."EsnId"
JOIN (
SELECT DISTINCT ON ("EsnId") *
FROM "EsnsSalesOrderItems"
ORDER BY "EsnId", "createdAt" DESC
) es ON es."EsnId" = e."id"
JOIN "SalesOrderItems" s ON s."id" = es."SalesOrderItemId"
JOIN "Clients" c ON c."id" = r."ClientId"
WHERE r."credited" = FALSE
AND r."verifyStatus" IS NOT NULL
GROUP BY c.id, r.id;
您在问题中的查询在另一个聚合上存在非法聚合：
sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount
简化并转换为合法语法：
(count(*) * sum(s."price")) AS amount
但你真的想乘以每组的计数吗？
我检索每组的单行"EsnsSalesOrderItems" with DISTINCT ON。详细解释：
- 选择每个 GROUP BY 组中的第一行？ https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
我还添加了表别名和格式，以使查询更易于人眼解析。如果你可以避免驼峰式大小写，您可以去掉所有双引号 http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS使视野变得模糊。
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
