从内部联接查询中的表中获取第二行 [英] Taking the 2nd row from a table in innerjoin query

问题描述

大家好，

我正在一个项目中，客户必须预订财产，然后该财产中才会有协议.为此，这两种付款都是通过现金或支票完成的，因此我将预订付款和协议付款都保留在自己的一张桌子上.并且我已经将预订ID作为我的外键.因此，在获取协议记录时，我同时获得了预订付款明细和协议明细.在这里，我想告诉另一件事，即首先预订"，然后仅插入协议.没有机会先插入协议然后再进行预订，所以我确定协议位于第二行.现在我的问题是在使用innerjoin获取付款的客户详细信息时，我同时获得了两个记录.但是我想要2行付款明细和客户记录.我想要协议"部分，所以同一位客户既完成了预订又完成了协议.因此，我该如何编写内部联接或任何其他查询来获取我的需求数据.


示例:
考虑我有预订表和付款表
在预订时，请考虑将预订ID作为Book123.现在，此预订ID是付款"表中的Forgien键.现在，客户预订了财产，然后将生成付款ID

BookID是主键

客户表

Hello every one ,

I am working on a project where a customer has to book a property then the agreement will be there in that property.For that both payment is done through cash or Cheque so i am maintaining both booking payment and agreement payment in one table its self . and i have given Booking Id as my foreign Key . So while fetching agreement record i am getting both Booking payment details as well as agreement details. Here another thing i want to tell First Booking is inserted then only agreement will be inserted . There is no chance of inserting agreement first then booking so i am sure that the agreement is in second row .Now my problem is while fetching customer detail with payment using innerjoin i am getting both record. But i want 2 row of payment details with customer record. Same customer has done both booking and agreement in that i want Agreement part . So how can i write inner join or any alternate query to fetch my require data .


Example:
Consider I am having Booking table and payment table
in booking consider booking ID as Book123. Now this Booking ID is Forgien key in Payment table. Now customer books a property then a payment ID wll be generated

BookID is primary key

Customer Table

CustomerID   Name Address BookID
CS123        abc    xyz    Book123

付款表

Payment Table

PaymentID   BookID   Amount
1           Book123  10000
2           Book123  15800

现在，当我内在加入并像这样
获取它的来临时

Now when I inner join and fetch its coming like this

CS123        abc    xyz    Book123  1           Book123  10000
CS123        abc    xyz    Book123  2           Book123  15800

它显示两行，但我只希望第二行

it''s displaying both row, but i want only 2nd row

CS123        abc    xyz    Book123  2           Book123  15800

在此先感谢
Arun

Thanks in Advance
Arun

推荐答案

您可能会使用以下内容:

You might use something like this:

DECLARE @pid INT

SELECT @pid = MAX(PaymentID)
FROM Payment
WHERE P.BookID = 'Book123' 

SELECT P.*, C.*
FROM Payment AS P 
    LEFT JOIN Customer AS C ON P.BookID = C.BookID
WHERE P.PaymentID = @pid

我认为，您需要在网格中显示每个客户的第二条记录.

I think, you need to display second record of each customer in a grid.If that so, you can achieve that using following sample code

SELECT CUSTOMERID,BOOKID,NAME,ADDRESS,PAYMENTID,AMOUNT FROM
   (
    SELECT C.CUSTOMERID,C.BOOKID,C.NAME,C.ADDRESS,P.PAYMENTID,P.AMOUNT,
    ROW_NUMBER() OVER(PARTITION BY P.BOOKID,C.CUSTOMERID ORDER BY P.BOOKID)
    AS ROWNUM FROM CUSTOMER C JOIN PAYMENT P
    ON C.BOOKID = P.BOOKID
   )
 AS A WHERE A.ROWNUM = 2

谢谢

Thank you

这篇关于从内部联接查询中的表中获取第二行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！