英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论