SQLSTATE [21000]:违反基数:1241操作数应包含1列. sql查询 [英] SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s). sql query

问题描述

我有两个名为数据"和区域"的表.我想获取具有数据相关记录的区域作为区域记录的项目.

地区

RegionId -- Name -- ParentId

数据

DataId -- RegiondId -- Url

抽样结果:

[
{Name:"a", items:[{...},{...},{...}, ...]},
{Name:"b", items:[{...},{...},{...}, ...]},
]

我使用了此查询

SELECT *,(
    SELECT * 
    FROM data 
    WHERE data.RegionId=regions.RegionId
) AS items 
FROM regions 
WHERE regions.ParentId=1

但是我得到了SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).

重要:区域在其记录之间具有父关系.因此，当我写此条件data.RegionId=regions.Id时，它必须返回其子项与RegionIds匹配的项.

解决方案

选择列表中的子查询必须返回一列和一行(单个值)，但是那里有一个select *，因此会出现错误消息. /p>

您的查询应编写为简单的内部联接，而不是子查询:

SELECT *
FROM regions 
INNER JOIN data ON data.RegionId=regions.RegionId
WHERE regions.ParentId=1

I have two table that named as 'data' and 'regions'. I want to get regions with data related records as item of regions records.

regions

RegionId -- Name -- ParentId

data

DataId -- RegiondId -- Url

Sample result:

[
{Name:"a", items:[{...},{...},{...}, ...]},
{Name:"b", items:[{...},{...},{...}, ...]},
]

I used this query

SELECT *,(
    SELECT * 
    FROM data 
    WHERE data.RegionId=regions.RegionId
) AS items 
FROM regions 
WHERE regions.ParentId=1

But I got SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).

Important: Region has parent relation between its records. So I when write this condition data.RegionId=regions.Id it must return that items has matches RegionIds of it's childs.

解决方案

A subquery in the select list must return one column and 1 row (single value), but you have a select * there, hence the error message.

Your query should be written as a simple inner join instead of a subquery:

SELECT *
FROM regions 
INNER JOIN data ON data.RegionId=regions.RegionId
WHERE regions.ParentId=1

这篇关于SQLSTATE [21000]:违反基数:1241操作数应包含1列. sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！