在选择列表中嵌入计数语句

发布于 2024-12-19 00:36:25 字数 1117 浏览 2 评论 0原文

我正在使用 Esri ArcMap 10.0 和 Microsoft Sql Server 2008 R2

我有 2 个表：

MN_SCHOOLS_PUBLIC - 学校 ID 字段称为 ORGID_REL
TRPD_Schdgrps_Schools - 学校 ID 字段称为

School_Code第一个表只是学校及其地址的列表，第二个表是学校团体预订的列表。表 2 中的每所学校都存在于表 1 中。我正在尝试创建一个查询，在其中生成已进行多次预订的学校列表。这就是我想到的：

select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1

这达到了目的，或者至少足够接近。但是，我的问题在于我必须在 GIS 软件中运行此查询，查询工具（Esri 的 ArcMap 中的按属性选择）始终为用户启动 select 语句。因此，当您打开该工具时，查询以以下内容开头：

select * from gisadmin.MN_SCHOOLS_PUBLIC

我想选择表 1 中在表 2 中具有多个条目的所有学校，并且我需要从上面的 select 语句开始。这是我想出的，但我似乎不知道如何让它工作：

select * from gisadmin.MN_SCHOOLS_PUBLIC
where EXISTS 
(SELECT * 
FROM central2.GISADMIN.TRPD_Schdgrps_Schools 
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL =
(select * from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
where School_Code in
(select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1)))

任何帮助将不胜感激

原文

I am using Esri ArcMap 10.0 with Microsoft Sql Server 2008 R2

I have 2 tables:

MN_SCHOOLS_PUBLIC - the school ID field is called ORGID_REL
TRPD_Schdgrps_Schools - the school ID field is called School_Code

The first table is just a list of schools with their addresses, the second table is a list of group reservations made by schools. Every school in table 2 exists in table 1. I am trying to create a query where I generate a list of schools that have made multiple reservations. This is what I came up with:

select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1

This did the trick or at least close enough. However, my problem lies int hat I have to run this query in GIS software, the query tool (Select by Attributes in Esri's ArcMap) ALWAYS starts the select statement for the user. So when you open the tool, the query starts with:

select * from gisadmin.MN_SCHOOLS_PUBLIC

I want to select all the schools in table 1 that have multiple entries in table 2 and I am required to start with the above select statement. This is what I came up with but I can't seem to figure out how to get it to work:

select * from gisadmin.MN_SCHOOLS_PUBLIC
where EXISTS 
(SELECT * 
FROM central2.GISADMIN.TRPD_Schdgrps_Schools 
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL =
(select * from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
where School_Code in
(select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1)))

Any help would be greatly appreciated

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海夕 2024-12-26 00:36:25

试试这个：

SELECT * FROM gisadmin.MN_SCHOOLS_PUBLIC msp
WHERE msp.School_Code IN
(
    SELECT tss.School_Code
    FROM central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS tss
    GROUP BY tss.School_Code
    HAVING COUNT(*) > 1
)

Try this:

SELECT * FROM gisadmin.MN_SCHOOLS_PUBLIC msp
WHERE msp.School_Code IN
(
    SELECT tss.School_Code
    FROM central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS tss
    GROUP BY tss.School_Code
    HAVING COUNT(*) > 1
)

回复收藏 0 原文

~没有更多了~