我试图将where子句中有子查询的update语句更改为join语句,以提高性能。到示例表/数据的DDL
CREATE TABLE [dbo].[Table1](
[pre_plan_id] [smallint] NULL,
[pre_type_id] [smallint] NULL,
[associate_id] [smallint] NOT NULL,
[deleted] [bit] NOT NULL
)
INSERT INTO Table1
VALUES
(NULL, NULL, -32768, 0),
(1, NULL, 2, 1),
(1, NULL, 3, 0),
(NULL, NULL, 3109, 0),
(1, NULL, 3109, 1)
CREATE TABLE [dbo].[Table2](
[type_id] [smallint] NOT NULL,
[plan_id] [smallint] NOT NULL,
[associate_id] [smallint] NOT NULL,
[time_in] [smalldatetime] NOT NULL
)
INSERT INTO Table2
VALUES
(390, 31, 3109, '2009-09-02'),
(304, 32, 3109, '2010-02-05'),
(388, 31, 3109, '2010-09-24') 使用子查询的查询:
SELECT pre_plan_id, pre_type_id FROM Table1 WHERE pre_plan_id =1
AND associate_id NOT IN
(SELECT TOP 2 associate_id
FROM Table2 WHERE time_in= '2010-09-24 00:00:00' group by associate_id order by count(*) desc) 我尝试将其转换为JOIN
SELECT pre_plan_id
FROM (SELECT pre_plan_id, pre_type_id, rn
FROM Table1 a
LEFT JOIN
( select associate_id, Row_number() over (partition by associate_id order by count(*) desc ) rn
FROM Table2
WHERE time_in= '2010-09-24 00:00:00' Group by associate_id) b
ON a.associate_id = b.associate_id where b.rn <> 1) a
where pre_plan_id = 1 但是,这不显示任何内容,而我期望的是两行;这是因为b.rn <> 1,当它是b.rn <> 1时,我期望它会显示空值。
对此有什么解释吗?任何关于优化查询的更好方法的指南都是非常感谢的。
谢谢。
发布于 2019-10-05 02:58:06
您可以只将子查询移到FROM子句并使用LEFT JOIN
SELECT t1.pre_plan_id, t1.pre_type_id
FROM Table1 t1 LEFT JOIN
(SELECT TOP 2 t2.associate_id
FROM Table2
WHERE t2.time_in= '2010-09-24 00:00:00'
GROUP BY associate_id
ORDER BY COUNT(*) DESC
) t2
ON t2.associate_id = t1.associate_id
WHERE t1.pre_plan_id = 1 AND t2.associate_id IS NULL;https://stackoverflow.com/questions/58241758
复制相似问题