再询问一个查询问题

asdasddfgerspfobjxlb 2010-05-07 07:18:26
第一个表

See

Name---Tiefe---Land
Bodensee 252 BRD
Gardasee 346 Italien
Vaenernsee 100 Schweden
Tanganjikasee 1435 Zaire
Tanganjikasee 1435 Tansania
Tanganjikasee 1435 Russland
Tanganjikasee 1435 Sambia
Victoriasee 85 Uganda
Victoriasee 85 Kenia
Victoriasee 85 Tansania
Baikalsee 1620 Russland
tanasee 72 Aethiopien
Tschadsee 7 Tschad
Tschadsee 7 Niger
Tschadsee 7 Nigeria
Eduardsee 117 Zaire
Eduardsee 117 Uganda
Ontariosee 236 USA
Ontariosee 236 Kanada
Titicacasee 272 Peru
Titicacasee 272 Bolivien
Schatzsee 272 Phantasia
Silbersee 272 Peru
Goldsee 135 Eldorado
Genfersee 310 Schweiz
Bodensee 252 Schweiz



第二个表: Kontinent


Land---Erdteil

Schweden-Europa
Schweiz-Europa
Italien-Europa
BRD-Europa
Russland-Europa
Zaire-Afrika
Tansania-Afrika
Burundi-Afrika
Samia-Afrika
Uganda-Afrika
Kenia-Afrika
Aethiopien-Afrika
Tschad-Afrika
Niger-Afrika
Nigeria-Afrika
Mexiko-Amerika
Kanada-Amerika
USA-Amerika
Peru-Amerika
Bolivien-Amerika
eldorado-Amerika
Phanitasia-Antarrktika
China-Asien
Australien-Australien







如果我想要列出非洲各个国家里最深的那个还的名字,深度和国家名字.要怎么办
就是说有些非洲国家里可能有2个海,但是我只要那个最深的那个
最后按照深度排列






...全文
114 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 sql_lover 的回复:]

重复的一样的深度平均值也一样嘛
[/Quote]

不一样呀..20跟2算平均是11

20,20,2平均呢?
sql_lover 2010-05-08
  • 打赏
  • 举报
回复
重复的一样的深度平均值也一样嘛
  • 打赏
  • 举报
回复
顶上去,有人帮忙看看如果我想算平均深度的话要怎么算呢?因为1表里有重复的海洋深度,但是每个海洋深度只需要1次算平均值.
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 sgtzzc 的回复:]
SQL code
---测试数据---
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……
[/Quote]

感谢你的回答,用not exists 排除同一个国家的其他海,受教了.

另外想再问一下,如果我想算平均深度呢?
sql_lover 2010-05-07
  • 打赏
  • 举报
回复

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
sql_lover 2010-05-07
  • 打赏
  • 举报
回复
错了,应该是:

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)
sql_lover 2010-05-07
  • 打赏
  • 举报
回复

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
sgtzzc 2010-05-07
  • 打赏
  • 举报
回复
---测试数据---
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 行受影响)
htl258_Tony 2010-05-07
  • 打赏
  • 举报
回复
----------------------------------------------------------------------------------
-- 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 行受影响)
*/

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧