27,579
社区成员
发帖
与我相关
我的任务
分享
select a.* from See a
where exists (select 1 from Kontinent c where a.Land=c.Land and c.Erdteil='Afrika')
and not exists (select 1 from See where Name=a.Name and Land=a.Land and Tiefe>a.Tiefe)
order by tiefe desc
select a.* from See a
where exists (select 1 from Kontinent c where a.Land=c.Land and c.Erdteil='Afrika')
and not exists (select 1 from See where Name=a.Name and Land=a.Land and Tiefe>a.Tiefe)
select a.* from See a, See b where exists
(select 1 from Kontinent c where a.Land=c.Land and c.Erdteil='Afrika') and a.Name=b.Name and a.Land=b.Land and a.Tiefe>b.Tiefe
---测试数据---
if object_id('[See]') is not null drop table [See]
go
create table [See]([Name] varchar(13),[Tiefe] int,[Land] varchar(10))
insert [See]
select 'Bodensee',252,'BRD' union all
select 'Gardasee',346,'Italien' union all
select 'Vaenernsee',100,'Schweden' union all
select 'Tanganjikasee',1435,'Zaire' union all
select 'Tanganjikasee',1435,'Tansania' union all
select 'Tanganjikasee',1435,'Russland' union all
select 'Tanganjikasee',1435,'Sambia' union all
select 'Victoriasee',85,'Uganda' union all
select 'Victoriasee',85,'Kenia' union all
select 'Victoriasee',85,'Tansania' union all
select 'Baikalsee',1620,'Russland' union all
select 'tanasee',72,'Aethiopien' union all
select 'Tschadsee',7,'Tschad' union all
select 'Tschadsee',7,'Niger' union all
select 'Tschadsee',7,'Nigeria' union all
select 'Eduardsee',117,'Zaire' union all
select 'Eduardsee',117,'Uganda' union all
select 'Ontariosee',236,'USA' union all
select 'Ontariosee',236,'Kanada' union all
select 'Titicacasee',272,'Peru' union all
select 'Titicacasee',272,'Bolivien' union all
select 'Schatzsee',272,'Phantasia' union all
select 'Silbersee',272,'Peru' union all
select 'Goldsee',135,'Eldorado' union all
select 'Genfersee',310,'Schweiz' union all
select 'Bodensee',252,'Schweiz'
go
if object_id('[Kontinent]') is not null drop table [Kontinent]
go
create table [Kontinent]([Land] varchar(10),[Erdteil] varchar(11))
insert [Kontinent]
select 'Schweden','Europa' union all
select 'Schweiz','Europa' union all
select 'Italien','Europa' union all
select 'BRD','Europa' union all
select 'Russland','Europa' union all
select 'Zaire','Afrika' union all
select 'Tansania','Afrika' union all
select 'Burundi','Afrika' union all
select 'Samia','Afrika' union all
select 'Uganda','Afrika' union all
select 'Kenia','Afrika' union all
select 'Aethiopien','Afrika' union all
select 'Tschad','Afrika' union all
select 'Niger','Afrika' union all
select 'Nigeria','Afrika' union all
select 'Mexiko','Amerika' union all
select 'Kanada','Amerika' union all
select 'USA','Amerika' union all
select 'Peru','Amerika' union all
select 'Bolivien','Amerika' union all
select 'eldorado','Amerika' union all
select 'Phanitasia','Antarrktika' union all
select 'China','Asien' union all
select 'Australien','Australien'
go
---查询---
select b.*
from Kontinent a
join See b on a.Land=b.Land
where a.Erdteil='Afrika'
and not exists(select 1 from see where land=b.land and tiefe>b.tiefe)
order by tiefe desc
---结果---
Name Tiefe Land
------------- ----------- ----------
Tanganjikasee 1435 Zaire
Tanganjikasee 1435 Tansania
Eduardsee 117 Uganda
Victoriasee 85 Kenia
tanasee 72 Aethiopien
Tschadsee 7 Tschad
Tschadsee 7 Niger
Tschadsee 7 Nigeria
(8 行受影响)
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-07 19:23:07
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [See]
IF OBJECT_ID('[See]') IS NOT NULL
DROP TABLE [See]
GO
CREATE TABLE [See] ([Name] [nvarchar](20),[Tiefe] [int],[Land] [nvarchar](10))
INSERT INTO [See]
SELECT 'Bodensee','252','BRD' UNION ALL
SELECT 'Gardasee','346','Italien' UNION ALL
SELECT 'Vaenernsee','100','Schweden' UNION ALL
SELECT 'Tanganjikasee','1435','Zaire' UNION ALL
SELECT 'Tanganjikasee','1435','Tansania' UNION ALL
SELECT 'Tanganjikasee','1435','Russland' UNION ALL
SELECT 'Tanganjikasee','1435','Sambia' UNION ALL
SELECT 'Victoriasee','85','Uganda' UNION ALL
SELECT 'Victoriasee','85','Kenia' UNION ALL
SELECT 'Victoriasee','85','Tansania' UNION ALL
SELECT 'Baikalsee','1620','Russland' UNION ALL
SELECT 'tanasee','72','Aethiopien' UNION ALL
SELECT 'Tschadsee','7','Tschad' UNION ALL
SELECT 'Tschadsee','7','Niger' UNION ALL
SELECT 'Tschadsee','7','Nigeria' UNION ALL
SELECT 'Eduardsee','117','Zaire' UNION ALL
SELECT 'Eduardsee','117','Uganda' UNION ALL
SELECT 'Ontariosee','236','USA' UNION ALL
SELECT 'Ontariosee','236','Kanada' UNION ALL
SELECT 'Titicacasee','272','Peru' UNION ALL
SELECT 'Titicacasee','272','Bolivien' UNION ALL
SELECT 'Schatzsee','272','Phantasia' UNION ALL
SELECT 'Silbersee','272','Peru' UNION ALL
SELECT 'Goldsee','135','Eldorado' UNION ALL
SELECT 'Genfersee','310','Schweiz' UNION ALL
SELECT 'Bodensee','252','Schweiz'
--> 生成测试数据表: [Kontinent]
IF OBJECT_ID('[Kontinent]') IS NOT NULL
DROP TABLE [Kontinent]
GO
CREATE TABLE [Kontinent] ([Land] [nvarchar](10),[Erdteil] [nvarchar](20))
INSERT INTO [Kontinent]
SELECT 'Schweden','Europa' UNION ALL
SELECT 'Schweiz','Europa' UNION ALL
SELECT 'Italien','Europa' UNION ALL
SELECT 'BRD','Europa' UNION ALL
SELECT 'Russland','Europa' UNION ALL
SELECT 'Zaire','Afrika' UNION ALL
SELECT 'Tansania','Afrika' UNION ALL
SELECT 'Burundi','Afrika' UNION ALL
SELECT 'Samia','Afrika' UNION ALL
SELECT 'Uganda','Afrika' UNION ALL
SELECT 'Kenia','Afrika' UNION ALL
SELECT 'Aethiopien','Afrika' UNION ALL
SELECT 'Tschad','Afrika' UNION ALL
SELECT 'Niger','Afrika' UNION ALL
SELECT 'Nigeria','Afrika' UNION ALL
SELECT 'Mexiko','Amerika' UNION ALL
SELECT 'Kanada','Amerika' UNION ALL
SELECT 'USA','Amerika' UNION ALL
SELECT 'Peru','Amerika' UNION ALL
SELECT 'Bolivien','Amerika' UNION ALL
SELECT 'eldorado','Amerika' UNION ALL
SELECT 'Phanitasia','Antarrktika' UNION ALL
SELECT 'China','Asien' UNION ALL
SELECT 'Australien','Australien'
--SELECT * FROM [See]
--SELECT * FROM [Kontinent]
-->SQL查询如下:
select a.*
from See a
join [Kontinent] b
on a.Land=b.Land
where b.Erdteil='Afrika'
and a.Land=(select top 1 Land from See where Name=a.Name order by Tiefe desc)
order by Tiefe desc
/*
Name Tiefe Land
-------------------- ----------- ----------
Tanganjikasee 1435 Tansania
Eduardsee 117 Uganda
Victoriasee 85 Kenia
tanasee 72 Aethiopien
Tschadsee 7 Niger
(5 行受影响)
*/
?