查询中的问题在哪里？帮助我，比X [英] where is problem in query ? help me with this, thanX

问题描述

LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
	12345-1234567-8	        2	2013-03-04	648700
	12345-1234567-8	        3	2013-04-04	648700
	12345-1234567-8	        4	2013-05-04	648700

但我想要

but i want as

LOAN_ID    Customer_CNIC      Installment_no  Date        Amount
1234-55    12345-1234567-8          1         2013-02-04  648700
                                    2         2013-03-04  648700
                                    3         2013-04-04  648700
                                    4         2013-05-04  648700

这里是查询

here is query

DECLARE @MinInstallments VARCHAR(100)

SELECT @MinInstallments = MIN(Installment_No)
            FROM Installments WHERE Loan_id IN 
            (SELECT Loan_id FROM Customer WHERE Customer_CNIC = ''12345-1234567-8'');
             Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '''' END AS LOAN_ID,c.Customer_CNIC, 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 = ''12345-1234567-8'' order by l.Loan_id,i.Installment_no;

推荐答案

它应该有效，但它没有优化：

It should works, but it''s not optimized:

DECLARE @MinInstallments VARCHAR(100)
DECLARE @CustCNIC VARCHAR(100)
 
SET @CustCNIC = '12345-1234567-8'

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;

在我看来，永远不要使用 CASE ... WHEN ... END 使输出查询更多' '可读''或''可视化''数据。有很多工具可以做到，例如： ReportViewer [ ^ ] 。最终结果集应如下所示：

In my opinion, never use CASE ... WHEN ... END to make output query more ''readible'' or for ''visualize'' data. There are many tools to do it, for example: ReportViewer[^]. Final resultset should looks like:

LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
1234-55 12345-1234567-8	        2	2013-03-04	648700
1234-55 12345-1234567-8	        3	2013-04-04	648700
1234-55 12345-1234567-8	        4	2013-05-04	648700

它完全是核心！您的查询没有任何问题。

and it''s totally corect! There is nothing wrong with your query.

Quote：

选择CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE''''结束为LOAN_ID

Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '''' END AS LOAN_ID

可能（我不是专家）你必须在 Customer_CNIC 字段上复制上述逻辑。即替换

Probably (I am not an expert) you have to replicate the above logic on Customer_CNIC field. Namely replace

c.Customer_CNIC

with

with

Select CASE i.Installment_no WHEN @MinInstallments THEN c.Customer_CNIC ELSE '' END AS CUSTOMER_CNIC

这篇关于查询中的问题在哪里？帮助我，比X的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！