如何按降序查找数据 [英] How to find data in descending order

问题描述

我的桌子上有一些记录。该表由Lakhs of Loan Numbers组成。每个贷款号码包含12到60个分期付款。



我的查询是查找具有分期付款金额的贷款编号。



每个贷款都有不同的分期付款方式。一些贷款的所有分期付款金额相同。一些贷款金额高于前一金额（即）分期金额应按升序排列。每个分期付款的部分贷款金额应有所不同。如何找到每个分期付款金额应减少的贷款。



例如



贷款InsNo InsAmount

A 1 500

A 2 500

B 1 700

B 2 400

B 3 800

C 1 400

C 2 300

C 3 200



我要求的输出应该是C



在上面的例子中，贷款A有两个分期付款，两个分期付款都有相同的金额500.

对于贷款C，它有三个分期付款，每个金额各不相同，还有下降顺序400,300,200 ..在我的整个表中我需要C类贷款。

Im having table with crores of records. The table consists of Lakhs of Loan Numbers. Each Loan Number consists of 12 to 60 Instalments.

My query is to find the Loan Number which has the Instalment Amount in descending order.

Each Loans having different patterns of Instalment. Some Loans have same amount for all instalments. Some loans having higher amount then the previous one (ie)Instalment Amount should be in ascending order. Some loans amount should be differs for each Instalment. How to find the loan where the amount should be decreased for each Instalment.

For Example

Loan InsNo InsAmount
A 1 500
A 2 500
B 1 700
B 2 400
B 3 800
C 1 400
C 2 300
C 3 200

My required output should be C

In above example loan A has two Instalment and both Instalment have same Amount 500.
For loan C it has three Instalment and each amount varied and also in desending order 400,300,200.. In my whole table i need C type of loans.

推荐答案

如果你不介意我的非常生锈的 SQL（而且我不得不使用SQLite，所以没有SQL Server的好东西，在这里），请关注我：



许多观点：

If you don't mind about my very rusty SQL (moreover I had to use SQLite so no SQL Server goodies, here), please follow me:

The many views:

create view  o  as select *  from dat order by Loan, InsAmount desc;
create view o1  as select *, (select count(*)+1  from o as b where a.Loan=b.Loan and a.InsAmount <b.insamount)>select * from o1;
select "----------------------------";
create view o2 as select distinct(Loan) from o1 where InsNo<>RowNo;
select * from o2;
select "----------------------------";
select distinct(Loan) from dat where Loan not in (select Loan from o2);

输出：

The output:

A|1|500|1
A|2|500|1
B|3|800|1
B|1|700|2
B|2|400|3
C|1|400|1
C|2|300|2
C|3|200|3
----------------------------
A
B
----------------------------
C

正如您可能看到的最后一个查询给出了想要的结果。 />

• 查看 o1 提供严格按<分配的新字段 RowNo code> InsAmount 具有相同贷款的行
• 查看o2报告存在<$ c的贷款$ c> InsNo - RowNo 不匹配。
• 最后一次选择采用 o2的补码


As you might see the last query gives the wanted result.


• View o1 provides the new field RowNo that strictly orders by InsAmount rows having the same Loan
• View o2 reports the Loans where there is a InsNo-RowNo mismatch.
• The last select takes the complement of o2.




嗨
我认为你需要按降序排列数据请尝试此查询



Hi I think u need datas in descending order pls try this query


SELECT Loan, SUM(InsAmount) AS aaa FROM tttt GROUP BY Loan ORDER BY aaa DESC 'This is desc if u need asc simply change desc to asc or remove desc



问候，

Aravind


Regards,
Aravind




select distinct t1.loan from tablename t1 where
(
(select top 1 t2.insno from tablename t2
where t2.loan = t1.loan order by t2.insamount desc
)=1
)




这篇关于如何按降序查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！