问：匹配行并在SQL中插入返回值（复杂条件） [英] Q: Match rows and Insert return value in SQL (Complex condition)
问题描述
这是问题表：
This is the Problem Table:
UNIQUE ID NAME TYPE PRICE PAYMENT METHOD Reference
hbg5-5rdw-6ts Bagui RECEIVED 150 MANUAL CREDIT CARD
asd4e-4rs-5tg Cams RECEIVED 100 CASH 181088
fg6gh-rfd4-tgv Cams TRANSFER 100 CASH 181088
a3accf-wrf-aw Chels RECEIVED 700 MANUAL COD
1sder-5tg7-gcd Chels SUCCESS 500 CHECK
asde-1d-sedc Chels SUCCESS 500 CHECK 1sder-5tgs7-gcd5 Failed
ased-asd-sedf Duzy RECEIVED 250 DEBIT 181077
5rt4w-4sd-zsd Duzy TRANSFER 250 DEBIT 181077
4er-445ff-thc Jose RECEIVED 300 CASH 157075
4wer-45ff-4hc Jose TRANSFER 300 CASH 157075
4sde-12d-sedc Lane SUCCESS 500 MANUAL CREDIT CARD
3accf-erf-aec5 Marjo TRANSFER 100 WIRE 181877
cbg44-fgb-6s Marjo RECEIVED 100 WIRE 181877
wer-445ff-4thc Marjo TRANSFER 100 WIRE 181877
3hbg5-5rd-6tsg Raj SUCCESS 300 COD as1sder-5tgs7-gcd5 failed
as1er-tgs7-gd5 Raj SUCCESS 300 COD
OUTPUT / RESULT表应该像这样返回
The OUTPUT/RESULT Table Should return Like this
UNIQUE ID NAME TYPE PRICE PMETHOD Reference Comment
hbg5-5rdw-6ts Bagui RECEIVED 150 CREDITCARD CREDIT CARD CREDIT CARD
asd4e-4rs-5tg Cams RECEIVED 100 CASH 181088 TRANSFER
fg6gh-rfd4-tgv Cams TRANSFER 100 CASH 181088 RECEIVED
a3accf-wrf-aw Chels RECEIVED 700 COD COD COD
1sder-5tg7-gcd Chels SUCCESS 500 MANUAL Failed Delivery
asde-1d-sedc Chels SUCCESS 500 MANUAL 1sder Wrong ADD Failed Delivery
ased-asd-sedf Duzy RECEIVED 250 DEBIT 181077 TRANSFER
5rt4w-4sd-zsd Duzy TRANSFER 250 DEBIT 181077 RECEIVED
4er-445ff-thc Jose RECEIVED 300 CASH 157075 TRANSFER
4wer-45ff-4hc Jose TRANSFER 300 CASH 157075 RECEIVED
4sde-12d-sedc Lane SUCCESS 500 MANUAL CREDIT CARD CREDIT CARD
3accf-erf-aec5 Marjo TRANSFER 100 WIRE 181877 CHECK
cbg44-fgb-6s Marjo RECEIVED 100 WIRE 181877 CHECK
wer-445ff-4thc Marjo TRANSFER 100 WIRE 181877 CHECK
3hbg5-5rd-6tsg Raj SUCCESS 300 MANUAL as1sder-5tgs7 Failed Delivery
as1er-tgs7-gd5 Raj SUCCESS 300 MANUAL Failed Delivery
注意事项：注释标记来自参考
有3个条件可供参考：
数字：：181088,181877 字符： COD，信用卡文本字符串： 1sder-5tgs7
Things to consider: Comment tagging is based from REFERENCE There are 3 conditions for the reference: NUMERIC: 181088,181877 CHARACTER: COD, Credit Card TEXT STRING: 1sder-5tgs7.
如果 NUMERIC：匹配数字参考并标记带有相应对的。 EG：如果数值大于2，例如181877，则应检查或接收 TYPE 的COMMENT
IF NUMERIC: MATCH Numeric REFERENCE and label COMMENT with TYPE of the corresponding pair. EG: Receive or Transfer
IF NUMERIC is more than 2 like 181877 comment should be check
如果字符：复制字符参考到评论，然后根据<$ c $更改付款方式 c>评论
IF CHARACTER: Copy Character REFERENCE to COMMENT then change Payment Method according to COMMENT
如果仅成功，则进入 TYPE 类型STRING：查找匹配文本字符串，并为 COMMENT 标记 Failed Delivery 手册，用于付款方式
，如果 Blanks 表示没有引用。
IF (PRESENT IN SUCCESS TYPE ONLY) TEXT STRING: Find match text string and Label Failed Delivery for COMMENT and Manual for PAYMENT METHOD
IF BLANKS meaning no reference just as is.
*有成千上万笔交易。
*There are thousands of transaction.
订购应参考：NUMERIC，字符串的字符。
Order should be by reference: NUMERIC, CHARACTER to STRINGS.
其他用户的其他建议：
接受
Other advice from another user : accept
您可以使用公用表表达式可以解决可以帮助维护代码的问题。
You can use common table expressions to break down the problem which can help with maintaining the code.
我没有使用lag / lead，因为您只有两个ro是一对，所以对行进行编号和将表连接到其本身感觉起来更快，更容易。
I didn't use lag/lead because you only have two rows in the pair, so numbering the rows and joining the table to itself felt quicker and easier to follow.
非常感谢。 SQL NEWBIE
Thank you so much. SQL NEWBIE
推荐答案
这种设计非常糟糕...
This design is awfull...
以下代码将为您指明方向。如果只用一个较大的 CASE WHEN（相同情况），以相同的方式进行操作，那么在 COMMENT 中获取其他列的更改值将很容易。但是，如果在您的控制之下，那么您真的应该重新考虑设计...
The following code will give you the direction. Getting changed values for other columns then COMMENT will be easy, if you do it the same way with just one more big 'CASE WHEN'. But you should really try to re-think the design, if this under your control...
DECLARE @tbl TABLE(UNIQUE_ID VARCHAR(100),NAME VARCHAR(100),TYPE VARCHAR(100),PRICE DECIMAL(14,4),PAYMENT_METHOD VARCHAR(100),Reference VARCHAR(100));
INSERT INTO @tbl VALUES
('hbg5-5rdw-6ts','Bagui','RECEIVED',150,'MANUAL','CREDIT CARD')
,('asd4e-4rs-5tg','Cams','RECEIVED',100,'CASH','181088')
,('fg6gh-rfd4-tgv','Cams','TRANSFER',100,'CASH','181088')
,('a3accf-wrf-aw','Chels','RECEIVED',700,'MANUAL','COD')
,('1sder-5tg7-gcd','Chels','SUCCESS',500,'CHECK','')
,('asde-1d-sedc','Chels','SUCCESS',500,'CHECK','1sder-5tgs7-gcd5 Failed')
,('ased-asd-sedf','Duzy','RECEIVED',250,'DEBIT','181077')
,('5rt4w-4sd-zsd','Duzy','TRANSFER',250,'DEBIT','181077')
,('4er-445ff-thc','Jose','RECEIVED',300,'CASH','157075')
,('4wer-45ff-4hc','Jose','TRANSFER',300,'CASH','157075')
,('4sde-12d-sedc','Lane','SUCCESS',500,'MANUAL','CREDIT CARD')
,('3accf-erf-aec5','Marjo','TRANSFER',100,'WIRE','181877')
,('cbg44-fgb-6s','Marjo','RECEIVED',100,'WIRE','181877')
,('wer-445ff-4thc','Marjo','TRANSFER',100,'WIRE','181877')
,('3hbg5-5rd-6tsg','Raj','SUCCESS',300,'COD','as1sder-5tgs7-gcd5 failed')
,('as1er-tgs7-gd5','Raj','SUCCESS',300,'COD','');
-CTE将首先检查某些事情
--The CTE will check some things first
WITH checkIt AS
(
SELECT *
,COUNT(*) OVER(PARTITION BY t1.Name,t1.Reference) AS PartCount
,ROW_NUMBER() OVER(PARTITION BY t1.Name,t1.Reference ORDER BY (SELECT NULL)) AS RowInx
,CASE WHEN ISNUMERIC(Reference)=1 THEN 'num' ELSE CASE WHEN ISNULL(Reference,'') ='' THEN 'empty' ELSE 'str' END END AS typ
FROM @tbl AS t1
)
-主选择将使用CTE的标记切换到嵌套 Case -hierarchy时的正确分支
--the main select will use the marks of the CTE to switch to the correct branch of the nested CASE WHEN-hierarchy
SELECT *
,CASE WHEN c1.typ='empty' THEN 'Failed Delivery'
WHEN c1.PartCount>2 THEN 'CHECK'
WHEN c1.typ='num' AND c1.RowInx=2 THEN (SELECT x.TYPE
FROM checkIt AS x
WHERE x.NAME=c1.NAME
AND x.Reference=c1.Reference
AND x.RowInx=1)
WHEN c1.typ='num' AND c1.RowInx=1 THEN (SELECT x.TYPE
FROM checkIt AS x
WHERE x.NAME=c1.NAME
AND x.Reference=c1.Reference
AND x.RowInx=2)
WHEN c1.typ='str' THEN CASE WHEN CHARINDEX(' failed',c1.Reference)>0 THEN 'Failed Delivery'
ELSE c1.Reference END
ELSE 'not handled'
END AS Comment
FROM checkIt AS c1
这篇关于问：匹配行并在SQL中插入返回值（复杂条件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
