我得到的多个值并不像预期的那样可以帮助我 [英] I am getting multiple values not as expected can u pls help me

问题描述

TEMP1

--------------------

COL1 COL2 COL3

--------------------

MW12 null A

MW13 13 A

null null A

null null A

MW16 16 A

MW17 null A

MW18 18 A

MW20 20 A

MW22 22 A



TEMP2

----- ---------------

COL1 COL2 COL3

---------------- ----

null null A

null null A

null 14 A

MW15 15 A

MW16 16 A

MW17 17 A

MW19 18 A

MW20 21 A

null 23 A



预计结果：



MW12

MW13

14

NULL

MW16

MW17

MW18

MW20

MW22,23（记录分离）



我尝试过：



SELECT DISTINCT

（

CASE

WH EN T2.COL1为空，T2.COL2为空，T1.COL2为空，T1.COL1--1

当T2.COL1为空时，T2.COL2为空，然后为T1.COL1- -2

当T1.COL2为空且T2.COL1为空且T1.COL1为空时T2.COL2--3

当T1.COL2 = T2时。 COL2和T2.COL1 = T1.COL1然后T1.COL1--5

当T2.COL1 = T1.COL1和T1.COL2为空时T1.COL1--6

当T1.COL2 = T2.COL2和T2.COL1！= T1.COL1那么T1.COL1--7

当T1.COL2！= T2.COL2和T2.COL1 = T1 .COL1然后T1.COL1--8

当T1.COL2！= T2.COL2和T2.COL1为空时那么T1.COL2 - 9

当T1。 COL2为空，T1.COL1为空，然后为空 - 4

结束

）EMPLOYEE_ID

来自TEMP1 T1，TEMP2 T2 WHERE T1。 COL3 = T2.COL3;

TEMP1
--------------------
COL1 COL2 COL3
--------------------
MW12 null A
MW13 13 A
null null A
null null A
MW16 16 A
MW17 null A
MW18 18 A
MW20 20 A
MW22 22 A

TEMP2
--------------------
COL1 COL2 COL3
--------------------
null null A
null null A
null 14 A
MW15 15 A
MW16 16 A
MW17 17 A
MW19 18 A
MW20 21 A
null 23 A

Expected RESULTS:

MW12
MW13
14
NULL
MW16
MW17
MW18
MW20
MW22,23(RECORDS SEPARATE)

What I have tried:

SELECT DISTINCT
(
CASE
WHEN T2.COL1 IS NULL AND T2.COL2 IS NULL AND T1.COL2 IS NULL THEN T1.COL1--1
WHEN T2.COL1 IS NULL AND T2.COL2 IS NULL THEN T1.COL1--2
WHEN T1.COL2 IS NULL AND T2.COL1 IS NULL AND T1.COL1 IS NULL THEN T2.COL2--3
WHEN T1.COL2=T2.COL2 AND T2.COL1=T1.COL1 THEN T1.COL1--5
WHEN T2.COL1=T1.COL1 AND T1.COL2 IS NULL THEN T1.COL1--6
WHEN T1.COL2=T2.COL2 AND T2.COL1!=T1.COL1 THEN T1.COL1--7
WHEN T1.COL2!=T2.COL2 AND T2.COL1=T1.COL1 THEN T1.COL1--8
WHEN T1.COL2!=T2.COL2 AND T2.COL1 IS NULL THEN T1.COL2 --9
WHEN T1.COL2 IS NULL AND T1.COL1 IS NULL THEN NULL--4
END
) EMPLOYEE_ID
FROM TEMP1 T1,TEMP2 T2 WHERE T1.COL3=T2.COL3;

推荐答案

SELECT  DISTINCT
           CASE WHEN  (t1.col1 IS NULL) AND (t2.col1 IS NOT NULL)  THEN null 
                WHEN  t2.col1 IS NULL THEN cast(t2.col2 as Varchar(50)) 
                 ELSE t1.col1  END AS Result
 FROM #TEMP1 T1 INNER JOIN #TEMP2 T2
                           ON(T1.COL3=T2.COL3)

目前还不清楚你实际想要达到的目标！



首先，我认为你需要了解COALESCE [ ^ ]功能



因为你似乎需要来自两个表的值，所以你还需要知道UNION [ ^ ]运营商



似乎没有任何逻辑说明你为什么不想要MW15和MW19在你的结果中，但我能得到的最接近的是

It is not at all clear what you are actually trying to achieve!

Firstly, I think you need to learn about the COALESCE[^] function

As you seem to need values from both tables then you also need to know about the UNION[^] operator

There doesn't seem to be any logic as to why you are not also wanting MW15 and MW19 in your results, but the closest I can get is with

select COALESCE(col1, col2) from temp1 
UNION 
SELECT COALESCE(col1, col2)  FROM temp2

请注意，我已经使用了UNION而不是UNION ALL来为我有效地删除重复项。



最后，事实是您的代码运行完全意味着您已将数字存储为字符类型字段 - 这是非常差的表设计。

Note that I've used UNION and not UNION ALL to effectively remove the duplicates for me.

Finally, the fact that your code ran at all implies that you have stored numbers as character type fields - that is very poor table design.

select distinct isnull(isnull(isnull(t1.COL1,t1.COL2),t2.COL1),t2.col2)

FROM TEMP1 T1,TEMP2 T2 WHERE T1.COL3=T2.COL3 

这篇关于我得到的多个值并不像预期的那样可以帮助我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！