英文:
need to match data of one table with another
问题
需要将一个表的数据与另一个表匹配。第一张表只有一行，第二张表根据日历有多行。
表格详情：-
第一张表：-
A B C D E G600 MT_000 6765 29-04-23 30-04-23 WEEKMT 6758 01-05-23 31-05-23 MONTH601 MT_0001 6771 01-06-23 30-06-23 WEEK
第二张表：-
A B C D601 MT 6758 07-05-23601 MT 6758 14-05-23601 MT 6758 21-05-23601 MT 6758 28-05-23601 MT 6758 28-05-23601 MT 6758 04-06-23
希望的数据如下：
A B C D E601 MT 6758 07-05-23 MONTH601 MT 6758 14-05-23 MONTH601 MT 6758 21-05-23 MONTH601 MT 6758 28-05-23 MONTH601 MT 6758 28-05-23 MONTH601 MT 6758 04-06-23 WEEK
A列的依赖关系应该存在，值也可以为空。
我尝试过连接两个表，但数据不正确。每一行都在与其他行进行比较，导致有18行。
请帮我解决这个问题。如果需要更清晰的信息，您可以提问。
英文:
Need to match data of one table with another. As Table one as only one row and table second has many row as per the calendar.
Table details:-
First table:-
A B C D E G600 MT_000 6765 29-04-23 30-04-23 WEEKMT 6758 01-05-23 31-05-23 MONTH601 MT_0001 6771 01-06-23 30-06-23 WEEK
Second table:-
A B C D601 MT 6758 07-05-23601 MT 6758 14-05-23601 MT 6758 21-05-23601 MT 6758 28-05-23601 MT 6758 28-05-23601 MT 6758 04-06-23
Want data like this :-
A B C D E601 MT 6758 07-05-23 MONTH601 MT 6758 14-05-23 MONTH601 MT 6758 21-05-23 MONTH601 MT 6758 28-05-23 MONTH601 MT 6758 28-05-23 MONTH601 MT 6758 04-06-23 WEEK
Dependency on Column A should be there value can null also.
I tried join both the table but data is not correct. Giving 18 row as each row is comparing with each other.
Please help me with this issue.
For more clearity you may ask q.
答案1
得分: 1
以下是翻译好的部分：
看起来你正在查找table2中的行是否为WEEK或MONTH。如果这是你想要的，请使用如下所示的inner join：
SELECT t2.*, t1.Gfrom table2 t2inner join table1 t1 on t2.D between t1.D and t1.Eorder by t2.A, t1.D
结果：
A B C D G601 MT 6758 07-MAY-23 MONTH601 MT 6758 14-MAY-23 MONTH601 MT 6758 21-MAY-23 MONTH601 MT 6758 28-MAY-23 MONTH601 MT 6758 28-MAY-23 MONTH601 MT 6758 04-JUN-23 WEEK
英文:
Its appear that you are looking for whether the rows in table2 are WEEK or MONTH. If that's what you want, use a inner join as shown below:
SELECT t2.*, t1.Gfrom table2 t2inner join table1 t1 on t2.D between t1.D and t1.Eorder by t2.A, t1.D
Result :
A B C D G601 MT 6758 07-MAY-23 MONTH601 MT 6758 14-MAY-23 MONTH601 MT 6758 21-MAY-23 MONTH601 MT 6758 28-MAY-23 MONTH601 MT 6758 28-MAY-23 MONTH601 MT 6758 04-JUN-23 WEEK
答案2
得分: 1
将两个表根据日期范围（可能还包括b列的起始部分）进行连接：
SELECT t2.*, t1.gFROM table2 t2INNER JOIN table1 t1ON (t1.b LIKE t2.b || '%' AND t2.d BETWEEN t1.d AND t1.e)ORDER BY t2.b, t2.d
对于示例数据：
CREATE TABLE table1 (A, B, C, D, E, G) ASSELECT 600, 'MT_000', 6765, DATE '2023-04-29', DATE '2023-04-30', 'WEEK' FROM DUAL UNION ALLSELECT NULL, 'MT', 6758, DATE '2023-05-01', DATE '2023-05-31', 'MONTH' FROM DUAL UNION ALLSELECT 601, 'MT_0001', 6771, DATE '2023-06-01', DATE '2023-06-30', 'WEEK' FROM DUAL;CREATE TABLE table2 (A, B, C, D) ASSELECT 601, 'MT', 6758, DATE '2023-05-07' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-14' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-21' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-28' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-28' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-06-04' FROM DUAL;
输出：
| A | B | C | D | G |
|---|---|---|---|---|
| 601 | MT | 6758 | 2023-05-07 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-14 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-21 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-28 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-28 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-06-04 00:00:00 | WEEK |
英文:
JOIN the two tables on the date range (and, maybe, the start of the b columns):
SELECT t2.*, t1.gFROM table2 t2INNER JOIN table1 t1ON (t1.b LIKE t2.b || '%' AND t2.d BETWEEN t1.d AND t1.e)ORDER BY t2.b, t2.d
Which, for the sample data:
CREATE TABLE table1 (A, B, C, D, E, G) ASSELECT 600, 'MT_000', 6765, DATE '2023-04-29', DATE '2023-04-30', 'WEEK' FROM DUAL UNION ALLSELECT NULL, 'MT', 6758, DATE '2023-05-01', DATE '2023-05-31', 'MONTH' FROM DUAL UNION ALLSELECT 601, 'MT_0001', 6771, DATE '2023-06-01', DATE '2023-06-30', 'WEEK' FROM DUAL;CREATE TABLE table2 (A, B, C, D) ASSELECT 601, 'MT', 6758, DATE '2023-05-07' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-14' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-21' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-28' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-05-28' FROM DUAL UNION ALLSELECT 601, 'MT', 6758, DATE '2023-06-04' FROM DUAL;
Outputs:
| A | B | C | D | G |
|---|---|---|---|---|
| 601 | MT | 6758 | 2023-05-07 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-14 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-21 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-28 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-05-28 00:00:00 | MONTH |
| 601 | MT | 6758 | 2023-06-04 00:00:00 | WEEK |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论