比如a:
id name
1 tom
2 jack
3 jerry
b:
id record
1 good
1 very good
3 modest
我要的查询是 能把a表中的都显示出来,如果b中有对应纪录,也一同显示,且对于b中重复纪录只显示一条
查询结果为:
1 tom good
2 jack
3 jerry modest
该怎么写sql语句啊,我研究好久了。多谢!
id name
1 tom
2 jack
3 jerry
b:
id record
1 good
1 very good
3 modest
我要的查询是 能把a表中的都显示出来,如果b中有对应纪录,也一同显示,且对于b中重复纪录只显示一条
查询结果为:
1 tom good
2 jack
3 jerry modest
该怎么写sql语句啊,我研究好久了。多谢!
select
a.*,
(select top 1 record from tableb where id=a.id)
from
tablea a
马上给分!
insert into @a select 1,'tom'
union all select 2,'jack'
union all select 3,'jerry'
declare @b table([id] int,name varchar(20))
insert into @b select 1,'good'
union all select 1,'very good'
union all select 3,'modest'
select a.id,name=a.name+' '+isnull((select top 1 name from @b b where a.id=b.id),'')
from @a a
如果表b中再多一列b:
id record recordtime
1 good 2000-9-9
1 very good 2000-1-1
3 modest 2000-2-2
1楼给的语句 (select top 1 record from tableb where id=a.id)现在多了recordtime,
当我写成(select top 1 record,recordtime from tableb where id=a.id)
会有错误提示:
服务器: 消息 116,级别 16,状态 1,行 8
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。只能选择1列?这可怎么解决?
insert into @a select 1,'tom'
union all select 2,'jack'
union all select 3,'jerry'
--
declare @b table([id] int,record varchar(20),recorddate datetime)
insert into @b select 1,'good','2000-9-9'
union all select 1,'very good','2001-1-1'
union all select 3,'modest','2002-2-2'Select A.*,B.Record,B.RecordDate
From @a A Left Join
( Select Id,Min(Record) As 'Record',Min(Recorddate) As 'Recorddate' From @b Group By Id) B
On A.id=B.id
declare @t table(id int,name varchar(10))
declare @t2 table(id int,record varchar(10),recordtime datetime)
insert @t
select 1,'tom' union all
select 2,'jack' union all
select 3,'jerry'
insert @t2
select 1,'record','2000-9-9' union all
select 1,'good','2000-1-1' union all
select 3,'modest','2000-2-2'select a.*,
record = (select top 1 record from @t2 where id = a.id),
recordtime = (select top 1 recordtime from @t2 where id = a.id)
from @t a
try:
select a.id,
a.name,
max(b.record) as record
from a
left join b
on a.id = b.id
group by a.d ,a.name