| ylbtech-SQL Server: SQL Server- SQL表的高级查询-子查询 |
SQL Server 表的高级查询-子查询。
--================================-- ylb:表的高级查询-子查询-- pubs库的练习-- 12/12/2011--================================use pubsgoselect * from authorsselect * from titleauthorselect * from titlesselect * from publishersselect * from storesgo--1. 查找和出版商同一州的作者姓名。select * from authors awhere state in(select state from publishers where state=a.state)goselect * from authors awhere exists(select * from publishers where state=a.state)go--2. 查找和商店同一州的作者姓名select * from authors awhere state in(select state from stores where state=a.state)go--3. 查找和商店同一城市的出版社名称select * from publishers pwhere city in (select city from stores where city=p.city)go--4. 查找写商业书的作者名select * from authorsselect * from titleauthorselect * from titlesgo--4_1,select title_id from titleswhere type='business'go--4_2,select au_id from titleauthorwhere title_id in('BU1032','BU1111','BU2075','BU7832')go--4_3,select * from authorswhere au_id in('213-46-8915','267-41-2394')go--4,结论select * from authorswhere au_id in(select au_id from titleauthorwhere title_id in(select title_id from titleswhere type='business'))go--5. 查找美国出版社出版的所有书select * from publishersselect * from titlesgo--5_1,select pub_id from publisherswhere country='USA'go--5_2,select * from titleswhere pub_id in('0877','0736')go--5结论select * from titleswhere pub_id in(select pub_id from publisherswhere country='USA')go--6. 查找美国出版社出版书的作者姓名--6_1,select pub_id from publisherswhere country='USA'go--6_2,select title_id from titleswhere pub_id in('0877','0736')go--6_3,select au_id from titleauthorwhere title_id in('BU2075','MC2222')go--6_4,select * from authorswhere au_id in('213-46-8915','712-45-1867')go--6总结select * from authorswhere au_id in(select au_id from titleauthorwhere title_id in(select title_id from titleswhere pub_id in(select pub_id from publisherswhere country='USA')))go--7. 查找在CA州出版社所出版的商业书作者姓名--7-1,select pub_id from publisherswhere state='CA'go--7-2,select title_id from titleswhere pub_id in('1389')and [type]='business'go--7-3,select au_id from titleauthorwhere title_id in('BU1032','BU1111')go--7-4,select * from authorswhere au_id in('213-46-8915','409-56-7008')go--7总结select * from authorswhere au_id in(select au_id from titleauthorwhere title_id in(select title_id from titleswhere pub_id in(select pub_id from publisherswhere state='CA')and [type]='business'))go--P:8. 查找和出版社在同一州的作者所写的书名--8_1,select au_id from authors awhere state in(select state from publishers where state=a.state)go--8-2,select title_id from titleauthorwhere au_id in(select au_id from authors awhere state in(select state from publishers where state=a.state))go--8-3,select * from titleswhere title_id in(select title_id from titleauthor)go--8 结论select * from titleswhere title_id in(select title_id from titleauthorwhere au_id in(select au_id from authors awhere state in(select state from publishers where state=a.state)))go--9. 查找和作者在同一城市的出版社名称select * from publishers pwhere city in(select city from authors where city=p.city)go--10. 查找单价大于所有商业书的书，它的作者姓名--方法一、--10-1,select MAX(price) from titles where type='business'go--10-2a,select title_id from titleswhere price >(select MAX(price) from titles where type='business')go--10-2b,select title_id from titleswhere price > all(select price from titles where type='business')go--10-3,select au_id from titleauthorwhere title_id in(select title_id from titleswhere price >(select MAX(price) from titles where type='business'))go--10总结select * from authorswhere au_id in(select au_id from titleauthorwhere title_id in(select title_id from titleswhere price >(select MAX(price) from titles where type='business')))go--11. 查找（Algodata Infosystems）出版社所在州，出过商业书的作者姓名--11_1,select pub_id from publisherswhere pub_name='Algodata Infosystems'go--11-2,select title_id from titleswhere type='business' and pub_id =(select pub_id from publisherswhere pub_name='Algodata Infosystems')go--11-3,select au_id from titleauthorwhere title_id in(select title_id from titleswhere pub_id =(select pub_id from publisherswhere pub_name='Algodata Infosystems'))go--11-4,select * from authorswhere au_id in(select au_id from titleauthorwhere title_id in(select title_id from titleswhere type='business' and pub_id =(select pub_id from publisherswhere pub_name='Algodata Infosystems')))  | 作者：出处：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 |