SQL查询以基于列值的子字符串获取结果 [英] SQL query to get results based on substring of column value
问题描述
这是银行应用程序。我将从一个帐户转移到另一个帐户的交易为同一个会员,有时我会收到从一个帐户转移到属于另一个会员的另一个帐户的交易。
我需要全部从一个帐户转移到另一个帐户的另一个帐户的交易。下面是数据和tmp表
从InternalAccountID列数据(前7个字符),可以确定是否从同一个成员或不同成员的一个帐户转移到另一个帐户。
为同一会员转账:
  ;  
FROM:BK-4001-1796
TO:BK-4001-665
转让给其他会员:
  ;        
FROM:BK- 2001 -200
TO:BK- 0001 -844
声明 @tmp
TABLE
(
ID int ,
TrnID bigint ,
会员名称 varchar ( 250 ",
AccountID int ,
TrnType varchar ( 100 ",
SubTrnType varchar ( 100 ",
金额 十进制 ( 30 , 10 ),
InternalAccountID varchar ( 50 ),
TrnCategory varchar ( 50 ",
CreationDate 日期时间
)
INSERT
INTO @tmp
( ID ,
TrnID , MemberName ,
AccountID , TrnType ,
SubTrnType , 金额 ,
InternalAccountID , TrnCategory ,
CreationDate )
SELECT 1859 ,
20190520000195 ,
'TEST1' , 1796 ,
'CashTransfer' ,
'Withdraw' , 821.90 ,
'BK-4001-1796' ,
'内部' ,
'2019-05-20 00:00:00.000'
UNION
SELECT 1860 ,
20190520000195 ,
'TEST1' , 665 ,
'CashTransfer' ,
'存款' , 821.90 ,
'BK-4001-665' ,
'内部' ,
'2019-05-20 00:00:00.000'
UNION
SELECT 2491 ,
20190520001029 ,
'TEST2' , 200 ,
'CashTransfer' ,
'Withdraw' , 5000.75 ,
'BK- 2001 -200' ,
'内部' ,
'2019-05-20 00:00:00.000'
UNION
SELECT 2491 ,
20190520001029 ,
'TEST2' , 844 ,
'CashTransfer' ,
'存款' , 5000.75 ,
'BK- 0001 -844' ,
'内部' ,
'2019-05-20 00:00:00.000'
选择
*
来自 @tmp
我应该得到的结果是
对于相同的TrnID,InternalAccountID的8个字符是不同的(如BK-2001与Bn-0001不同,对于TrnID
20190520001029)。
结果集应返回20190520001029的两笔交易,但最终交易应由2个交易组成,如下所示:
TrnID
DEBIT_ACT CREDIT_ACT
< /跨度> AMOUNT <跨度> 跨度> DEBIT_INTERNALACCOUNTID <跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
跨度> CREDIT_INTERNALACCOUNTID,CREATIONDATE 跨度>
2491
200 844 ;&NBSP;&NBSP;&NBSP;&NBSP; 跨度> 500.75 <跨度>&NBSP;&NBSP;&NBSP; 跨度> BK-2001200 <跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;
BK-0001-844
我该怎么做。
谢谢
< blockquote>
嗨Spunny,
请查看:
DECLARE @tmp TABLE(
ID int,
TrnID bigint,
MemberName varchar(250),
AccountID int,
TrnType varchar(100),
SubTrnType varchar(100),
金额十进制(30,10),
InternalAccountID varchar(50),
TrnCategory varchar(50),
CreationDate DateTime
);
将INTO @tmp(ID,TrnID,成员名称,帐户ID,TrnType,SubTrnType,金额,InternalAccountID,TrnCategory,CreationDate)
值(1859,20190520000195, 'TEST1',1796年, 'CashTransfer', '取款', 821.90,'BK-4001-1796','内部','2019-05-20 00:00:00.000')
,(1860,20190520000195,'TEST1',665,'CashTransfer','存款' ,821.90,'BK-4001 -665','内部','2019-05-20 00:00:00.000')
,(2491,20190520001029,'TEST2',200,'CashTransfer','Withdraw',5000.75,'BK- 2001-200','内部','2019-05-20 00:00:00.000')
,(2491,20190520001029,'TEST2',844,'CashTransfer','存款',5000.75,'BK -0001-844','INTERNAL','2019-05-20 00:00:00.000');
SELECT * FROM @tmp;
; WITH AS
RS(
选择a.ID,a.TrnID,a.AccountID AS DEBIT_ACT,a.Amount,a.InternalAccountID AS DEBIT_INTERNALACCOUNTID
, a.CreationDate
,b.AccountID AS CREDIT_ACT,b.InternalAccountID AS CREDIT_INTERNALACCOUNTID
,rn = ROW_NUMBER()OVER(a.TrnID ORDER by a.SubTrnType DESC)
FROM @tmp AS一个INNER JOIN
@tmp为b ON a.TrnID = b.TrnID和左(a.InternalAccountID,7)!= LEFT(b.InternalAccountID,7)
)
SELECT *
来自rs
WHERE rn = 1;
结果:
ID TrnID DEBIT_ACT金额DEBIT_INTERNALACCOUNTID CreationDate CREDIT_ACT CREDIT_INTERNALACCOUNTID
2491 20190520001029 200 5000.7500000000 BK-2001-200 2019-05-20 00:00:00.000 844 BK-0001-844 1
Hi,
This is banking application. I get transactions that are transferred from one account to another account for same member and sometimes I get transactions that are transfers from one account to another account belonging to another member.
I need to get all the transactions that are transfers from one account to another account belonging to another member. Below is the data and tmp table
From InternalAccountID column data (first 7 characters), it can be determined if transfer is from one account to another for same member or different member.
Transfer for Same Member:
FROM: BK-4001-1796
TO: BK-4001-665
Transfer for different Member:
FROM: BK-2001-200
TO: BK-0001-844
declare @tmp TABLE
(
ID int,
TrnID bigint,
MemberName varchar(250),
AccountID int,
TrnType varchar(100),
SubTrnType varchar(100),
Amount decimal(30,10),
InternalAccountID varchar(50),
TrnCategory varchar(50),
CreationDate DateTime
)
INSERT INTO @tmp (ID, TrnID, MemberName, AccountID, TrnType, SubTrnType, Amount, InternalAccountID, TrnCategory, CreationDate)
SELECT 1859, 20190520000195, 'TEST1', 1796, 'CashTransfer', 'Withdraw', 821.90, 'BK-4001-1796', 'INTERNAL', '2019-05-20 00:00:00.000'
UNION
SELECT 1860, 20190520000195, 'TEST1', 665, 'CashTransfer', 'Deposit', 821.90, 'BK-4001-665', 'INTERNAL', '2019-05-20 00:00:00.000'
UNION
SELECT 2491, 20190520001029, 'TEST2', 200, 'CashTransfer', 'Withdraw', 5000.75, 'BK-2001-200', 'INTERNAL', '2019-05-20 00:00:00.000'
UNION
SELECT 2491, 20190520001029, 'TEST2', 844, 'CashTransfer', 'Deposit', 5000.75, 'BK-0001-844', 'INTERNAL', '2019-05-20 00:00:00.000'
select * from @tmp
The result I should get is
Where first 8 characters of InternalAccountID is different for same TrnID ( like BK-2001 is different from BK-0001 for TrnID 20190520001029).
The result set should return both transactions of 20190520001029, but final transaction should be made up of 2 transactions into one like this:
TrnID DEBIT_ACT CREDIT_ACT AMOUNT DEBIT_INTERNALACCOUNTID CREDIT_INTERNALACCOUNTID, CREATIONDATE
2491 200 844 500.75 BK-2001200 BK-0001-844
How can I do.
Thank You
Hi Spunny,
Please check it out:
DECLARE @tmp TABLE( ID int, TrnID bigint, MemberName varchar(250), AccountID int, TrnType varchar(100), SubTrnType varchar(100), Amount decimal(30,10), InternalAccountID varchar(50), TrnCategory varchar(50), CreationDate DateTime ); INSERT INTO @tmp (ID, TrnID, MemberName, AccountID, TrnType, SubTrnType, Amount, InternalAccountID, TrnCategory, CreationDate) VALUES( 1859, 20190520000195, 'TEST1', 1796, 'CashTransfer', 'Withdraw', 821.90, 'BK-4001-1796', 'INTERNAL', '2019-05-20 00:00:00.000') , (1860, 20190520000195, 'TEST1', 665, 'CashTransfer', 'Deposit', 821.90, 'BK-4001-665', 'INTERNAL', '2019-05-20 00:00:00.000') , (2491, 20190520001029, 'TEST2', 200, 'CashTransfer', 'Withdraw', 5000.75, 'BK-2001-200', 'INTERNAL', '2019-05-20 00:00:00.000') , (2491, 20190520001029, 'TEST2', 844, 'CashTransfer', 'Deposit', 5000.75, 'BK-0001-844', 'INTERNAL', '2019-05-20 00:00:00.000'); SELECT * FROM @tmp; ;WITH rs AS ( SELECT a.ID, a.TrnID, a.AccountID AS DEBIT_ACT, a.Amount, a.InternalAccountID AS DEBIT_INTERNALACCOUNTID , a.CreationDate , b.AccountID AS CREDIT_ACT, b.InternalAccountID AS CREDIT_INTERNALACCOUNTID , rn = ROW_NUMBER() OVER(PARTITION BY a.TrnID ORDER BY a.SubTrnType DESC) FROM @tmp AS a INNER JOIN @tmp AS b ON a.TrnID = b.TrnID AND LEFT(a.InternalAccountID,7) != LEFT(b.InternalAccountID,7) ) SELECT * FROM rs WHERE rn = 1;Outcome:
ID TrnID DEBIT_ACT Amount DEBIT_INTERNALACCOUNTID CreationDate CREDIT_ACT CREDIT_INTERNALACCOUNTID rn 2491 20190520001029 200 5000.7500000000 BK-2001-200 2019-05-20 00:00:00.000 844 BK-0001-844 1
这篇关于SQL查询以基于列值的子字符串获取结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!