select custom-id,name,
goods1=(select top 1 goods from 从表
where 从表.custom-id = 主表.custom-id),
goods2=(select top 1 goods from 从表
where 从表.custom-id = 主表.custom-id
and 从表.goods not in(select top 1 goods
from 从表
where 从表.custom-id = 主表.custom-id)
)
from 主表(将忽略重复的goods字段)
goods1=(select top 1 goods from 从表
where 从表.custom-id = 主表.custom-id),
goods2=(select top 1 goods from 从表
where 从表.custom-id = 主表.custom-id
and 从表.goods not in(select top 1 goods
from 从表
where 从表.custom-id = 主表.custom-id)
)
from 主表(将忽略重复的goods字段)
the same to you,go and look.
然后依次输出主从表的各字段
from 主表 as A left join
(select custom-id,goods from 从表 X
where goods = (select min(goods) from 从表
where custom-id = X.custom-id)) as B
on A.custom-id = B.custon-id left join
(select custom-id,goods from 从表 Y
where goods = (select min(goods) from 从表
where custom-id = Y.custom-id
and goods <> (select min(goods) from 从表
where custom-id = Y.custom-id)) as C
on A.custom-id = C.custon-id