如何从Oracle SQL的同一列中获取单独的值

huangapple go评论59阅读模式
英文:

how to get separate value from same column on Oracle SQL

问题

ID tenant_address
1 5th Avenue, Apt 320, NYC, NY, 10012
2 4th Avenue, Apt 325, NYC, NY, 10012
英文:

apartment_Table

ID update_Date code address1 address2 city state_code zip_code
1 2023/02/15 CO Apt 320 null NYC NY 10012
1 2021/12/03 CO Apt 105 null NYC NY 10012
1 2023/02/15 WK 5th Avenue null NYC NY 10012
2 2023/02/15 CO Apt 325 null NYC NY 10012
2 2022/01/12 CO Apt 123 null NYC NY 10012
2 2023/02/14 WK 4th Avenue null NYC NY 10012
2 2021/02/11 WK 5th Avenue null NYC NY 10012

From the table, WK code is road_name and CO code is apartment number.

update_Date is the latest data when user update in the system. I would like to know How do I get result like below

(latest update_Date WK belong to latest update_Date CO)

ID tenant_address
1 5th Avenue, Apt 320, NYC, NY, 10012
2 4th Avenue, Apt 325, NYC, NY, 10012

SQL I wrote:

SELECT
View_B_ID AS ViewID,
DECODE(P.address1, NULL, 'Street Not Available, ', SUBSTR(P.address1, 1, 50) || ', ')
|| DECODE(P.address2, NULL, '', SUBSTR(P.address2, 1, 50) || ', ')
|| DECODE(P.CITY, NULL, '', P.CITY || ', ')
|| DECODE(P.STAT_CODE, NULL, '', STAT_CODE || ', ')
|| DECODE(P.ZIP, NULL, '', P.ZIP) AS tenant_address
FROM View_B --assume I get data from View_B
 LEFT JOIN (SELECT R.ID,
            R.address1,
            R.address2,
            R.CITY,
            R.STAT_CODE,
            R.ZIP,
            ROW_NUMBER() OVER(PARTITION BY R.ID ORDER BY R.update_Date DESC) AS RN
            FROM apartment_Table R
            WHERE R.code = 'CO'
            ) P
ON ViewID = P.ID
AND P.RN = 1

Output:

ID tenant_address
1 Apt 320, NYC, NY, 10012
2 Apt 325, NYC, NY, 10012

Should I use LISTAGG function ? how to get the correct result ?
please help. thank you so much

答案1

得分: 1

你可以使用max keep dense_rank聚合函数来完成任务,如下所示:

SELECT ID,
       REGEXP_REPLACE(MAX(ADDRESS1) /* WK code road_name */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS1) /* CO code apartment number */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'CO', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS2)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(CITY)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(STATE_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ZIP_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC)
                 , '(,[[:space:]])(,)'
                 , ''
              ) AS TENANT_ADDRESS
  FROM YOUR_TABLE_NAME T
 GROUP BY ID;

在db<>fiddle上查看示例

查看Oracle文档获取更多详细信息

英文:

You could use max keep dense_rank aggregate function to get the job done as below :

SELECT ID,
       REGEXP_REPLACE(MAX(ADDRESS1) /* WK code road_name */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;WK&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC) || &#39;, &#39; || 
                      MAX(ADDRESS1) /* CO code apartment number */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;CO&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC) || &#39;, &#39; || 
                      MAX(ADDRESS2)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;WK&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC) || &#39;, &#39; || 
                      MAX(CITY)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;WK&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC) || &#39;, &#39; || 
                      MAX(STATE_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;WK&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC) || &#39;, &#39; || 
                      MAX(ZIP_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, &#39;WK&#39;, 1, 2) ASC,
                           UPDATE_DATE DESC)
                 , &#39;(,[[:space:]])(,)&#39;
                 , &#39;\2&#39;
              ) AS TENANT_ADDRESS
  FROM YOUR_TABLE_NAME T
 GROUP BY ID;

demo on db<>fiddle

See oracle documentation for more details

答案2

得分: 0

以下是翻译好的内容,代码部分未进行翻译:

You could create a query (tmax) with max dates per ID for both codes 'WK' and 'CO' having them in the same row. Then join your table twice by codes and just concat your tenant's latest address:
您可以创建一个查询(tmax),其中按ID获取'WK'和'CO'的最大日期,使它们在同一行中。然后按代码两次连接您的表,并只需连接您租户的最新地址:

With your sample data:
使用您的示例数据:

... you should get this as the result
您应该得到以下结果:

ID TENANT_ADDRESS


1 5th Avenue, Apt 320, NYC, NY, 10012
2 4th Avenue, Apt 325, NYC, NY, 10012

英文:

You could create a query (tmax) with max dates per ID for both codes 'WK' and 'CO' having them in the same row. Then join your table twice by codes and just concat your tenant's latest address:

SELECT      tmax.ID, 
            twk.ADDRESS1 || &#39;, &#39; || tco.ADDRESS1 || &#39;, &#39; || tco.CITY || &#39;, &#39; || tco.STATE_CODE || &#39;, &#39; || tco.ZIP_CODE &quot;TENANT_ADDRESS&quot;
FROM        ( Select      ID, 
                          MAX(CASE WHEN CODE = &#39;WK&#39; THEN UPD_DATE END) &quot;WK_DATE&quot;,
                          MAX(CASE WHEN CODE = &#39;CO&#39; THEN UPD_DATE END) &quot;CO_DATE&quot; 
              From        tbl
              Group By    ID
            ) tmax
INNER JOIN  tbl twk ON(twk.ID = tmax.ID And twk.UPD_DATE = tmax.WK_DATE and twk.CODE = &#39;WK&#39;)
INNER JOIN  tbl tco ON(tco.ID = tmax.ID And tco.UPD_DATE = tmax.CO_DATE and tco.CODE = &#39;CO&#39;)

With your sample data:

WITH
	tbl (ID,	UPD_DATE,	CODE,	ADDRESS1,	ADDRESS2,	CITY,	STATE_CODE,	ZIP_CODE) AS
		(
			Select 1, To_Date(&#39;2023/02/15&#39;, &#39;yyyy/mm/dd&#39;), &#39;CO&#39;, &#39;Apt 320&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All 
			Select 1, To_Date(&#39;2021/12/03&#39;, &#39;yyyy/mm/dd&#39;), &#39;CO&#39;, &#39;Apt 105&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All
			Select 1, To_Date(&#39;2023/02/15&#39;, &#39;yyyy/mm/dd&#39;), &#39;WK&#39;, &#39;5th Avenue&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All
			Select 2, To_Date(&#39;2023/02/15&#39;, &#39;yyyy/mm/dd&#39;), &#39;CO&#39;, &#39;Apt 325&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All
			Select 2, To_Date(&#39;2022/01/12&#39;, &#39;yyyy/mm/dd&#39;), &#39;CO&#39;, &#39;Apt 123&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All
			Select 2, To_Date(&#39;2023/02/14&#39;, &#39;yyyy/mm/dd&#39;), &#39;WK&#39;, &#39;4th Avenue&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual Union All
			Select 2, To_Date(&#39;2021/02/11&#39;, &#39;yyyy/mm/dd&#39;), &#39;WK&#39;, &#39;5th Avenue&#39;, 	Null, &#39;NYC&#39;, &#39;NY&#39;, 10012 From Dual 
		)

... you should get this as the result


        ID TENANT_ADDRESS                        
---------- --------------------------------------
         1 5th Avenue, Apt 320, NYC, NY, 10012   
         2 4th Avenue, Apt 325, NYC, NY, 10012 

huangapple
  • 本文由 发表于 2023年2月16日 16:28:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/75469557.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定