这是问题表：
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表应该像这样返回
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个条件可供参考：NUMERIC： 181088,181877特性： COD，信用卡文本字符串： 1sder-5tgs7。
IF NUMERIC： MATCH数值
REFERENCE和标签COMMENT与TYPE相应的对。EG：如果NUMERIC大于2（例如181877），则应接收或转移注释如果字符：复制字符
REFERENCE，COMMENT然后Payment Method根据更改COMMENTIF（
TYPE仅成功）当前文本字符串：查找IF的match文本字符串和“标签交付失败”COMMENT以及“PAYMENT METHODIF手册”，BLANKS表示没有照原样引用。
*有成千上万的交易。
订单应通过参考：NUMERIC，CHARACTER至STRINGS。
其他用户的其他建议：接受
您可以使用公用表表达式来分解问题，以帮助维护代码。
我没有使用滞后/超前，因为一对中只有两行，因此对行进行编号并将表与其自身连接起来感觉更快，更容易理解。
非常感谢。SQL NEWBIE
这种设计是可怕的...
以下代码将为您提供指导。COMMENT如果您只用一个大的“ CASE WHEN”来做同样的事情，那么获取其他列的更改值将很容易。但是，如果您可以控制，那么您应该真正尝试重新考虑设计...
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将首先检查一些事情
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 WHEN层次结构的正确分支
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句