SQL Server多表查询

SQL Server多表查询

2009-08-20 20:14

--从titles和publishers表查询每本书的书名和它的出版社名称 SELECT title,pub_name FROM titles,publishers WHERE titles.pub_id=publishers.pub_id --从titles和publishers表查询每个出版社出版的所有图书的平均单价（采用表的别名方式） SELECT pub_name,AVG(price) AS avgprice FROM titles AS t,publishers AS p WHERE t.pub_id=p.pub_id GROUP BY pub_name --从titles和publishers表查询所有出版社的图书出版情况（包括查询出尚未出版过图书的出版社） SELECT pub_name,title,price FROM publishers LEFT JOIN titles ON publishers.pub_id=titles.pub_id --从sales和titles表中查询所有图书的出售情况（采用右链接查询包括尚未出售过的图书） SELECT title,ord_num,ord_date,qty FROM sales RIGHT JOIN titles ON sales.title_id=titles.title_id --从authors和publishers表中查询出所有的city和state SELECT city,state FROM authors UNION SELECT city,state FROM publishers --从titles表中查询所有图书的出版社编号pub_id小于出版社名称pub_name为Ramona Publishers的出版社编号pub_id SELECT * FROM titles WHERE pub_id <(SELECT pub_id FROM publishers WHERE pub_name='Ramona Publishers') --从titles表中查询所有在publishers表中存在对应出版社的图书（采用IN子查询实现） SELECT * FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers) --从titles表中查询已经出售过（存在于sales表）的图书（采用EXISTS子查询实现） SELECT * FROM titles WHERE EXISTS(SELECT * FROM sales WHERE title_id=titles.title_id) --从titles表中查询所有单价低于平均单价的图书，并存放到一张临时表中 SELECT * INTO #T FROM titles WHERE price < (SELECT AVG(price) FROM titles) SELECT * FROM #T DROP TABLE #T