我的需要可能看起來有點奇怪，但我需要從 excel 檔案中提取一整列并將其粘貼到“IN”子句中。在此列中有許多重復值。
SQL 查詢看起來像這樣：
SELECT pack.pack_lib,
upub.usr_nni,
upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub ON upub.usr_id = pack.usr_id_publication
WHERE pack.pack_lib in(
'07655_23687_30863',
'07655_23687_30863',
'07432_76544_67890'
)
ORDER BY pack.date_publication DESC;
正如您在此示例中所見，“IN”子句中有 2 個重復值，此查詢的標準行為是為這 2 個值回傳一個值。
但我需要得到兩條線（這將是相同的）而不是一條。
上面只是一個例子，但會有很多值，一些重復，還有一些不重復。
如何做到這一點？我在版本9.2.0.8.0上使用ORACLE
uj5u.com熱心網友回復：
在 Oracle 中，您可以INNER JOIN對表集合運算式：
SELECT pack.pack_lib,
upub.usr_nni,
upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub
ON upub.usr_id = pack.usr_id_publication
INNER JOIN TABLE(SYS.ODCIVARCHAR2LIST(
'07655_23687_30863',
'07655_23687_30863',
'07432_76544_67890'
)) l
ON pack.pack_lib = l.COLUMN_VALUE
ORDER BY pack.date_publication DESC;
其中，對于樣本資料：
CREATE TABLE t_user(usr_id, usr_nni, usr_email) AS
SELECT 1, 1, '[email protected]' FROM DUAL UNION ALL
SELECT 2, 2, '[email protected]' FROM DUAL;
CREATE TABLE t_package(usr_id_publication, pack_lib, date_publication) AS
SELECT 1, '07655_23687_30863', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 2, '07432_76544_67890', DATE '2022-01-02' FROM DUAL;
輸出：
PACK_LIB USR_NNI USR_EMAIL 07432_76544_67890 2 [email protected] 07655_23687_30863 1 [email protected] 07655_23687_30863 1 [email protected]
如果您沒有SYS.ODCI*LIST型別的可見性，那么您可以創建用戶定義的集合型別：
CREATE TYPE string_list AS TABLE OF VARCHAR2(20);
然后使用：
SELECT pack.pack_lib,
upub.usr_nni,
upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub
ON upub.usr_id = pack.usr_id_publication
INNER JOIN TABLE(string_list(
'07655_23687_30863',
'07655_23687_30863',
'07432_76544_67890'
)) l
ON pack.pack_lib = l.COLUMN_VALUE
ORDER BY pack.date_publication DESC;
db<>在這里擺弄
uj5u.com熱心網友回復：
您需要使用JOIN而不是半連接（IN語法）。以下查詢應適用于 Oracle：
SELECT pack.pack_lib,
upub.usr_nni,
upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub ON upub.usr_id = pack.usr_id_publication
LEFT OUTER JOIN (
SELECT '07655_23687_30863' pack_lib FROM dual
UNION ALL
SELECT '07655_23687_30863' FROM dual
) t
ON pack.pack_lib = t.pack_lib;
SQL Server 和 PostgreSQL 還應該允許以下語法
SELECT pack.pack_lib,
upub.usr_nni,
upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub ON upub.usr_id = pack.usr_id_publication
LEFT OUTER JOIN (VALUES ('07655_23687_30863'), ('07655_23687_30863')) AS t(pack_lib)
ON pack.pack_lib = t.pack_lib;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/414315.html
標籤：
下一篇：用10億條記錄更新emp_id列