gpt4 book ai didi

postgresql - 在 PostgreSQL 中搜索子字符串

转载 作者:行者123 更新时间:2023-11-29 14:03:31 25 4
gpt4 key购买 nike

我有一个 POstgreSQL 8.4。我有一个表,我想使用子查询返回的子字符串(字符变化数据类型)在此表的一行(字符变化数据类型)中找到一个字符串:

SELECT  uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE (
SELECT str
FROM test
WHERE str IS NOT NULL)

但是报错

ERROR:  more than one row returned by a subquery used as an expression

test.str 字段中,我在 uchastki.kadnum 66:07 中有类似 66:07:21 01 001 的字符串:21 01 001:27.

如何使用子查询的结果查找子字符串?

更新

表格测试:

CREATE TABLE test
(
id serial NOT NULL,
str character varying(255)
)
WITH (
OIDS=FALSE
);
ALTER TABLE test OWNER TO postgres;

表 uchastki:

CREATE TABLE uchastki
(
fid serial NOT NULL,
the_geom geometry,
id_uch integer,
num_opora character varying,
kod_lep integer,
kadnum character varying,
sq real,
kod_type_opora character varying,
num_f11s integer,
num_opisanie character varying,
CONSTRAINT uchastki_pkey PRIMARY KEY (fid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2)
)
WITH (
OIDS=FALSE
);
ALTER TABLE uchastki OWNER TO postgres;

最佳答案

使用 like any :

SELECT  uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE ANY(
SELECT str
FROM test
WHERE str IS NOT NULL)

或者也许:

SELECT  uchastki.kadnum
FROM uchastki
WHERE kadnum LIKE ANY(
SELECT '%' || str || '%'
FROM test
WHERE str IS NOT NULL)

这是一个不错的功能,您可以使用不同的运算符,例如 = any (select ... ) , 或 <> all (select...) .

关于postgresql - 在 PostgreSQL 中搜索子字符串,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12952581/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com