我有一个表,我试图复制它的每一行,但其中一列的值不同。问题是获取该值涉及到另一个没有当前关系的表。
表格搜索:
SearchID | 名称 |
---|---|
1 | A 1 |
2 | B1 |
3 | C1 |
4 | A 2 |
5 | B 2 |
6 | C2 |
我想我想明白了。
insert into searchfields(searchid, Foo)
select
(
select top 1 searchid from search where searchid =
(select top 1 max(s.searchid)
from search s
group by
substring(name, 1, len(name) - charindex(' ', reverse(name)))
having sf.searchid = min(s.searchid))
) [SearchId], Foo
from searchfields sf
order by searchid