主表
id name money
1 n1 27
2 n2 51
……
从表
id food
1 f1
1 f2
1 f3
2 f2
2 f7
……
想得到下面的结果
id,name money food
1 n1 27 f1
f2
f3
2 n2 52 f2
f7
……
关键就是，在结果里，主表重复的东西不显示出来。
------解决方案--------------------
你这种展现方式sql做不到，查询出数据然后前端处理吧
------解决方案--------------------
select 1 as id,'n1' as name,27 as money into t1
insert into t1 select 2,'n2',51
select 1 as id,'f1' as food into t2
insert into t2
select 1,'f2' union
select 1,'f3' union
select 2,'f2' union
select 2,'f7'
select t1.id,t1.name,t1.money,t2.food into t3 from t2 left join t1 on t2.id=t1.id
select ids=case when exists (select 1 from t3 where id=a.id and food<a.food) then '' else id end,
name=case when exists (select 1 from t3 where id=a.id and food<a.food) then '' else name end,
money=case when exists (select 1 from t3 where id=a.id and food<a.food) then '' else money end,
food
from t3 a order by id,food
ids name money food
1 n1 27 f1
0 0 f2
0 0 f3
2 n2 51 f2
0 0 f7