如何在Oracle SQL中使用`select distinct`与另一张表进行连接?

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

How do I use select distinct with a join on another table in Oracle SQL?

问题

以下是您要翻译的部分:

Desired results:

WO Person Created
280 W123 20220101
299 E201 20200531
300 R360 20221115

Thanks to Paul W - the below code works:

Select /*+ full(wrk) parallel(8) USE_HASH(wrk str) */
      WRK.WO, WRK.PERSON, STR.CREATED
FROM WRK
JOIN (SELECT /*+ NO_MERGE */
           wo,
           MAX(created) created
      FROM str
     GROUP BY wo) STR on WRK.WO = STR.WO
WHERE WRK.STATE = 'PA'
英文:

I'm fairly new to SQL and only have read access to the database that I'm using. I'm trying to query the number of work orders (WO) in the table WRK that are in the state of PA (and yes it is labeled as state PA in the database, not pA or Pa). I also need the date the work order was created, which comes from a different table, table STR in the column CREATED. When I run the below code, I get duplicate work orders all with the same data, which ends up being 3 million+ records and takes some time to pull from the database. I imagine Select Distinct is the way I need to go, but could be wrong. I've tried searching on here and a couple other places for code that would do what I need to do, which is return only 1 work order instead of multiple duplicate records. I also need the person who created the work order "WRK.PERSON", and this will always be the same person for any given work order. The join is throwing everything off, but I can't find a way around it. Thank you in advance!

Code:

Select WRK.WO, WRK.PERSON, STR.CREATED
FROM WRK
JOIN STR on WRK.WO = STR.WO
WHERE WRK.STATE = 'PA'

Current results:

WO Person Created
280 W123 20220101
280 W123 20220101
280 W123 20220101
280 W123 20220101
299 E201 20200531
299 E201 20200531
299 E201 20200531
300 R360 20221115
300 R360 20221115
300 R360 20221115

Desired results:

WO Person Created
280 W123 20220101
299 E201 20200531
300 R360 20221115

Thanks to Paul W - the below code works:

Select /*+ full(wrk) parallel(8) USE_HASH(wrk str) */
      WRK.WO, WRK.PERSON, STR.CREATED
FROM WRK
JOIN (SELECT /*+ NO_MERGE */
           wo,
           MAX(created) created
      FROM str
     GROUP BY wo) STR on WRK.WO = STR.WO
WHERE WRK.STATE = 'PA'

答案1

得分: 1

这对你是否有效?主要是确保你不要进行多对多的连接,至少在连接列的一侧减少到唯一性。我还提供了一些提示,以帮助处理性能问题。

Select /*+ full(wrk) parallel(8) USE_HASH(wrk str) */
       WRK.WO, WRK.PERSON, STR.CREATED
FROM WRK
JOIN (SELECT /*+ NO_MERGE */
             wo,
             MAX(created) created
        FROM str
       GROUP BY wo) STR on WRK.WO = STR.WO
WHERE WRK.STATE = 'PA'
英文:

Will this work for you? Main thing is ensuring that you don't have a many-to-many join by reducing at least one side to uniqueness on the join column. I also threw in some hints you may want given the volume of data to help deal with the performance.

Select /*+ full(wrk) parallel(8) USE_HASH(wrk str) */
      WRK.WO, WRK.PERSON, STR.CREATED
FROM WRK
JOIN (SELECT /*+ NO_MERGE */
           wo,
           MAX(created) created
      FROM str
     GROUP BY wo) STR on WRK.WO = STR.WO
WHERE WRK.STATE = 'PA'

答案2

得分: 1

以下是翻译好的部分:

WITH
	tbl_wrk AS 
		(
			Select 280 "WO", 'W123' "PERSON", 'PA' "STATE" From Dual Union All
			Select 299 "WO", 'E201' "PERSON", 'PA' "STATE" From Dual Union All
			Select 300 "WO", 'R360' "PERSON", 'PA' "STATE" From Dual Union All
      --
			Select 180 "WO", 'Q333' "PERSON", 'NY' "STATE" From Dual Union All
			Select 220 "WO", 'K123' "PERSON", 'NY' "STATE" From Dual 
		),
	tbl_str AS
		(
			Select 280 "WO", '20220101' "CREATED" From Dual Union All
            Select 280 "WO", '20220101' "CREATED" From Dual Union All
            Select 280 "WO", '20220101' "CREATED" From Dual Union All
      --
			Select 299 "WO", '20200531' "CREATED" From Dual Union All
            Select 299 "WO", '20200531' "CREATED" From Dual Union All
            Select 299 "WO", '20200531' "CREATED" From Dual Union All
      --
			Select 300 "WO", '20221115' "CREATED" From Dual Union All
            Select 300 "WO", '20221115' "CREATED" From Dual Union All
            Select 300 "WO", '20221115' "CREATED" From Dual Union All
      --
			Select 180 "WO", '20190318' "CREATED" From Dual Union All
			Select 220 "WO", '20210109' "CREATED" From Dual 
		)
SELECT		  w.WO, w.PERSON, Max(CREATED) "CREATED"
FROM 		  tbl_wrk w
INNER JOIN    tbl_str s ON(s.WO = w.WO)
WHERE 		  w.STATE = 'PA'
GROUP BY      w.WO, w.PERSON
ORDER BY      w.WO

        WO PERSON CREATED
---------- ------ --------
       280 W123   20220101 
       299 E201   20200531 
       300 R360   20221115
英文:
WITH
	tbl_wrk AS 
		(
			Select 280 "WO", 'W123' "PERSON", 'PA' "STATE" From Dual Union All
			Select 299 "WO", 'E201' "PERSON", 'PA' "STATE" From Dual Union All
			Select 300 "WO", 'R360' "PERSON", 'PA' "STATE" From Dual Union All
      --
			Select 180 "WO", 'Q333' "PERSON", 'NY' "STATE" From Dual Union All
			Select 220 "WO", 'K123' "PERSON", 'NY' "STATE" From Dual 
		),
	tbl_str AS
		(
			Select 280 "WO", '20220101' "CREATED" From Dual Union All
            Select 280 "WO", '20220101' "CREATED" From Dual Union All
            Select 280 "WO", '20220101' "CREATED" From Dual Union All
      --
			Select 299 "WO", '20200531' "CREATED" From Dual Union All
            Select 299 "WO", '20200531' "CREATED" From Dual Union All
            Select 299 "WO", '20200531' "CREATED" From Dual Union All
      --
			Select 300 "WO", '20221115' "CREATED" From Dual Union All
            Select 300 "WO", '20221115' "CREATED" From Dual Union All
            Select 300 "WO", '20221115' "CREATED" From Dual Union All
      --
			Select 180 "WO", '20190318' "CREATED" From Dual Union All
			Select 220 "WO", '20210109' "CREATED" From Dual 
		)
SELECT		  w.WO, w.PERSON, Max(CREATED) "CREATED"
FROM 		  tbl_wrk w
INNER JOIN    tbl_str s ON(s.WO = w.WO)
WHERE 		  w.STATE = 'PA'
GROUP BY      w.WO, w.PERSON
ORDER BY      w.WO

        WO PERSON CREATED
---------- ------ --------
       280 W123   20220101 
       299 E201   20200531 
       300 R360   20221115

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

发表评论

匿名网友

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

确定