将2列合并为一列，并添加一列以使用SQL中的if或case来放置备注(Combine 2 columns in one and add a column to put remarks using if

将2列合并为一列，并添加一列以使用SQL中的if或case来放置备注(Combine 2 columns in one and add a column to put remarks using if or case in SQL)

我有3张桌子。

我想得到这个结果

我已经尝试过工会表，但它确实有效。 我只需要在一列（Type）中组合2列（Num_Typ）和（Add_Typ），然后如果地址或电话号码不可用则显示N / A. 接下来，我将添加一个列（备注），具体取决于他们是否有地址，电话号码或两者，仅限地址或仅限电话。

这是查询

SELECT A.NAMES, A.CODE, B.NUM_TYP, B.PHONE_NUN, B.ADD_TYP, B.ADDR FROM NAMES A LEFT JOIN ( SELECT PHONE.ID, PHONE.NUM_TYP, PHONE.PHONE_NUM, ADDRESS.ADD_TYP, ADDRESS.ADDR FROM PHONE LEFT JOIN ADDRESS ON (PHONE.NUM_TYP = ADDRESS.ADD_TYP) AND (PHONE.ID = ADDRESS.ID) UNION SELECT ADDRESS.ID, PHONE.USG_TYP, PHONE.PHONE_NUM, ADDRESS.ADD_TYP, ADDRESS.ADDR FROM ADDRESS LEFT JOIN PHONE ON (ADDRESS.ADD_TYP = PHONE.NUM_TYP) AND (ADDRESS.ID = PHONE.ID)) AS B ON A.ID = B.ID;

感谢任何帮助。 谢谢。

I have 3 tables.

I want to get this result

I have tried union tables and it worked. I just need to combine 2 columns (Num_Typ) and (Add_Typ) in one column (Type) then if address or phone number is not available then display N/A. Next I will add a column (Remarks) depending on whether they have an address , a phone number or both, address only or phone only.

Here is the Query

SELECT A.NAMES, A.CODE, B.NUM_TYP, B.PHONE_NUN, B.ADD_TYP, B.ADDR FROM NAMES A LEFT JOIN ( SELECT PHONE.ID, PHONE.NUM_TYP, PHONE.PHONE_NUM, ADDRESS.ADD_TYP, ADDRESS.ADDR FROM PHONE LEFT JOIN ADDRESS ON (PHONE.NUM_TYP = ADDRESS.ADD_TYP) AND (PHONE.ID = ADDRESS.ID) UNION SELECT ADDRESS.ID, PHONE.USG_TYP, PHONE.PHONE_NUM, ADDRESS.ADD_TYP, ADDRESS.ADDR FROM ADDRESS LEFT JOIN PHONE ON (ADDRESS.ADD_TYP = PHONE.NUM_TYP) AND (ADDRESS.ID = PHONE.ID)) AS B ON A.ID = B.ID;

Appreciate any help. Thanks.

最满意答案

您可以在查询中添加CASE表达式，以呈现所需的消息：

SELECT A.NAMES, A.CODE, B.NUM_TYP, B.PHONE_NUN, B.ADD_TYP, B.ADDR, CASE WHEN B.PHONE_NUN IS NOT NULL AND B.ADDR IS NULL THEN 'MEMBER HAS PHONE ONLY' WHEN B.ADDR IS NOT NULL AND B.PHONE_NUM IS NULL THEN 'MEMBER HAS ADDR ONLY' WHEN B.PHONE_NUN IS NOT NULL AND B.ADDR IS NOT NULL THEN 'MEMBER HAS BOTH' END AS REMARKS FROM NAMES A LEFT JOIN ...

You can add a CASE expression to your query which renders the message you want:

SELECT A.NAMES, A.CODE, B.NUM_TYP, B.PHONE_NUN, B.ADD_TYP, B.ADDR, CASE WHEN B.PHONE_NUN IS NOT NULL AND B.ADDR IS NULL THEN 'MEMBER HAS PHONE ONLY' WHEN B.ADDR IS NOT NULL AND B.PHONE_NUM IS NULL THEN 'MEMBER HAS ADDR ONLY' WHEN B.PHONE_NUN IS NOT NULL AND B.ADDR IS NOT NULL THEN 'MEMBER HAS BOTH' END AS REMARKS FROM NAMES A LEFT JOIN ...