随笔- 97  评论- 70  文章- 0 

連接兩表sql語句,二種寫法,sql面試題目

 select a.bookname,b.categoryname
from bk_bookname a,bk_category b

where a.categoryid=b.id

(一般用于mysql中,相當于inner join[內連接])

 

    

SELECT     bk_bookname.bookname, bk_category.categoryname

FROM         bk_bookname inner JOIN

             bk_category ON bk_bookname.categoryid = bk_category.id


注意 right join 、left join 的區別

 

 

A

           

B


 

1. SELECT * FROM A  JOIN  B  ON A.Id = B.Id    將顯示 9     條數據。              Inner join(等值连接)只返回两个表中联结字段相等的行

 

 

2 SELECT * FROM A  LEFT JOIN  B  ON A.Id = B.Id      將顯示 12  條數據。              (left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记


                                   

3 SELECT * FROM A  RIGHT JOIN  B  ON A.Id = B.Id   將顯示 10  條數據。       (right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录)

   

 

4. SELECT * FROM A,B WHERE  A.Id = B.Id

 等同于內聯接

 

5. 找出A表,在Age 1820之間的記錄

 SELECT * FROM  A    WHERE  (Age BETWEEN 18 AND 20)

 

(如果 test_expression 的值大于或等于 begin_expression 的值并且小于或等于 end_expression 的值,则 BETWEEN 返回 TRUE)


6. 找出單科分數前二位同學的姓名

   SELECT [Name] FROM A join

(SELECT TOP (2) Id, MAX(Score) AS score FROM B GROUP BY Id ORDER BY score DESC) as AA

on A.id=AA.id

 


 

 

7. 寫一個存儲過程,要求輸入ID找出該ID對應的姓名和最高分數,返回name:score

 如:張六:90

 

alter proc [sp_totalscore]

@id int,

@return nvarchar(100) output

as

begin

    declare @name nvarchar(50)

    declare @score int

    select top 1 @name=a.name,@score=b.score

    from a join b on a.id=b.id

    where a.id =@id

    order by b.score desc

    set @return=@name+':'+cast(@score as nvarchar(50))

end

 

 

 

//打印

declare @return nvarchar(50)

execute sp_totalscore 6,@return output

print @return

 

posted on 2008-10-24 16:57 感動常在 阅读(...) 评论(...) 编辑 收藏