select A.*,B.tel from 主表 A inner join (select id,max(tel) as tel from 子表 group by id) B on A.id=B.id or: select A.*,B.tel from 主表 A inner join (select id,min(tel) as tel from 子表 group by id) B on A.id=B.id
create table ttt( id varchar(10), name varchar(10) ) create table www( id varchar(10), tel varchar(20) ) insert into ttt values('01','XXX') insert into ttt values('02','YYY')insert into www values('01','123456') insert into www values('01','13800018000') insert into www values('02','5869453')select a.*,b.tel from ttt a,(select id,max(tel) tel from www group by id)b where a.id=b.id
select a.id,name,max(tel) from ttt a,www b where a.id=b.id group by a.id,name
应该是这样的吧. select * from (select a.ID,a.NAME, b.Tel from 主表 a, 子表 b where a.id=b.id and a.id='01' ) kk order by newid()
create table c(id varchar(5),name varchar(5)) insert into c select '01','xxx' union all select '02','yyy'create table d(id varchar(5),Tel varchar(20)) insert into d select '01','123456' union all select '01','13800018000' union all select '02','5869453'select top 1 c.id,d.tel from c,d where c.id=d.id and c.id='01' order by newid()
(select id,max(tel) as tel from 子表 group by id) B on A.id=B.id
or:
select A.*,B.tel from 主表 A inner join
(select id,min(tel) as tel from 子表 group by id) B on A.id=B.id
id varchar(10),
name varchar(10)
)
create table www(
id varchar(10),
tel varchar(20)
)
insert into ttt values('01','XXX')
insert into ttt values('02','YYY')insert into www values('01','123456')
insert into www values('01','13800018000')
insert into www values('02','5869453')select a.*,b.tel
from ttt a,(select id,max(tel) tel from www group by id)b
where a.id=b.id
where a.id=b.id
group by a.id,name
select *
from (select a.ID,a.NAME, b.Tel
from 主表 a, 子表 b
where a.id=b.id and a.id='01'
) kk
order by newid()
insert into c
select '01','xxx'
union all select '02','yyy'create table d(id varchar(5),Tel varchar(20))
insert into d
select '01','123456'
union all select '01','13800018000'
union all select '02','5869453'select top 1 c.id,d.tel from c,d where c.id=d.id and c.id='01'
order by newid()
当子表有多条数据时,怎样只随机显示一条。如:
--按照楼主这种意思,应该不是 Yisa() 的意思
from 主表 t1 inner join (select top 1 * from 子表 order by newid()) t2
on t1.id=t2.id
where t1.id='01'
from 主表 a left join (select ID,max(Tel) as Tel from 子表 group by id) b
on a.id=b.id