了解 SQL 中的显式类型转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42269987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Understanding explicit type casts in SQL
提问by Albertus Brand Venter
SELECT *
FROM lookup_type
WHERE type_group = 'enquiry'
AND type_sub_group = 'category'
AND type_attribute IN (SELECT prod_id
FROM product
WHERE prod_id IN (SELECT option_prod_id
FROM product_option
WHERE option_id IN (SELECT link_option_id
FROM product_link
WHERE link_member_id = '146')));
In the query above I would like to select a record in my lookup table. However I get the following error from my postgreSQL client.
在上面的查询中，我想在查找表中选择一条记录。但是，我从我的 postgreSQL 客户端收到以下错误。
ERROR: operator does not exist: character varying = integer
LINE 1: ...uiry' and type_sub_group='category' and type_attr in(SELECT ...
^HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
错误：运算符不存在：字符变化 = 整数
LINE 1: ...uiry' and type_sub_group='category' and type_attr in(SELECT ...
^提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。
The type attris a varcharcolumn and prod_idis a integer column.
的type attr是一varchar列，并prod_id是一个整数列。
What am I doing wrong and how do I fix this? An explanation into how to avoid this in the future would be much appreciated.
我做错了什么，我该如何解决？将来如何避免这种情况的解释将不胜感激。
回答by IMSoP
PostgreSQL is strongly typed- every value has a defined type, and every function and operator is defined to work with particular types.
PostgreSQL 是强类型的——每个值都有一个定义的类型，每个函数和运算符都被定义为使用特定的类型。
There is an operator for comparing two VarCharvalues, and a different operatorfor comparing two Integervalues. Both happen to be spelled =, but they are separate entries in Postgres's system catalogue. Since there is no operator called =which takes a VarCharon one side and an Integeron the other, this explains the "operator does not exist" message.
有一个运算符用于比较两个VarChar值，另一个运算符用于比较两个Integer值。两者都拼写为=，但它们是 Postgres 系统目录中的单独条目。由于没有调用的运算符在一侧=采用 a，而VarChar在另一侧采用 an Integer，这就解释了“运算符不存在”消息。
In practice, the rule of thumb is that you need to make both sides of an =sign be the same type, so that they can be compared. This can be done using the standard SQL CAST(value AS type)function, which converts a value from one type to another.
在实践中，经验法则是您需要使一个=标志的两侧是相同的类型，以便它们可以进行比较。这可以使用标准 SQLCAST(value AS type)函数来完成，该函数将值从一种类型转换为另一种类型。
In this case, you want the results from your inner SELECTto all be VarChars so that they can be fed into the INclause, so you can write:
在这种情况下，您希望来自内部的结果SELECT全部为VarChars，以便将它们输入到IN子句中，因此您可以编写：
... and type_attribute in (SELECT CAST(prod_id as VarChar) ...
An integer can always be cast to a string, because you can always write out a number using digits. If you know a string will only contain digits, you can also cast the other way, e.g. CAST(type_attribute AS int).
整数总是可以转换为字符串，因为你总是可以用数字写出一个数字。如果你知道一个字符串只包含数字，你也可以用另一种方式转换，例如CAST(type_attribute AS int).
回答by Muhammad Qasim
You are checking type_attribute which is a varchar field with an integer i.e prod_id. One thing you can, is to type cast prod_id to varchar in your sub query.
您正在检查 type_attribute，它是一个带有整数的 varchar 字段，即 prod_id。您可以做的一件事是在您的子查询中将 prod_id 类型转换为 varchar。
SELECT Cast(prod_id As varchar)
回答by Georgi Raychev
So you have your query like this:
所以你有这样的查询：
SELECT *
FROM lookup_type
WHERE type_group = 'enquiry'
and type_sub_group='category'
and type_attribute in (
SELECT prod_id
FROM product
WHERE prod_id in (
SELECT option_prod_id
FROM product_option
WHERE option_id in (
SELECT link_option_id
FROM product_link
WHERE link_member_id = '146'
)
)
);
Lets start with the inner query:
让我们从内部查询开始：
SELECT link_option_id
FROM product_link
WHERE link_member_id = '146'
If link_option_idis an integer, but option_idfrom the upper nest is a varchartypes, you will have an issue. Thus you need to castthe result and have it like:
如果link_option_id是一个integer，但从option_id上面的嵌套是一个varchar类型，你会遇到问题。因此，您需要cast结果并使其像：
SELECT cast(link_option_id,varchar)
FROM product_link
WHERE link_member_id = '146'
You get it. Same logic must be applied going up the nests.
你懂了。必须应用相同的逻辑来爬上巢穴。
Here is some more additional info on the CAST function (quick google search): http://www.postgresqltutorial.com/postgresql-cast/
以下是有关 CAST 函数的更多附加信息（谷歌快速搜索）：http: //www.postgresqltutorial.com/postgresql-cast/
回答by Erich Kitzmueller
In PostgreSQL, you can cast using the :: notation, e.g.
在 PostgreSQL 中，您可以使用 :: 符号进行转换，例如
SELECT *
FROM lookup_type
WHERE type_group = 'enquiry'
and type_sub_group='category'
and type_attribute in (SELECT prod_id::varchar
FROM product
WHERE prod_id in (SELECT option_prod_id
FROM product_option
WHERE option_id in (SELECT link_option_id
FROM product_link
WHERE link_member_id = '146')));
