英文:
How to only return a calculated value to results after a SQL join?
问题
我有两个表，我只想从对table2的查询中获取一个起始日期，然后添加到table1的值中。起始日期是通过GROUP BY语句推导出来的，例如：
表1
| ID | 摘要 | 结束日期 |
|---|---|---|
| 1 | 标题 | 2023/01/01 |
| 2 | 标题 2 | 2023/02/02 |
表2
| ID | 摘要 | 起始日期 | 父级 ID |
|---|---|---|---|
| 3 | 标题 | 2022/01/10 | 1 |
| 4 | 标题 2 | 2023/02/10 | 1 |
| 5 | 标题 3 | 2023/03/10 | 2 |
我希望从表2中每个父级ID的最小起始日期获得表1的所有内容。
我尝试了类似这样的查询，但无法返回额外的列，我猜想我需要更改GROUP BY子句中的某些内容：
SELECT table1.*, t2.Start
FROM table1 INNER JOIN
(
SELECT [父级 ID], min(起始日期)
FROM table2
GROUP BY [父级 ID]
) t2 ON t2.[父级 ID] = table1.ID
英文:
I have two tables and I only want a start date from a query on table2 to be added to the values on table1. The start date is deduced with a GROUP BY statement, for example:
Table 1
| ID | Summary | End |
|---|---|---|
| 1 | Heading | 01/01/2023 |
| 2 | Heading 2 | 02/02/2023 |
Table 2
| ID | Summary | Start | Parent ID |
|---|---|---|---|
| 3 | Heading | 01/10/2022 | 1 |
| 4 | Heading 2 | 02/10/2023 | 1 |
| 5 | Heading 3 | 03/10/2023 | 2 |
I would like to have all of table 1 with just the minimum start date for each Parent ID from Table 2.
I tried something like this but I can't get the extra columns to return, I'm guessing I need to change something with the GROUP BY clause?
SELECT table1.*, t2.Start
FROM table1 INNER JOIN
(
SELECT [Parent ID], min(Start)
FROM table2
GROUP BY [Parent ID]
) t2 ON t2.[Parent ID] = t1.ID
答案1
得分: 1
SELECT t1.*, t2.Start
FROM table1 t1 INNER JOIN
(
SELECT [Parent ID], MIN(Start) AS Start
FROM table2
GROUP BY [Parent ID]
) t2 ON t2.[Parent ID] = t1.ID
英文:
SELECT t1.*, t2.Start
FROM table1 t1 inner join
(
select [Parent ID], min(Start) as Start
from table2
group by [Parent ID]
) t2 on t2.[Parent ID] = t1.ID
答案2
得分: 1
另一种方法是使用 GROUP BY 和聚合函数 min() 来执行：
select t1.ID, max(t1.Summary) as 摘要, max(t1.End) as 结束, min(t2.Start_) as 最小开始时间
from table1 t1
inner join table2 t2 on t1.id = t2.Parent_ID
group by t1.ID
英文:
An other way to do it is by using GROUP BY and the aggregation function min() :
select t1.ID, max(t1.Summary) as Summary, max(t1.End) as End, min(t2.Start_) as min_start
from table1 t1
inner join table2 t2 on t1.id = t2.Parent_ID
group by t1.ID
答案3
得分: 0
;with a as(
Select row_number() over (PARTITION BY Table1.id order by table2.start) as 'row',table1.*,table2.start)
from table1 left join table2 on table1.id=table2.id)
select * from a where row=1
英文:
;with a as(
Select row_number() over (PARTITION BY Table1.id order by table2.start) as 'row',table1.*,table2.start)
from table1 left join table2 on table1.id=table2.id)
select * from a where row=1
答案4
得分: 0
An alternative solution rather than using a GROUP BY clause is to fetch the result within a subquery instead.
SELECT t1.*, (
SELECT min(Start)
FROM table2 t2
WHERE t2.ParentID = t1.ID
)
FROM table1 t1;
英文:
An alternative solution rather than using a GROUP BY clause is to fetch the result within a subquery instead.
SELECT t1.*, (
SELECT min(Start)
FROM table2 t2
WHERE t2.ParentID = t1.ID
)
FROM table1 t1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论