我如何...获取不同列名称的顶部 [英] How do I...get the top of distinct column name

问题描述

with cte as(
	select 
		status_share_agro,
		jewel_loancode,
		user_code,name,
		lend_date,contact_no,
		case when status_share_agro=1 then 'Share Customer' when status_share_agro=2 then 'agro Customer' end as Customer ,
		Sum(jewel_lendingamount) as jewel_lendingamount
	From 
		tbl_jewel_lendingentry  
	group by 
		jewel_loancode,
		user_code,
		name,
		contact_no,
		status_share_agro,
		lend_date)
,cte2 as(
	select 
		jewel_loancode, 
		balanceamount,
		jewel_repay_id  
	From 
		tbl_jewel_loanrepayment  
	group by 
		jewel_loancode,balanceamount,
		jewel_repay_id  )
select 
	a.status_share_agro,
	max(b.jewel_repay_id) as jewel_repay_id ,
	a.jewel_loancode,
	convert(varchar(100),a.lend_date,103) as lend_date,
	a.user_code,
	a.name,
	a.contact_no,
	a.customer,
	a.jewel_lendingamount as lending_amount,
	isnull(cast(b.balanceamount as nvarchar(15)),'Not Started') as balanceamount   
From 
	cte as a 
	left join cte2 as b on a.jewel_loancode=b.jewel_loancode 
where  1=1 
group by 
	a.jewel_loancode,
	a.jewel_lendingamount,
	a.user_code,
	a.name,
	a.contact_no,
	a.customer,
	a.lend_date,
	b.balanceamount,
	a.status_share_agro

结果是

And The Result is

status_share_agro	jewel_repay_id	jewel_loancode	lend_date	user_code	name	contact_no	customer	lending_amount	balanceamount
1	2	J10001	09/12/2015	M1001	Mr.Test	919952189141	Share Customer	1000	400
1	1	J10001	09/12/2015	M1001	Mr.Test	919952189141	Share Customer	1000	1000
1	NULL	J10002	09/12/2015	M1001	Mr.Test	919952189141	Share Customer	2000	Not Started
2	NULL	J10003	09/12/2015	BNL500001	EEEEE	978978988	agro Customer	2000	Not Started
1	4	J10004	09/12/2015	M1001	Mr.Test	919952189141	Share Customer	2000	-0.75
1	3	J10004	09/12/2015	M1001	Mr.Test	919952189141	Share Customer	2000	1500
1	NULL	J10005	11/12/2015	M1001	Mr.Test	919952189141	Share Customer	90000	Not Started

我需要的是排在顶部的行在宝石贷款代码中





j1001 top 1 repayid

j1002 ,,,,,,,, ,,,,

j1003 ,,,,,,,,,,,,,,

My need is the row which is top in the jewel loan code


j1001 top 1 repayid
j1002 ,,,,,,,,,,,,
j1003 ,,,,,,,,,,,,,,

推荐答案

tk1为

（选择row_number（）over（由jewel_repay_id desc按jewel_loancode顺序划分）为row_id，你需要的列来自（

整个查询

）as t1

）从tk1中选择*其中row_id = 1



u将只获得不同的列

with tk1 as
(select row_number() over(partition by jewel_loancode order by jewel_repay_id desc) as row_id,yourrequired columns from (
entire your query
) as t1
)select * from tk1 where row_id=1

u will get only distinct columns

这篇关于我如何...获取不同列名称的顶部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！