select * from book b where not exists(select * from book where type=b.type and price<20)
--> liangCK小梁 于2008-09-09 --> 生成测试数据: #book if object_id('tempdb.dbo.#book') is not null drop table #book create table #book (type nvarchar(6),name nvarchar(3),price int) insert into #book select '计算机','书1',12 union all select '计算机','书2',38 union all select '经管','书3',50--SQL查询如下:select * from #book b where not exists(select * from #book where type=b.type and price <20)/* type name price ------ ---- ----------- 经管 书3 50(1 行受影响) */
SELECT TYPE FROM BOOK WHERE PRICE>20 GROUP BY TYPE
好像看错题目了 应该是liangCK 的写法
select * from book where type not in (select type from book where price <= 20)
select distinct type from book b where not exists(select * from book where type=b.type and price <20)
from book b
where not exists(select * from book where type=b.type and price<20)
--> 生成测试数据: #book
if object_id('tempdb.dbo.#book') is not null drop table #book
create table #book (type nvarchar(6),name nvarchar(3),price int)
insert into #book
select '计算机','书1',12 union all
select '计算机','书2',38 union all
select '经管','书3',50--SQL查询如下:select *
from #book b
where not exists(select * from #book where type=b.type and price <20)/*
type name price
------ ---- -----------
经管 书3 50(1 行受影响)
*/
应该是liangCK 的写法
select distinct type
from book b
where not exists(select * from book where type=b.type and price <20)