where-in-select-replace-on-join连接

ar7v8xwq 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(312)

我有一个sql查询：

SELECT
    count(*) as count
FROM
    USERS.Names p1
WHERE
    p1.rowid IN (
            SELECT p2.rowid
            FROM USERS.Names p2
            WHERE
                p2.Id = p1.Id AND p2.status = p1.Status
                AND p2.DateTo = (SELECT max(p3.dateTo)
                                FROM USERS.Names p3
                                WHERE p3.entityid = p1.Id
                                        AND p3.status = p1.Status
                                        AND p3.Code = p2.Code)
  )
  AND p1.Status = 1
  AND p1.Id >= 12
  AND p1.Id < 31308;

с我移除嵌套的 SELECT 并用 JOIN 什么？这个请求已经运行了很长时间

sql mysql

来源：https://stackoverflow.com/questions/50510107/where-in-select-replace-on-join

2条答案

按热度按时间

cfh9epnr1#

试试这个：

SELECT
    count(*) as count
FROM
    USERS.Names p1
INNER JOIN
    SELECT (
        MAX(dateto) MaxDateto
    FROM USERS.Names) p2
        ON p1.dateto = p2.MaxDateto
WHERE
    p1.Status = 1
    AND p1.Id >= 12
    AND p1.Id < 31308;

赞(0）回复(0）举报 2021-06-21

2mbi3lxu2#

以下可能是您要寻找的：

SELECT count(1) count
  FROM Names p3 
            JOIN 
                (
                 SELECT max(p1.dateTo) dateTo_max
                   FROM Names p1 JOIN Names p2 
                     ON ( p2.entityid = p1.Id )  
                  WHERE p1.dateTo = p2.dateto
                    AND p1.Status = 1
                    AND p1.Id between 12 AND 31307) p4 
                     ON ( p3.dateto = p4.dateTo_max );

赞(0）回复(0）举报 2021-06-21

我来回答

where-in-select-replace-on-join连接

2条答案

相关问题

热门标签

最新问答