PostgreSQL与Go中如何用CASE WHEN匹配二维数组实现产品分类加分？

方法1：使用jsonb类型（推荐） #

这种方式适配Go的JSON处理习惯，PostgreSQL对jsonb的支持也非常成熟，写法简洁且性能稳定。

SQL语句

SELECT 
  products.*,
  -- 匹配不到时默认返回0，转成int类型
  COALESCE(($1::jsonb) ->> CAST(products.category AS text), '0')::int AS add_score
FROM products;

Go调用示例

import (
  "database/sql"
  "encoding/json"
  _ "github.com/lib/pq"
)

// 定义分类-权重映射
type CategoryScore map[int]int

func main() {
  db, err := sql.Open("postgres", "your_dsn_here")
  if err != nil {
    // 错误处理
  }
  defer db.Close()

  // 构造权重规则
  scoreRule := CategoryScore{1: 100, 3: 50, 4: 120}
  jsonRule, err := json.Marshal(scoreRule)
  if err != nil {
    // 错误处理
  }

  // 执行查询
  rows, err := db.Query(`
    SELECT id, category, COALESCE(($1::jsonb)->>CAST(category AS text), '0')::int AS add_score
    FROM products
  `, string(jsonRule))
  if err != nil {
    // 错误处理
  }
  defer rows.Close()

  // 处理查询结果
  for rows.Next() {
    var id, category, addScore int
    if err := rows.Scan(&id, &category, &addScore); err != nil {
      // 错误处理
    }
    // 业务逻辑处理
  }
}

方法2：使用PostgreSQL二维数组 + unnest #

如果必须传入二维数组格式（如[[1,100],[3,50],[4,120]]），可以通过unnest将数组拆分为临时表，再通过左连接匹配权重。

SQL语句

WITH category_scores AS (
  SELECT 
    -- 拆分二维数组，取子数组的第一个元素作为分类ID
    (unnest($1::int[][]))[1] AS category,
    -- 取子数组的第二个元素作为权重值
    (unnest($1::int[][]))[2] AS score
)
SELECT 
  p.*,
  COALESCE(cs.score, 0) AS add_score
FROM products p
LEFT JOIN category_scores cs ON p.category = cs.category;

Go调用示例

import (
  "database/sql"
  _ "github.com/lib/pq"
)

func main() {
  db, err := sql.Open("postgres", "your_dsn_here")
  if err != nil {
    // 错误处理
  }
  defer db.Close()

  // 构造二维数组规则
  scoreRule := [][]int{{1, 100}, {3, 50}, {4, 120}}

  // 执行查询
  rows, err := db.Query(`
    WITH category_scores AS (
      SELECT 
        (unnest($1::int[][]))[1] AS category,
        (unnest($1::int[][]))[2] AS score
    )
    SELECT id, category, COALESCE(cs.score, 0) AS add_score
    FROM products p
    LEFT JOIN category_scores cs ON p.category = cs.category
  `, scoreRule)
  if err != nil {
    // 错误处理
  }
  defer rows.Close()

  // 处理查询结果
  // ...
}

方法3：动态生成CASE表达式（适合规则较少场景） #

如果分类权重规则数量不多，可以在Go中动态拼接CASE语句，注意必须使用参数化查询避免SQL注入。

Go实现示例

import (
  "database/sql"
  "fmt"
  "strings"
  _ "github.com/lib/pq"
)

type CategoryScore map[int]int

func main() {
  db, err := sql.Open("postgres", "your_dsn_here")
  if err != nil {
    // 错误处理
  }
  defer db.Close()

  scoreRule := CategoryScore{1: 100, 3: 50, 4: 120}
  var caseClauses []string
  var args []interface{}
  argIndex := 1

  // 动态生成WHEN子句
  for cat, score := range scoreRule {
    caseClauses = append(caseClauses, fmt.Sprintf("WHEN category = $%d THEN $%d", argIndex, argIndex+1))
    args = append(args, cat, score)
    argIndex += 2
  }

  // 拼接完整CASE表达式
  caseExpr := strings.Join(caseClauses, " ")
  if caseExpr == "" {
    caseExpr = "ELSE 0"
  } else {
    caseExpr += " ELSE 0"
  }

  // 构造查询语句
  query := fmt.Sprintf(`
    SELECT id, category, CASE %s END AS add_score
    FROM products
  `, caseExpr)

  // 执行参数化查询
  rows, err := db.Query(query, args...)
  if err != nil {
    // 错误处理
  }
  defer rows.Close()

  // 处理查询结果
  // ...
}

内容的提问来源于stack exchange，提问作者JackPhillips