将联接限制为一行 [英] Limit join to one row

问题描述

我有以下查询:

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"的最后一个条目.

The problem is that the table "EsnsSalesOrderItems" can have the same EsnId in different entries. I want to restrict the query to only pull the last entry in "EsnsSalesOrderItems" that has the same "EsnId".

在最后一个"条目中，我的意思是:

By "last" entry I mean the following:

在表"EsnsSalesOrderItems"中最后出现的那个.因此，例如，如果"EsnsSalesOrderItems"有两个分别与"EsnId" = 6和"createdAt" = '2012-06-19'和'2012-07-19'对应的条目，则应该只给我来自'2012-07-19'的条目.

The one that appears last in the table "EsnsSalesOrderItems". So for example if "EsnsSalesOrderItems" has two entries with "EsnId" = 6 and "createdAt" = '2012-06-19' and '2012-07-19' respectively it should only give me the entry from '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;

您在问题中的查询的聚合非法于另一个聚合:

Your query in the question has an illegal aggregate over another aggregate:

sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount

已简化并转换为合法语法:

Simplified and converted to legal syntax:

(count(*) * sum(s."price")) AS amount

但是您真的要乘以每组的数量吗?

But do you really want to multiply with the count per group?

我用 DISTINCT ON 检索"EsnsSalesOrderItems"中每组的单行.详细说明:

I retrieve the the single row per group in "EsnsSalesOrderItems" with DISTINCT ON. Detailed explanation:

• 在每个GROUP BY组中选择第一行?

我还添加了表别名和格式，以使查询更易于人眼解析.如果您可以避免使用骆驼套，您可以消除所有的双引号笼罩视图.

I also added table aliases and formatting to make the query easier to parse for human eyes. If you could avoid camel case you could get rid of all the double quotes clouding the view.

这篇关于将联接限制为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！