我如何...获取不同列名称的顶部

问题描述

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 ,,,,,,,,,,,,,,

推荐答案

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