如何优化包含子查询的MySQL查询？

发布于 2024-08-30 02:18:05 字数 1111 浏览 9 评论 0原文

我有两个表，House 和 Person。对于 House 中的任意行，Person 中可以有 0 个、1 个或多个对应行。但是，在这些人中，最多有一个人的状态为“活动”，其他人的状态都为“已取消”。

例如

SELECT * FROM House LEFT JOIN Person ON House.ID = Person.HouseID

House.ID | Person.ID | Person.Status
       1 |         1 |     CANCELLED
       1 |         2 |     CANCELLED
       1 |         3 |        ACTIVE
       2 |         1 |        ACTIVE
       3 |      NULL |          NULL
       4 |         4 |     CANCELLED

我想过滤掉取消的行，并得到类似这样的结果：

House.ID | Person.ID | Person.Status
       1 |         3 |        ACTIVE
       2 |         1 |        ACTIVE
       3 |      NULL |          NULL
       4 |      NULL |          NULL

我已经通过以下子 select: 实现了这一点

SELECT *
FROM House
LEFT JOIN 
(
    SELECT *
    FROM Person
    WHERE Person.Status != "CANCELLED"
) Person
ON House.ID = Person.HouseID

。 ..这有效，但破坏了所有索引。有没有更好的解决方案？

我正在使用 MySQL 并且所有相关列都已建立索引。 EXPLAIN 未列出 possible_keys 中的任何内容。

谢谢。

原文

I have two tables, House and Person. For any row in House, there can be 0, 1 or many corresponding rows in Person. But, of those people, a maximum of one will have a status of "ACTIVE", the others will all have a status of "CANCELLED".

e.g.

SELECT * FROM House LEFT JOIN Person ON House.ID = Person.HouseID

House.ID | Person.ID | Person.Status
       1 |         1 |     CANCELLED
       1 |         2 |     CANCELLED
       1 |         3 |        ACTIVE
       2 |         1 |        ACTIVE
       3 |      NULL |          NULL
       4 |         4 |     CANCELLED

I want to filter out the cancelled rows, and get something like this:

House.ID | Person.ID | Person.Status
       1 |         3 |        ACTIVE
       2 |         1 |        ACTIVE
       3 |      NULL |          NULL
       4 |      NULL |          NULL

I've achieved this with the following sub select:

SELECT *
FROM House
LEFT JOIN 
(
    SELECT *
    FROM Person
    WHERE Person.Status != "CANCELLED"
) Person
ON House.ID = Person.HouseID

...which works, but breaks all the indexes. Is there a better solution that doesn't?

I'm using MySQL and all relevant columns are indexed. EXPLAIN lists nothing in possible_keys.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

悲欢浪云 2024-09-06 02:18:05

怎么样：

SELECT *
FROM House
LEFT JOIN Person
ON House.ID = Person.HouseID 
AND Person.Status != "CANCELLED"

How about:

SELECT *
FROM House
LEFT JOIN Person
ON House.ID = Person.HouseID 
AND Person.Status != "CANCELLED"

回复收藏 0 原文

寄风 2024-09-06 02:18:05

您可以控制数据库结构吗？如果是这样，我认为您可以通过从 Person 表中删除 Status 列，并将 ActivePersonID 列添加到 House 表中来更好地表示数据。通过这种方式，您可以从“人员”中删除所有冗余的“取消”值，并消除应用程序或存储过程代码，以确保每个家庭只有一个人处于活动状态。

此外，您还可以将您的查询表示为

 SELECT * FROM House LEFT JOIN Person ON House.ActivePersonID = Person.ID

Do you have control of the database structure? If so, I think you could better represent your data by removing the column Status from the Person table and instead adding a column ActivePersonID to the House table. This way you remove all the redundant CANCELLED values from Person and eliminate application or stored procedure code to ensure only one person per household is active.

In addition, you could then represent your query as

 SELECT * FROM House LEFT JOIN Person ON House.ActivePersonID = Person.ID

回复收藏 0 原文

聚集的泪 2024-09-06 02:18:05

使用：

   SELECT * 
     FROM HOUSE h 
LEFT JOIN PERSON p ON p.houseid = h.id
                  AND p.status = 'ACTIVE'

Use:

   SELECT * 
     FROM HOUSE h 
LEFT JOIN PERSON p ON p.houseid = h.id
                  AND p.status = 'ACTIVE'

回复收藏 0 原文

挽心 2024-09-06 02:18:05

这是在 SQL Server 中，但逻辑似乎有效，与上面的 Chris 相呼应：

declare @house table
(
    houseid int
)

declare @person table
(
    personid int,
    houseid int,
    personstatus varchar(20)
)

insert into @house (houseid) VALUES (1)
insert into @house (houseid) VALUES (2)
insert into @house (houseid) VALUES (3)
insert into @house (houseid) VALUES (4)

insert into @person (personid, houseid, personstatus) VALUES (1, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (2, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (3, 1, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (1, 2, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (4, 4, 'CANCELLED')

select * from @house
select * from @person

select *
from @house h LEFT OUTER JOIN @person p ON h.houseid = p.houseid 
    AND p.personstatus <> 'CANCELLED'

This is in SQL Server, but the logic seems to work, echoing Chris above:

declare @house table
(
    houseid int
)

declare @person table
(
    personid int,
    houseid int,
    personstatus varchar(20)
)

insert into @house (houseid) VALUES (1)
insert into @house (houseid) VALUES (2)
insert into @house (houseid) VALUES (3)
insert into @house (houseid) VALUES (4)

insert into @person (personid, houseid, personstatus) VALUES (1, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (2, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (3, 1, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (1, 2, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (4, 4, 'CANCELLED')

select * from @house
select * from @person

select *
from @house h LEFT OUTER JOIN @person p ON h.houseid = p.houseid 
    AND p.personstatus <> 'CANCELLED'

回复收藏 0 原文

~没有更多了~