加入相同的数据库表但只想找回一条记录 [英] Joining Same DB Table but want to locate back one record only
问题描述
正确的人。
这里的第一次海报。
我有一张名为Trans的表，看起来像
批量，序列，客户，日期，交易
------------------- --------------------------------------
B100,1， C1,10 / 10/2007，T1
B100,2，C2,10 / 09/2007，T2
...
B78 ，5，C1,05 / 05/2007，T5
..
B69,3，C2,04 / 04/2007，T8
..
B53,3，C1,03 / 03/2007，T10
我需要的脚本必须经过所有在B100中记录并找到同一表中每个客户的最后一笔交易（按日期）。我基本上想结束
客户，日期
-------------------- -
C1,05 / 05/2007
C2,04 / 04/2007
希望有人能给我一个脚本来做。
谢谢
RebelDevil
Right folks.
First time poster here.
I have a table called Trans that looks like
Batch, Seq, Client, Date, Transaction
---------------------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
...
B78, 5, C1, 05/05/2007, T5
..
B69, 3, C2, 04/04/2007, T8
..
B53, 3, C1, 03/03/2007, T10
The script I need will have to go through all recs in B100 and find the last transaction (by date) for each client within the same table. I basically want to end up with
Client, Date
----------------------
C1, 05/05/2007
C2, 04/04/2007
Hopefully somebody can give me a script to do it.
Thanks
RebelDevil
推荐答案
正确的人们。
这里的第一次海报。
我有一张叫做Trans的桌子喜欢
批量，顺序，客户，日期，交易
------------ ---------------------------------------------
B100,1，C1,10 / 09/2007，T1
B100,2，C2,10 / 09/2007，T2
...
B78,5，C1,05 / 05/2007，T5
..
B69,3，C2,04 / 04/2007，T8
..
B53,3，C1,03 / 03/2007，T10
脚本我需要经过B100中的所有recs并找到每个客户机智的最后一笔交易（按日期）同桌。我基本上想结束
客户，日期
-------------------- -
C1,05 / 05/2007
C2,04 / 04/2007
希望有人能给我一个脚本来做。
谢谢
RebelDevil
Right folks.
First time poster here.
I have a table called Trans that looks like
Batch, Seq, Client, Date, Transaction
---------------------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
...
B78, 5, C1, 05/05/2007, T5
..
B69, 3, C2, 04/04/2007, T8
..
B53, 3, C1, 03/03/2007, T10
The script I need will have to go through all recs in B100 and find the last transaction (by date) for each client within the same table. I basically want to end up with
Client, Date
----------------------
C1, 05/05/2007
C2, 04/04/2007
Hopefully somebody can give me a script to do it.
Thanks
RebelDevil
您的样本数据是不是很清楚，并没有与您的要求同步：
您希望每批下每个客户的最新交易。
样本数据
批量客户日期转换
B100 C1 10/09/2007 T1
B100 C1 05/05/2007 T2
B100 C2 10/09/2007 T3
B100 C2 05/04/2007 T4
然后您的样品输出应该看起来喜欢：
客户日期
C1 10/09/2007
C2 10/09/2007
...........
我认为我的理解是正确的，
尝试以下查询：
Your sample data is not that clear and is not with in sync of your requirement:
You want the latest transaction for each client under each batch.
Sample data
Batch client date trans
B100 C1 10/09/2007 T1
B100 C1 05/05/2007 T2
B100 C2 10/09/2007 T3
B100 C2 05/04/2007 T4
Then your sample output should look like:
Client Date
C1 10/09/2007
C2 10/09/2007
...........
I assume my understanding is correct,
try for below query:
对不起，
让我再试一次。我上面可能错了。
Trans Table中的记录是
批量，序列，客户，日期，交易
-------------------------------------------- -
B100,1，C1,10 / 09/2007，T1
B100,2，C2,10 / 09/2007，T2
B78,5，C1,05 / 05/2007，T5
B69,3，C2,04 / 04/2007，T8
B69,4，C1,04 / 04/2007，T12
B53,3，C1,03 / 03/2007，T10
此表的唯一索引是批量+序号
我有一个屏幕，然后显示批次B100的所有交易，所以它显示
B100,1，C1,10 / 09/2007，T1
B100,2，C2,10 / 09/2007，T2
但我还需要为每个客户显示以前的交易。
当我说我以前的意思是按样本日期的最后一笔交易。所以在C1文件中的前一个是
B78,5，C1,05 / 05/2007，T5
和之前的一个在C2的文件是
B69,3，C2,04 / 04/2007，T8
我的决赛SQL语句应该返回
客户端，上一个交易。
C1，T5
C2，T8
一旦我有一个SQL语句来显示上面的内容，我就可以解决剩下的问题了。
希望这能更好地解释它。
TIA
RebelDevil
Sorry,
Let me try to explain it again. I might have got it wrong above.
The records in the Trans Table are
Batch, Seq, Client, Date, Transaction
---------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
B78, 5, C1, 05/05/2007, T5
B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12
B53, 3, C1, 03/03/2007, T10
The unique index for this table is Batch+Seq
I have a screen then that display all transactions for Batch B100 so it displays
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
But I also need to display the previous transaction for each client.
When I say previous I mean by the last transaction by Sample date. So the previous one in the file for C1 is
B78, 5, C1, 05/05/2007, T5
and the previous one in the file for C2 is
B69, 3, C2, 04/04/2007, T8
My final SQL Statement should return
Client, Prev Transaction.
C1, T5
C2, T8
Once I have a SQL statement to display the above then I can work out the rest.
Hope this explains it better.
TIA
RebelDevil
对不起，
让我试试再解释一下。我上面可能错了。
Trans Table中的记录是
批量，序列，客户，日期，交易
-------------------------------------------- -
B100,1，C1,10 / 09/2007，T1
B100,2，C2,10 / 09/2007，T2
B78,5，C1,05 / 05/2007，T5
B69,3，C2,04 / 04/2007，T8
B69,4，C1,04 / 04/2007，T12
B53,3，C1,03 / 03/2007，T10
此表的唯一索引是批量+序号
我有一个屏幕，然后显示批次B100的所有交易，所以它显示
B100,1，C1,10 / 09/2007，T1
B100,2，C2,10 / 09/2007，T2
但我还需要为每个客户显示以前的交易。
当我说我以前的意思是按样本日期的最后一笔交易。所以在C1文件中的前一个是
B78,5，C1,05 / 05/2007，T5
和之前的一个在C2的文件是
B69,3，C2,04 / 04/2007，T8
我的决赛SQL语句应该返回
客户端，上一个交易。
C1，T5
C2，T8
一旦我有一个SQL语句来显示上面的内容，我就可以解决剩下的问题了。
希望这能更好地解释它。
TIA
RebelDevil
Sorry,
Let me try to explain it again. I might have got it wrong above.
The records in the Trans Table are
Batch, Seq, Client, Date, Transaction
---------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
B78, 5, C1, 05/05/2007, T5
B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12
B53, 3, C1, 03/03/2007, T10
The unique index for this table is Batch+Seq
I have a screen then that display all transactions for Batch B100 so it displays
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
But I also need to display the previous transaction for each client.
When I say previous I mean by the last transaction by Sample date. So the previous one in the file for C1 is
B78, 5, C1, 05/05/2007, T5
and the previous one in the file for C2 is
B69, 3, C2, 04/04/2007, T8
My final SQL Statement should return
Client, Prev Transaction.
C1, T5
C2, T8
Once I have a SQL statement to display the above then I can work out the rest.
Hope this explains it better.
TIA
RebelDevil
尝试以下查询：
Try below query:
这篇关于加入相同的数据库表但只想找回一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
