存储过程复制记录sql server [英] Stored procedure duplicating the records sql server

问题描述

我正在尝试使用MSSQL Server中的存储过程从不同的表中获取。这是代码

I am trying to fetch from different tables using stored procedure in MSSQL Server. here is the code

<pre lang="SQL">SELECT 
    ap.ApplicantName, ap.PlotSerialNo, pl.LocationTitle,
    ptt.PlotTitle,pp.TotalPayment AS TotalActualPayment,ap.concession,
    (pp.TotalPayment-CONVERT(INT, ap.concession)) AS TotalAfterConc, 
    ai.DownPayments,
    SUM(CONVERT(INT, ai.Arrears)+CONVERT(INT, ai.LastPayment)) AS Downpmtlstpmt,
    (CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.InstallmentAmount)), 0) END) AS TotalAmtPaidInstallments,
    (CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.Arrear)), 0) END) AS TotalArrearPaidInstallments
FROM 
    dbo.TblApplicant AS ap 
JOIN 
    dbo.TblApplicantInitialPayments AS ai ON ai.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.TblApplicantInstallments AS aii ON aii.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.PaymentType AS Pt ON pt.PaymentTypeId = ap.PaymentTypeId
INNER JOIN 
    dbo.TblApplicantPlotPayments AS app ON app.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.TblPlotPayments AS pp ON pp.PlotPaymentID = app.PlotPaymentID 
INNER JOIN 
    dbo.TblPlotLocation AS pl ON pl.LocationId = app.LocationId
INNER JOIN 
    dbo.TblPlotType as ptt on ptt.PlotTypeId = app.PlotTypeId
WHERE 
    pt.PaymentTypeId = 1 AND ptt.PlotTypeId = @plotsize
GROUP BY 
    ap.ApplicantName, ap.ApplicantId, ptt.PlotTitle, 
    pl.LocationTitle, aii.Status, 
    ap.PlotSerialNo, pp.TotalPayment, ap.concession,
    ai.DownPayments, ai.Arrears, ap.RegDate, pt.Title
ORDER BY 
    ptt.PlotTitle, pl.LocationTitle</pre>

但它是退货你的重复记录。请告诉我我做错了什么。



这里是输出



http://prntscr.com/adtdu0 [ ^ ]



谢谢



我尝试过：

But it is returning duplicate records. Please tell me what I am doing wrong.

here is the out put

http://prntscr.com/adtdu0[^]

Thanks

What I have tried:

SELECT 
    ap.ApplicantName, ap.PlotSerialNo, pl.LocationTitle,
    ptt.PlotTitle,pp.TotalPayment AS TotalActualPayment,ap.concession,
    (pp.TotalPayment-CONVERT(INT, ap.concession)) AS TotalAfterConc, 
    ai.DownPayments,
    SUM(CONVERT(INT, ai.Arrears)+CONVERT(INT, ai.LastPayment)) AS Downpmtlstpmt,
    (CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.InstallmentAmount)), 0) END) AS TotalAmtPaidInstallments,
    (CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.Arrear)), 0) END) AS TotalArrearPaidInstallments
FROM 
    dbo.TblApplicant AS ap 
JOIN 
    dbo.TblApplicantInitialPayments AS ai ON ai.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.TblApplicantInstallments AS aii ON aii.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.PaymentType AS Pt ON pt.PaymentTypeId = ap.PaymentTypeId
INNER JOIN 
    dbo.TblApplicantPlotPayments AS app ON app.ApplicantId = ap.ApplicantId 
INNER JOIN 
    dbo.TblPlotPayments AS pp ON pp.PlotPaymentID = app.PlotPaymentID 
INNER JOIN 
    dbo.TblPlotLocation AS pl ON pl.LocationId = app.LocationId
INNER JOIN 
    dbo.TblPlotType as ptt on ptt.PlotTypeId = app.PlotTypeId
WHERE 
    pt.PaymentTypeId = 1 AND ptt.PlotTypeId = @plotsize
GROUP BY 
    ap.ApplicantName, ap.ApplicantId, ptt.PlotTitle, 
    pl.LocationTitle, aii.Status, 
    ap.PlotSerialNo, pp.TotalPayment, ap.concession,
    ai.DownPayments, ai.Arrears, ap.RegDate, pt.Title
ORDER BY 
    ptt.PlotTitle, pl.LocationTitle

推荐答案

您的查询给出了正确的记录。

如果你需要独特的记录，那么你必须从你的选择查询中减少列数。

你必须从你的文件中删除这些列（Downpmtlstpmt，TotalAmtPaidInstallments，TotalArrearPaidInstallments）选择查询。

之后只有你才能得到唯一的记录。

your query is giving correct record.
If u need unique records then u have to reduce number of columns from ur select query.
You have to remove these columns (Downpmtlstpmt,TotalAmtPaidInstallments,TotalArrearPaidInstallments ) from ur select query.
After that only u will get the unique record.

这篇关于存储过程复制记录sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！