php - SQL 参数数据类型 int 对于 charindex 函数的参数 1 无效

Question

我正在尝试将 MySQL 查询转换为 MsSql 查询，但遇到了问题。这是我的查询:

MySQL

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND FIND_IN_SET(categories.id, books.multiple_category_id)) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

我试过的SQL

SELECT *,
  (SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(categories.id, books.multiple_category_id) > 0) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

我得到的错误是:

Argument data type int is invalid for argument 1 of charindex function.

供引用。 http://sqlfiddle.com/#!3/4ed19/3

有没有人有什么想法？提前致谢!

Answer 1

使用这个查询。我刚刚将 categories.id 更改为 CAST(categories.id AS VARCHAR)。因为 id 在 categories 表中是 integer。

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(CAST(categories.id AS VARCHAR), books.multiple_category_id) > 0) AS book_count
FROM categories,
     books
WHERE categories.parent_id=0
  AND categories.status=1 ;