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

sql ms sql

小火車 2021-10-18 12:45:33 ‧ 1313 瀏覽

分享至

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]

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 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