iT邦幫忙

0

MS-SQL 將OR條件的值設為Select的欄位

請問有辦法將OR條件的值設為Select出來的欄位嗎??
例如:
source_type_id | text_ch | value
   1    | name1 | [1_1]
   6    | name1 | [2_1]
   9    | name1 | [3_2]

SELECT b.[type_id] as source_type_id , b.[text_ch]
                                FROM Type AS a
                                INNER JOIN SubType AS b 
                                ON a.[1_1] = b.[sub_type_id] 
                                OR a.[1_2] = b.[sub_type_id]
                                OR a.[1_3] = b.[sub_type_id]
                                OR a.[1_4] = b.[sub_type_id]
                                OR a.[1_5] = b.[sub_type_id]
                                OR a.[2_1] = b.[sub_type_id]
                                OR a.[2_2] = b.[sub_type_id]
                                OR a.[3_1] = b.[sub_type_id]
                                OR a.[3_2] = b.[sub_type_id]
                                OR a.[3_3] = b.[sub_type_id]
                                OR a.[3_4] = b.[sub_type_id]
                                OR a.[3_5] = b.[sub_type_id]
                                OR a.[4_1] = b.[sub_type_id]
                                OR a.[4_2] = b.[sub_type_id]
                                OR a.[4_3] = b.[sub_type_id]
                                OR a.[4_4] = b.[sub_type_id]
                                OR a.[4_5] = b.[sub_type_id]
                                OR a.[5_1] = b.[sub_type_id]
                                OR a.[5_2] = b.[sub_type_id]
                                OR a.[5_3] = b.[sub_type_id]
                                OR a.[5_4] = b.[sub_type_id]
                                WHERE a.id = @id
                                GROUP BY b.[type_id] , b.[text_ch]

1 個回答

8
rogeryao
iT邦大師 1 級 ‧ 2021-10-18 15:05:55
最佳解答
CREATE TABLE Type (
[id] varchar(20),
[1_1] varchar(20),	
[2_1] varchar(20),	
[3_1] varchar(20),	
[3_2] varchar(20));

INSERT INTO Type ([id],[1_1],[2_1],[3_1],[3_2])
VALUES 
('ss','q','w','ee','r'),
('gg','uq','dw','ke','sr');
CREATE TABLE SubType (
[type_id] varchar(20),
[sub_type_id] varchar(20),	
[text_ch] varchar(20));

INSERT INTO SubType ([type_id],[sub_type_id],[text_ch])
VALUES 
('1','q','name1'),
('6','w','name1'),
('3','dd','name1'),
('9','r','name1');
-- 方法一
SELECT b.[type_id] as source_type_id , b.[text_ch],
CASE WHEN b.[sub_type_id]=a.[1_1] THEN '[1_1]' 
WHEN b.[sub_type_id]=a.[2_1] THEN '[2_1]' 
WHEN b.[sub_type_id]=a.[3_1] THEN '[3_1]'
WHEN b.[sub_type_id]=a.[3_2] THEN '[3_2]'
END AS value
FROM Type AS a
INNER JOIN SubType AS b 
ON a.[1_1] = b.[sub_type_id] 
OR a.[2_1] = b.[sub_type_id]
OR a.[3_1] = b.[sub_type_id]
OR a.[3_2] = b.[sub_type_id]
WHERE a.id = 'ss'
-- 方法二
SELECT b.[type_id] AS source_type_id , b.[text_ch], a.value
FROM (
SELECT id,'[1_1]' AS value,[1_1] AS temp
FROM Type 
UNION  
SELECT id,'[2_1]' AS value,[2_1] AS temp
FROM Type 
UNION 
SELECT id,'[3_1]' AS value,[3_1] AS temp
FROM Type 
UNION 
SELECT id,'[3_2]' AS value,[3_2] AS temp
FROM Type) AS a
INNER JOIN SubType AS b ON a.[temp] = b.[sub_type_id] 
WHERE a.id = 'ss'
-- 方法三
SELECT b.[type_id] AS source_type_id , b.[text_ch], '['+a.value+']' AS value
FROM Type UNPIVOT(temp FOR [value] IN ([1_1],[2_1],[3_1],[3_2])) a
INNER JOIN SubType AS b ON a.[temp] = b.[sub_type_id] 
WHERE a.id = 'ss'

Demo

小火車 iT邦新手 4 級 ‧ 2021-10-19 09:11:58 檢舉

感謝大大 提供多種方法 小弟學習到了 謝謝

我要發表回答

立即登入回答