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

◤◤◤两个小问题,每个30分,先答对者有分◢◢◢
Q1:

两张表关联,生成一张临时表,其中有一字段是自动增加列
下面的sql语句生成的Id是不正确的,应该如何写才正确?
select id = identity(int,1,1),a.isbn,a.qty,b.qty
into #
from ta b,tb a
where a.isbn = b.isbn
order by a.isbn



Q2:
主从表关联,(从表按条件已排序),如何只返回第一条从表记录?


请指教,谢谢!

------解决方案--------------------
q1.
SQL code

create table ta 
(id int identity(1,1), 
slot varchar(50), 
isbn varchar(50), 
qty int) 

create table tb 
(isbn varchar(50), 
qty int) 


insert ta(slot,isbn,qty) 
select  'a ', '001 ',10 
union all 
select  'b ', '002 ',5 
union all 
select  'c ', '001 ',6 
union all 
select  'd ', '001 ',6 
union all 
select  'e ', '003 ',8 
union all 
select  'f ', '002 ',10 
union all 
select  'd ', '004 ',8 

insert tb(isbn,qty) 
select  '001 ',12 
union all 
select  '002 ',8 
union all 
select  '003 ',7 

select id=identity(int),a.slot,a.isbn,a.qty qty_ta,c.qty qty_tb 
    into #temp
    from ta a
inner join 
    (select min(id) mi from ta group by slot) b
    on id=mi
inner join tb c
    on c.isbn=a.isbn order by a.isbn
select * from #temp
drop table ta,tb,#temp

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

SQL code

select mi id_ta,a.slot,a.isbn,a.qty qty_ta,c.qty qty_tb 
    from tb c
inner join ta a
    on a.isbn=c.isbn
inner join 
    (select min(id) mi from ta group by isbn) b
    on a.id=mi
    order by a.isbn