如何检索所有记录，它只是检索最后一个。帮助我。感谢名单 [英] how to retrive all records, it is just retrieving only last one. help me with this. thanx

问题描述

 DECLARE @MinInstallments VARCHAR（100）
 DECLARE @CustCNIC VARCHAR（100）
 
从客户选择@CustCNIC = Customer_CNIC 
 
 SELECT @MinInstallments = MIN（Installment_No）
 FROM分期付款AS i 
 WHERE Loan_id IN 
（SELECT Loan_id FROM Customer WHERE Customer_CNIC = @CustCNIC）; 
 
 SELECT CASE i.Installment_no WHEN @MinInstallments那么i.Loan_id ELSE'''结束为LOAN_ID，
''Customer_CNIC''= CASE当i.Installment_no = @MinInstallMents那么@CustCNIC ELSE''''END，i.Installment_no，i.Date，i.Amount 
来自分期付款i，贷款l，客户c 
 WHERE l.Loan_id = c.Loan_id和c.Loan_id = i。 Loan_id和c.Customer_CNIC = 
 @CustCNIC ORDER BY l.Loan_id，i.Installment_no 

解决方案

试试这个

  DECLARE   @ CustMinLoanInstallments   AS  表（Customer_CNIC  VARCHAR （  100 ），Loan_Id  VARCHAR （ 100 ） ，
 MinInstallments  BIGINT ）
 
  INSERT   INTO   @ CustMinLoanInstallments  
  SELECT  i.Loan_id，C.Customer_CNIC MIN（Installment_No）
  FROM 分期付款 AS  i，Customer  AS  C 
  WHERE  i.Loan_id = C. Loan_id 
 
  SELECT   CASE  i.Installment_no  WHEN  cmli.MinInstallments  THEN  i.Loan_id  ELSE  ' '  END   AS  LOAN_ID，
 '  Customer_CNIC' =  CASE   WHEN  i.Installment_no = cmli.MinInstallments  THEN   @ CustCNIC   ELSE  ' '  END ，i.Installment_no，i。日期，i.Amount 
  FROM 分期付款i ，贷款l，客户c， @ CustMinLoanInstallments  cmli 
  WHERE  l.Loan_id = c.Loan_id 和 c.Loan_id = i.Loan_id 
  AND  cmli.Loan_id = i.Loan_id 和 cmli.Customer_CNIC = c.Customer_CNIC 
  ORDER   BY  l.Loan_id，i.Installment_no 

希望这会有所帮助

--RDBurmon

DECLARE @MinInstallments VARCHAR(100) 
DECLARE @CustCNIC VARCHAR(100) 

Select @CustCNIC =Customer_CNIC from Customer 

SELECT @MinInstallments = MIN(Installment_No) 
FROM Installments AS i 
WHERE Loan_id IN 
(SELECT Loan_id FROM Customer WHERE Customer_CNIC = @CustCNIC);

SELECT CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '''' END AS LOAN_ID, 
''Customer_CNIC'' = CASE WHEN i.Installment_no = @MinInstallMents THEN @CustCNIC ELSE ''''END, i.Installment_no, i.Date, i.Amount 
FROM Installments i, Loan l, Customer c 
WHERE l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id and c.Customer_CNIC=
@CustCNIC ORDER BY l.Loan_id,i.Installment_no

解决方案

Try this

DECLARE @CustMinLoanInstallments AS TABLE(Customer_CNIC VARCHAR(100),Loan_Id VARCHAR(100), 
MinInstallments BIGINT) 

INSERT INTO @CustMinLoanInstallments 
SELECT i.Loan_id , C.Customer_CNIC MIN(Installment_No)
FROM Installments AS i , Customer AS C 
WHERE i.Loan_id =C.Loan_id

SELECT CASE i.Installment_no WHEN cmli.MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID,
'Customer_CNIC' = CASE WHEN i.Installment_no = cmli.MinInstallments THEN @CustCNIC ELSE ''END, i.Installment_no, i.Date, i.Amount
FROM Installments i, Loan l, Customer c ,@CustMinLoanInstallments cmli 
WHERE l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id 
  AND cmli.Loan_id = i.Loan_id and cmli.Customer_CNIC = c.Customer_CNIC
 ORDER BY l.Loan_id,i.Installment_no

Hope this helps
--RDBurmon

这篇关于如何检索所有记录，它只是检索最后一个。帮助我。感谢名单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！