主页 / coding / 问题 / 79352109
Accepted
David Bullock
Asked: 2025-01-13 19:56:31 +0800 CST

在 SQL Server 中编译按 CASE 分组的准备好的语句是否存在已知问题？

  • 772

我正在尝试根据案例表达式进行分组。下面的 JOOQ 似乎是正确的做法，并且或多或少产生了我期望的 SQL。从“sakila”数据库（请原谅我的 Kotlin，它必须转义“when”和“as”）：

val shared = `when`(ACTOR.LAST_NAME.like("A%"), "A")
    .`when`(ACTOR.LAST_NAME.like("B%"), "B")
    .otherwise("C")

val r = ctx.select(
    count(), shared.`as`("code")
)
    .from(ACTOR)
    .groupBy(shared)
    .fetch()

SQL Server 2017 Express（v14）抱怨：

列“DTB_DEV_SAKILA.dbo.actor.last_name”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

这很令人惊讶，因为确实如此。以下是完整的调试/堆栈跟踪（我已将其半格式化以便于阅读）：

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [
select count(*), 
case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
else ? end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? else ? end
]; Column 'DTB_DEV_SAKILA.dbo.actor.last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

令人恼火的是，如果我剪切/粘贴查询（由堆栈跟踪吐出）并用实际值替换占位符/准备好的语句“？”，SSMS 就可以很好地执行它。

select 
    count(*), 
    case 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'A%' 
            then 'A' 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'B%' 
            then 'B' 
        else 'C' 
    end as code
from
    [DTB_DEV_SAKILA].[dbo].[actor] 
group by 
    case 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'A%' then 'A' 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'B%' then 'B' 
        else 'C' 
    end as code

笔记：

  • 如果我使用 JOOQ 的inline()函数来摆脱准备好的语句“？”（呃，绑定变量），它可以正常工作；
  • 使用 IIF 而不是 CASE WHE N ...没有任何喘息的机会，它抱怨同样的事情。

但是这段代码（我猜没有绑定变量，只是列引用）运行得很好：

val shared = length(ACTOR.LAST_NAME)

val r = ctx.select(
    count(), shared.`as`("voluminousness")
)
    .from(ACTOR)
    .groupBy(shared)
    .fetch()

因此，问题在于，在尝试编译时，根据 case 语句进行分组的一些联系PreparedStatement。

这似乎更像是 JDBC / SQL Server 问题，而不是 JOOQ 问题。但是 JOOQ 用户通常会采用与此处不同的方法吗？有解决方法吗？其他数据库是否也有这样的分组限制CASE？

JOOQ 3.19.10、MS JDBC 12.8.1.jre11、SQL Server 2017（v14.0.2065）

sql-server

2 个回答

  1. Best Answer

    问题是您生成的代码使用 指定了参数?。这在 JDBC/ODBC 中很正常，但 SQL Server 不直接支持这一点。实际发生的情况是它们被翻译成@p1 @p2。

    因此，您生成的 SQL 变成如下所示，这很明显就是它不起作用的原因：参数名称不同，并且编译器不知道它们是相同的（理论上您可以传递不同的值）。

    select
  count(*), 
  case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p0 then @p1 
       when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p2 then @p3 
       else ?
  end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by
  case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p4 then @p5 
       when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p6 then @p7 
       else ?
  end;

    有很多方法可以解决这个问题。最简单的方法可能是APPLY。我不确定 jOOQ 中的确切语法，但我相信它确实支持它。

    你想要这样的东西

    select
  count(*), 
  v.code
from [DTB_DEV_SAKILA].[dbo].[actor] a
cross apply (
    select
      case when a.[last_name] like ? then ?
           when a.[last_name] like ? then ?
           else ?
      end as code
) v
group by
  v.CaseExpr;

    在 jOOQ 中似乎可能是这样的

    val shared = `when`(ACTOR.LAST_NAME.like("A%"), "A")
    .`when`(ACTOR.LAST_NAME.like("B%"), "B")
    .otherwise("C")

val calc = select(shared.`as`("code")

val r = ctx.select(
    count(), 
    calc.shared
)
    .from(ACTOR.crossApply(calc))
    .groupBy(calc.shared)
    .fetch()
    • 4

  2. 作为 jOOQ 中的一项新选择功能，这绝对是一个值得探索的有趣想法：

    它不会默认启用，因为这可能会对诸如 之类的方法产生许多微妙的影响Query.getBindValues()，或者用户对绑定值排序做出的任何假设。JDBC 不支持命名参数，但可以像这样模拟：

    -- Ordinary indexed parameters
declare @p1 varchar(max) = ?;
declare @p2 varchar(max) = ?;
declare @p3 varchar(max) = ?;
declare @p4 varchar(max) = ?;
declare @p5 varchar(max) = ?;

-- Now, the named parameters can be re-used safely
select
  count(*), 
  case
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p1 then @p2
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p3 then @p4 
    else @p5
  end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by
  case
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p1 then @p2
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p3 then @p4 
    else @p5
  end

    这也是 mssql-jdbc 在幕后所做的，只不过它不知道某些绑定值是相同的（它不可能知道这一点，并且 jOOQ 无法将此信息传达给 mssql-jdbc）。但 jOOQ 可以知道此信息，因为绑定值包装器的各个对（类型化org.jooq.Param）的标识是相同的。

    同时，最简单的方法是使用DSL.inline()您提到的方法，在共享表达式中创建内联值，以避免绑定变量：

    val shared = `when`(ACTOR.LAST_NAME.like(inline("A%")), inline("A"))
    .`when`(ACTOR.LAST_NAME.like(inline("B%")), inline("B"))
    .otherwise(inline("C"))

    或者，只需为此查询运行静态语句

    • 1

相关问题