MySQL查询“多行”

Hi I have a mysql query below

嗨，我在下面有一个mysql查询

SELECT `company`,`type`,`code` FROM `location` WHERE (
SELECT code FROM meter WHERE `meter`.`location_code`=location.code AND meter.code NOT IN (
SELECT meter_code FROM reading
))

I receive an error saying that more than one row is returned

我收到一条错误消息，说明返回了多行

Can I ask why/have guidance

我可以问为什么/有指导

Other questions haven't really helped me to be honest

其他问题并没有真正帮助我说实话

2 个解决方案

#1

Since you don't need data from meter or meter code

由于您不需要来自仪表或仪表代码的数据

SELECT `company`,`type`,`code` 
FROM `location` 
WHERE EXISTS (SELECT * 
              FROM meter 
              LEFT JOIN Reading R
                on meter.code = R.meter_Code
              WHERE `meter`.`location_code`=location.code 
                and R.meter_Code is null
             )

or to keep with the theme of using exists (this avoids performance impact of distinct and avoids the joins; but a not exists can be slow.

或者保持使用存在的主题（这避免了不同的性能影响并避免了连接;但是不存在可能会很慢。

SELECT `company`,`type`,`code` 
FROM location l 
WHERE EXISTS (SELECT * 
              FROM meter m
              WHERE m.location_code=l.code 
                and not exists (SELECT * 
                                FROM READING r
                                WHERE R.meter_Code = m.Code

             )

This is how it could be done with joins, but the distinct and joins seem like they could be costly. distinct is necessary as I assume a location may have many meters or vice versa or a meter may have many readings which cause the data to be multiplied thus distinct; but at a cost.

这是如何通过连接完成的，但是不同的连接似乎可能代价高昂。因为我假设一个位置可能有很多米，反之亦然，或者一个仪表可能有很多读数导致数据成倍增加，因此是必要的;但需要付出代价。

SELECT DISTINCT l.company,l.type,l.code
FROM location l
INNER JOIN METER M
 on l.code = m.location_Code
LEFT JOIN reading R
 on R.Meter_Code = m.Code
WHERE r.meter_Code is null

Would need to test each to find what performance best suits your need. record count in each table indexes and data distribution could all change the performance of each of these. I'm partial to the last one, then first one from a maintenance standpoint but it could be the worst in performance.

需要测试每个以找到最适合您需求的性能。每个表中的记录计数索引和数据分布都可以改变每个表的性能。我偏向最后一个，然后从维护的角度来看第一个，但它可能是性能最差的。

#2

I think you need to use the EXISTS operator:

我认为你需要使用EXISTS运算符：

SELECT `company`,`type`,`code` 
FROM `location` 
WHERE EXISTS (SELECT code 
              FROM meter 
              WHERE `meter`.`location_code` = location.code AND 
                     meter.code NOT IN (SELECT meter_code FROM reading))

#1