如何通过加入选择max id记录 [英] how to select max id record through joining

问题描述

我有一个程序........

  alter   PROCEDURE  [dbo] .sp_GatherCleintInfofoDeactivation 
（
 < span class =code-sdkkeyword> @ Center_Id   int ，
  @ Company_U_Id   int ，
  @ CRT_Result   int ，
  @ Loan_Proposal_Status   int  
）
 
  AS  
  BEGIN  
 
  SET   NOCOUNT   ON ; 
 
 
 
  SELECT  TargetInfo.m_name，TargetInfo.Client_Id，ClientCRTReportInfo.Client_Id  as  clientID，ClientCRTReportInfo.CRTId，ClientCRTReportInfo.SubGroup_Id，TempPaymentShceduleInfo.TempId  into  #TempAllWeights 
  FROM  CenterInfo  INNER   JOIN  
 ClientCRTReportInfo  ON  CenterInfo.Center_Id = ClientCRTReportInfo.Center_Id  INNER   JOIN  
 TargetInfo < span class =code-keyword> ON  CenterInfo.Center_Id = TargetInfo.Center_Id  AND  ClientCRTReportInfo.Client_Id = TargetInfo.Client_Id_Auto 
 内部 加入 TempPaymentShceduleInfo   TargetInfo.Client_Id_Auto = TempPaymentShceduleInfo.Client_Id 
  WHERE  （CenterInfo.Center_Id =  @ Center_Id ） AND （ClientCRTReportInfo.Company_U_Id =  @Company_U_Id ）和 TempPaymentShceduleInfo.TempId =     （选择 MAX（TempId）来自 TempPaymentShceduleInfo） 
 
 选择 * 来自 #TempAllWeights 
 
 
 
 
 
 
 
  END  
 返回 

在这里我销售的行，

（即TempPaymentShceduleInfo.TempId =（从TempPaymentShceduleInfo中选择MAX（TempId）））

我想为所有客户选择TempPaymentShceduleInfo中最大temp_id的记录，tempId是TempPaymentShceduleInfo的主键。我怎么能解决这个问题？？请提前...先谢谢..

解决方案

实际上我此时无法提供详细信息，但我可以我详细解释一下我想要的......



i有三张桌子.....

1。 ClientDetailTable（clientId作为主键）

2. PaymentDetailTable（paymentId作为主键）（clientId作为外键）

3. AddressDetailTable（AddId作为主键）（clientId as外键）





现在我想选择ClientDetailTable中存在的所有客户记录及其来自
$的地址b $ b

AddressDetailTable。我没有问题（每个客户端的记录在AddressDetailTable和ClientDetailTable中只存在一次），但是当我尝试从PaymentDetailTable加入corressponds记录时，我遇到了问题。实际上在PaymentDetailTable记录中存在多次（paymentId设置为身份（1,1））现在我想从PaymentDetailTable中选择记录，该记录对该客户具有最大的paymentId....

并且通过这种方式我想加入这三张桌子....请给出建议兄弟......



i希望你明白这个问题，等待响应......

SELECT

t。*

FROM

TableX AS t $ / $
加入

（选择DISTINCT

title ---你想要分组的东西

来自TableX

）AS dt

ON t.PK = ---表的主键

（SELECT tt.PK

来自TableX AS tt

WHERE tt.title = dt.title

ORDER BY id ASC ---（或DESC）你要订购什么？ />
限制1

）



Kishor Makwana

软件工程师

Insight Softech

www.insightsoftech.com

  WITH  T  AS （选择 cd。*，ad。*，pd。*， ROW_NUMBER（） over （ partition   by  pd .clientID 订单 按 pd.paymentID  desc  ）rowNumber 
 来自 ClientDetailTable cd  inner   join  AddressDetailTable ad  on  cd.clientID = ad.clientID 
  inner   join  PaymentDetailTable pd  on  cd.clientID = pd.clientID）
 
  select  * 来自 T 其中 rowNumber =  1  

在此查询中，您只需要将表名替换为您的表名。

如果您从此查询中获得正确的解决方案，请与我们联系。

i have a procedure ........

alter PROCEDURE [dbo].sp_GatherCleintInfofoDeactivation  
(  
@Center_Id int,  
@Company_U_Id int,  
@CRT_Result int,  
@Loan_Proposal_Status int  
)  

AS  
BEGIN  

 SET NOCOUNT ON;  



SELECT     TargetInfo.m_name,TargetInfo.Client_Id, ClientCRTReportInfo.Client_Id as clientID,ClientCRTReportInfo.CRTId,ClientCRTReportInfo.SubGroup_Id,TempPaymentShceduleInfo.TempId   into #TempAllWeights
FROM         CenterInfo INNER JOIN  
                      ClientCRTReportInfo ON CenterInfo.Center_Id = ClientCRTReportInfo.Center_Id INNER JOIN  
                      TargetInfo ON CenterInfo.Center_Id = TargetInfo.Center_Id AND ClientCRTReportInfo.Client_Id = TargetInfo.Client_Id_Auto  
                      inner join TempPaymentShceduleInfo  on TargetInfo.Client_Id_Auto =TempPaymentShceduleInfo.Client_Id
WHERE     (CenterInfo.Center_Id = @Center_Id) AND (ClientCRTReportInfo.Company_U_Id = @Company_U_Id) and TempPaymentShceduleInfo.TempId=(select MAX(TempId) from TempPaymentShceduleInfo)

select * from #TempAllWeights






                     
END  
RETURN  

here the line where i markeed ,
(i.e. TempPaymentShceduleInfo.TempId=(select MAX(TempId) from TempPaymentShceduleInfo))
i want to select the record with maximum temp_id in "TempPaymentShceduleInfo" for all clients individual, tempId is the primary key of "TempPaymentShceduleInfo". how can i solve this ...?pls rply ...thanks in advance..

解决方案

actually i am unable to give detail at this time , but i can explain u this in detail what i want ...... ..

i have three tables .....
1. ClientDetailTable(clientId as primary key )
2. PaymentDetailTable (paymentId as primary key ) (clientId as foreign key )
3. AddressDetailTable (AddId as primary key) (clientId as foreign key)


now i want to select all clients record which exist in "ClientDetailTable" and their addresses from

"AddressDetailTable " . i have no problem in this (record for each client exist only once in both "AddressDetailTable " and "ClientDetailTable" ) , but when i am trying to join corressponds record from "PaymentDetailTable " i have arise a problem . Actually in "PaymentDetailTable " record exist more than once (paymentId is set identity(1,1)) now i want to select record from "PaymentDetailTable " which has maximum "paymentId " for that client ....
and in this way i want to join these three tables ....pls give suggestion bro......

i hope u understand the problem , waiting for response ......

SELECT
t.*
FROM
TableX AS t
JOIN
( SELECT DISTINCT
title --- what you want to Group By
FROM TableX
) AS dt
ON t.PK = --- the Primary Key of the table
( SELECT tt.PK
FROM TableX AS tt
WHERE tt.title = dt.title
ORDER BY id ASC --- (or DESC) what you want to Order By
LIMIT 1
)

Kishor Makwana
Software Engineer
Insight Softech
www.insightsoftech.com

WITH T AS (select cd.*,ad.*,pd.*,ROW_NUMBER() over (partition by pd.clientID order by pd.paymentID desc) rowNumber
from ClientDetailTable cd inner join AddressDetailTable ad on cd.clientID = ad.clientID
inner join PaymentDetailTable pd on cd.clientID = pd.clientID)

select * from T where rowNumber = 1

In this query, you just need to replace table name with your table name.
Please let me know if you get the right solution from this query.

这篇关于如何通过加入选择max id记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！