首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >基于表B的标准,表A的SQL返回结果

问基于表B的标准,表A的SQL返回结果
EN

Stack Overflow用户
提问于 2022-01-10 23:07:58
回答 1查看 124关注 0票数 2

我有两个表,它们有一个1到多个关系。假定以下结构:

代码语言:javascript
复制
users             users_metadata
-------------     -------------
id | email        id | user_id | type | score

用户可以拥有许多元数据。users表有100 k行,users_metadata表有300 k行。它可能会增长10倍,所以无论我写什么,都需要对大量的数据是最优的。

我需要编写一个sql语句,它只返回通过元数据表中的几个不同分数条件的用户电子邮件.。

代码语言:javascript
复制
// if type = 1 and if score > 75 then <1 point> else <0 points>
// if type = 2 and if score > 100 then <1 point> else <0 points>
// if type = 3 and if score > 0 then [-10 points] else <0 points>

// there are other types that we want to ignore in the score calculations

如果用户通过了阈值(例如,>= 1点),那么我希望该用户在结果集中,否则我希望忽略该用户。

我尝试过用户一个存储函数/游标,它接受一个user_id并循环元数据来确定点,但是结果的执行非常慢(尽管它确实有效)。

现在我有了这个,执行大约需要1到3秒。

代码语言:javascript
复制
SELECT u.id, u.email,

    (
        SELECT 
            SUM(
                IF(k.type = 1, IF(k.score > 75, 1, 0), 0) + 
                IF(k.type = 2, IF(k.score > 100, 1, 0), 0) +
                IF(k.type = 3, IF(k.score > 0, 1, -10), 0)
            ) 
        FROM user_metadata k WHERE k.user_id = u.id
        
    ) AS total

FROM users u GROUP BY u.id HAVING total IS NOT NULL;

我觉得在10倍时,这个速度会更慢。1到3秒的查询执行时间对于我已经需要的东西来说太慢了。

更理想的方法是什么?

如果我也使用一种类似于PHP的语言,那么运行两个查询(一个是从user_metadata of user_metadata of passing用户获取passing),然后再选择ids列表中的IN更好吗?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-01-10 23:20:59

尝试使用联接而不是关联子查询。

代码语言:javascript
复制
SELECT u.id, u.email, t.total
FROM users AS u
JOIN (
    SELECT user_id, SUM(CASE type
        WHEN 1 THEN score > 75
        WHEN 2 THEN score > 100
        WHEN 3 THEN IF(k.score > 0, 1, -10)
        END) AS total
    FROM user_metadata
    GROUP BY user_id
    HAVING total >= 1
) AS t ON u.id = t.user_id

在子查询中进行分组和筛选会使联接变得更小,这将大大提高性能。

您也不需要在查询中使用GROUP BY u.id,因为这是您正在查询的表的主键;希望MySQL能够优化它。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/70659885

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档