日期:2014-05-18  浏览次数:20712 次

急!!!关于联合查询的问题!!!
求1条Sql语句
a ,b2个表
a表数据
id name
1 力量
2 往往
3 饿额
b表数据
id title key
1 亲切 WW
1 常常 DD
1 爸爸 CC
2 天天 GG
2 哦哦 BB
3 批评 NN
3 可靠 MM
现在用Sql语句
要得出下面的结果(id列关联2个表)
id title key
1 亲切 WW
2 天天 GG
3 批评 NN
结果不限定为每个ID的第1条记录,但是ID必须是对应的
也可以是

id title key
1 常常 DD
2 哦哦 BB
3 可靠 MM


------解决方案--------------------
SQL code
select * from b a where not exists(select 1 from b where id=a.id and title>a.title)

------解决方案--------------------
SQL code
select * from b
where not exists(select 1 from b t where id=b.id and title>b.title)

------解决方案--------------------
SQL code
select * from b a where not exists(select 1 from b where id=a.id and key <a.key)

------解决方案--------------------
SQL code
select * 
from b a 
where not exists(select 1 
          from b 
          where id=a.id and title>a.title)

------解决方案--------------------
SQL code

declare @a table(id int,name nvarchar(20))
insert into @a
select 1,N'力量'
union
select 2,N'往往'
union
select 3,N'饿额'

declare @b table(id int,title nvarchar(10),key1 varchar(10))
insert into @b
select 1,N'亲切','WW'
union
select 1,N'常常','DD'
union
select 1,N'爸爸','CC'
union
select 2,N'天天','GG'
union
select 2,N'哦哦','BB'
union
select 3,N'批评','NN'
union
select 3,N'可靠','MM'

select * from @b g
where not exists(select 1 from @b f where f.id=g.id and f.title>g.title)
and g.id in (select id from @a)

------解决方案--------------------
SQL code

declare @a table (id int,name varchar(10))
insert into @a select 1,'力量'
insert into @a select 2,'往往'
insert into @a select 3,'饿额'
declare @b table (id int,title varchar(10),[key] varchar(10))
insert into @b select 1,'亲切','WW'
insert into @b select 1,'常常','DD'
insert into @b select 1,'爸爸','CC'
insert into @b select 2,'天天','GG'
insert into @b select 2,'哦哦','BB'
insert into @b select 3,'批评','NN'
insert into @b select 3,'可靠','MM'

select * from @a a,(
select id,
title=(select top 1 title from @b where id=b.id order by newid()),
[key]=(select top 1 [key] from @b where id=b.id order by newid())
 from @b b group by id) b
where a.id=b.id

------解决方案--------------------
newid()
------解决方案--------------------
SQL code

select a.id,b1.title,b1.[key]
from a left join b b1 on a.id=b1.id where 
(select count(1) from b where [key]<=b1.[key] and id=b1.id)=
(select count(1) from b where id=b1.id)-1

------解决方案--------------------

SQL code
select a.id,(Select top 1 title from @b where id = a.id) title,(select top 1 key1 from @b where id = a.id) key1
from @a a