返回行值的总和和行数

huangapple go评论66阅读模式
英文:

Returning sum of row values and row count

问题

我有一个查询,用于返回表列中值的总和。我想要现在还返回行数。

以下是我的当前查询的代码:

db.select(DSL.coalesce(DSL.sum(AMOUNT), 0).cast(Long::class.java))
    .from(this.innerJoin(ACCOUNT).on(ACCOUNT_ID.eq(ACCOUNT.ID)))
    .where(ACCOUNT.ACCOUNT_ID.eq(accId))
    .awaitSingle()
    .value1()
英文:

I have a query to return the sum of the values in a table column. I'd like to now also return the row count.

Here is what my current query looks like:

db.select(DSL.coalesce(DSL.sum(AMOUNT), 0).cast(Long::class.java))
                .from(this.innerJoin(ACCOUNT).on(ACCOUNT_ID.eq(ACCOUNT.ID)))
                .where(ACCOUNT.ACCOUNT_ID.eq(accId))
                .awaitSingle()
                .value1()

答案1

得分: 1

你可以只需在相同的 jOOQ 查询中添加另一个聚合函数,例如:

val (sum, count) = 
db.select(
     coalesce(sum(AMOUNT), 0).coerce(Long::class.java),
     count())
  .from(this.innerJoin(ACCOUNT).on(ACCOUNT_ID.eq(ACCOUNT.ID)))
  .where(ACCOUNT.ACCOUNT_ID.eq(accId))
  .awaitSingle()

这个答案假设通常的 import org.jooq.impl.DSL.*

英文:

You can just add another aggregate function to the same jOOQ query, e.g.:

val (sum, count) = 
db.select(
     coalesce(sum(AMOUNT), 0).coerce(Long::class.java),
     count())
  .from(this.innerJoin(ACCOUNT).on(ACCOUNT_ID.eq(ACCOUNT.ID)))
  .where(ACCOUNT.ACCOUNT_ID.eq(accId))
  .awaitSingle()

This answer assumes the usual import org.jooq.impl.DSL.*

huangapple
  • 本文由 发表于 2023年6月6日 12:23:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76411433.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定