一对一的SQL查询，性能良好

有人可以帮我吗？我试图写一个SQL查询，并在2-3天遇到麻烦。让我首先定义问题。

我有2个表

Payment_Schedule_Master

[payment_schedule_master_id] [int]不是null，主键    [fpi_id] [varchar]（9）不为空，    [delete_flag] [char]（1）不是null，    [create_by] [varchar]（30）不是null，    [create_date] [dateTime]不是null，    [modify_by] [varchar]（30）不是null，    [modify_date] [dateTime]不是null

Payment_Schedule_Detail

[payment_schedule_detail_id] [int]身份（1,1）不是null，主键    [payment_schedule_master_id] [int]不是null，主钥匙的主键    [pay_ year] [int]不是null，    [pay_month] [int]不零，    [实际] [钱]无效，    [预测] [钱]无效，    [delete_flag] [char]（1）不是null，    [create_by] [varchar]（30）不是null，    [create_date] [dateTime]不是null，    [modify_by] [varchar]（30）不是null，    [modify_date] [dateTime]不是null

两者之间存在一对多关系：Master有一个条目，detail有很多。Payment_Schedule_Detail具有id，外键，实际，预测和许多列。实际和预测中将具有数值。

问题：
我想获得具有Actual和ForeCast等于0的Payment_Schedule_Master行。

我的查询：

我尝试了此查询

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualSum, t.ForecastSum
from 
    (Select 
        SUM(Actual) As ActualSum, 
        SUM (forecast) AS ForecastSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
where  
    t.ActualSum = t.ForecastSum
and t.ActualSum = 0

此查询的问题是，如果Actual在1月1月有200个，而12月的-200也将选择该标题，因为SUM (Actual)将是0，这是错误的。

我不确定如何修改查询，即它只能获得具有实际0和预测0的标题。

测试：
而且，如果有人让我知道如何测试该方法？

更新：尝试了此查询，但需要8秒。

Select 
    t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
    t.ActualSum, t.ForecastSum, psd.ACTUAL, psd.FORECAST
from 
    (Select 
         SUM(Actual) As ActualSum, SUM (forecast) AS ForecastSum, 
         PAYMENT_SCHEDULE_MASTER_ID
     from 
          [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
          PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
     dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
Inner Join 
     [dbo].[PAYMENT_SCHEDULE_DETAIL] psd on psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
 where  
     t.ActualSum = t.ForecastSum
     and t.ActualSum = 0
     and psd.ACTUAL = 0 
order by 
     psm.FPI_ID

数据和输出：

psm_id  Actual      ForeCast  [other columns]
900     10000.00    0.00
900     -10000.00   0.00
900     0.00        0.00
912     0.00        0.00
912     0.00        0.00
912     0.00        0.00

psm_id = payment_schedule_master_id

payment_schedule_master_id 900实际的总和为0，结果不应出现在结果上。但是912将出现在结果中，因为所有记录均为0。我希望这会有所帮助。

怎么样 -

select * from Payment_Schedule_Master
where NOT EXISTS
 (
     SELECT  null FROM  Payment_Schedule_Detail 
        where (forecast != 0 or actual != 0) and 
        Payment_Schedule_Master.id = PAYMENT_SCHEDULE_MASTER_ID
 )

注意，这也将找到没有任何相关payment_schedule_details行的payment_schedule_master。

要测试您可以使用SQL小提琴之类的东西。

尝试这个

我尝试以下SQL

select *
from Payment_Schedule_Master mast
where ((select sum(actual) from Payment_Schedule_Detail where main = mast.id) = 0)
and ((select sum(forecast) from Payment_Schedule_Detail where main = mast.id) = 0)

这更简单，我认为它使您想要的是

Select 
    t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
    t.ActualSum, t.ForecastSum, psd.ACTUAL, psd.FORECAST
from 
    (Select t1.ActualSum,t1.ForecastSum,t1.PAYMENT_SCHEDULE_MASTER_ID
    from
    (Select 
         SUM(Actual) As ActualSum, SUM (forecast) AS ForecastSum, PAYMENT_SCHEDULE_MASTER_ID
     from 
          [CILS].[dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
          PAYMENT_SCHEDULE_MASTER_ID) t1
          where  
     t1.ActualSum = 0 and t1.ForecastSum=0)t    
Inner Join 
     [Cils].dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
Inner Join 
     [CILS].[dbo].[PAYMENT_SCHEDULE_DETAIL] psd on psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
order by 
     psm.FPI_ID

您的原始查询非常接近您的需求。您正在寻找实际和预测始终为零的案例。好吧，如果它们总是0，那么绝对值的总和为0。因此，请尝试以下操作：

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualAbsSum, t.ForecastAbsSum
from 
    (Select 
        SUM(abs(Actual)) As ActualAbsSum, 
        SUM(abs(forecast)) AS ForecastAbsSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
where  
    t.ForecastAbsSum = 0 and t.ActualAbsSum = 0

您是否想在以下方式获取任何示例，例如：

Select psm.*
from dbo.PAYMENT_SCHEDULE_MASTER psm 
where psm.PAYMENT_SCHEDULE_MASTER_ID in (select PAYMENT_SCHEDULE_MASTER_ID
                                         from [PAYMENT_SCHEDULE_DETAIL]
                                         where actual = 0 and forecast = 0
                                        )

这使用in而不是join只是为了清晰（两者都在SQL Server Engine中实现了相同的说法）。

在我看来，您需要在总和之前使用'实际'和'tocast'列的位置：

：

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualSum, t.ForecastSum
from 
    (Select 
        SUM(Actual) As ActualSum, 
        SUM (forecast) AS ForecastSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     where Actual=0 and forecast=0
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID

说，如果性能是一个问题，则不需要总结实际和预测，因为您正在总结零。然后我们可以简单地做：

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 0 as ActualSum, 0 as ForecastSum
from
    (Select distinct
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        dbo.PAYMENT_SCHEDULE_DETAIL
     where Actual=0 and forecast=0 ) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm
On psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID

我对性能优化不太聪明 - 如果您的子查询产生了许多行，那么在加入之前，在加入之前选择了不同的行，可能会更快。但是您可以尝试一下：

Select distinct
   psd.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 0 as ActualSum, 0 as ForecastSum
from
   dbo.PAYMENT_SCHEDULE_DETAIL psd
Inner Join 
   dbo.PAYMENT_SCHEDULE_MASTER psm
On
   psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
Where
   Actual=0 and forecast=0

这更简单，但是在过滤不同的行之前，它与行连接在一起时可能会较慢。而且，除非您做很多数据写入，否则不要忘记索引您的fk。

我相信这就是您要寻找的：

SELECT M.*
FROM Payment_Schedule_Detail D
JOIN Payment_Schedule_Master M
ON M.PAYMENT_SCHEDULE_MASTER_ID = D.PAYMENT_SCHEDULE_MASTER_ID
GROUP BY D.PAYMENT_SCHEDULE_MASTER_ID
HAVING SUM(D.Actual) = 0 AND SUM(D.Forecast) = 0

或以下：

SELECT M.*
FROM (SELECT PAYMENT_SCHEDULE_MASTER_ID,
             SUM(Actual) ActualSum, SUM(Forecast) ForecastSum
      FROM Payment_Schedule_Detail
      GROUP BY PAYMENT_SCHEDULE_MASTER_ID) D
JOIN Payment_Schedule_Master M
ON M.PAYMENT_SCHEDULE_MASTER_ID = D.PAYMENT_SCHEDULE_MASTER_ID
WHERE ActualSum = 0 AND ForecastSum = 0

不确定哪个在性能方面更好。我认为第二个会创建一个临时表，因此较慢，但这可能有点取决于优化器。

根据您使用的类型的SQL数据库，外键可能会自动或可能不会自动索引，如果不是，则应索引外键。

SELECT psm_id, ActualSum, ForecastSum FROM
(SELECT 
    PAYMENT_SCHEDULE_MASTER_ID AS psm_id, SUM(psd.Actual) As ActualSum, SUM (psd.forecast) AS ForecastSum
FROM 
    [dbo].[PAYMENT_SCHEDULE_DETAIL] psd, dbo.PAYMENT_SCHEDULE_MASTER psm
GROUP BY PAYMENT_SCHEDULE_MASTER_ID
) ts, dbo.PAYMENT_SCHEDULE_MASTER psm1
WHERE psm1.PAYMENT_SCHEDULE_MASTER_ID = ts.psm_id
AND ts.ActualSum = 0 AND ts.ForecastSum
ORDER BY 
     psm1.FPI_ID

谢谢您的答案，但根本没有工作。

在我的数据库中，有一个我没有看到的特殊情况。是，如果针对特定的payment_schedule_id的实际和预测均为0，则只有一行。

Select PAYMENT_SCHEDULE_MASTER_ID from (
Select COUNT(*)  As IdCount, PAYMENT_SCHEDULE_MASTER_ID, ACTUAL , FORECAST
from PAYMENT_SCHEDULE_DETAIL
group by PAYMENT_SCHEDULE_MASTER_ID, ACTUAL, FORECAST) t
where t.IdCount = 1 
and t.ACTUAL = 0.00
and t.FORECAST = 0.00

上面是解决方案。

相关内容

最新更新

热门标签：