试图做一个COUNT / AS RANK，它将无法正常工作。 [英] Trying to do a COUNT/AS RANK and it will not work.

问题描述

大家好！我是SQL的新手，并为自己提供了3天的速成课程！我为自己缺乏知识而道歉。一个项目被分配给我工作，所以现在我需要完成它。


我正在运行SQL SERVER 2008.我有一个数据库。它被称为HomeHealth。它有3个表。


1.HomeCareAgencies（持有代理商的信息，如姓名，地址，电话，以及表格中称为助记符的ID。

2.MoCounties（它拥有，县名，服务提供并且还有助记字段。此表适用于1个州。

3. ILCounties（与MoCounties相同但是一秒钟）状态。


现在，我需要能够运行一个查询，它将为我提供HomeCareAgencies，MoCounties County Name and Services以及ILCounties County Name and Services的所有字段。 />
似乎很容易，事实上我已经完成了这个。但是，它创造了大约9,000行的结果，因为如果一个家庭护理机构在两个州的多个县并且提供多种服务它会显示它在个别行中。我需要它为代理商提供一行，以包括它提供的所有MoCounties，IlCounties及其所有服务。


我尝试使用Count（*）AS在我的排名查询，但它剥落了。


此查询效果很好：

________________________________________________

Hello everyone! I am new to SQL and have given myself a 3 Day crash course! I apologize for my lack of knowledge. A project was assigned to me for work and so now I need to get it done.

I am running SQL SERVER 2008. I have one Database. It is called "HomeHealth". It has 3 Tables.

1.HomeCareAgencies (Holds the Agency Information such as name, address, phone, as well as an ID that is called Mnemonic in the table.
2.MoCounties (It holds, County Names, Services Offered and also has the Mnemonic field. This table is for 1 State.
3. ILCounties (Same as MoCounties but is for a second state.

Now, I need to be able to run a query that will give me all fields from HomeCareAgencies, MoCounties County Name and Services as well as ILCounties County Name and Services.
Seems like it would be easy and in fact I have accomplished this. However, It creates a result of about 9,000 rows because if one home Care Agency is in multiple counties in both states and offers multiple services it displays it in individual lines. I need it to have one line for the Agency to include all of the MoCounties, IlCounties and all services that it provides.

I tried using a Count(*)AS Rank in my query but it flakes out.

This Query works great:
________________________________________________

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | 换行 | 行号

推荐答案

虽然你已经非常好地提出了你的问题，特别是对于那个不熟悉网站的人，我不确定我是否也非常关注它。

我看到的是，作为一个开始，你可能需要一个左侧有[HomeCareAgencies]的基本查询，其他两个表都有LEFT JOIN。类似于：

Although you have laid out your question very well Ken, especially for one who is new to the site, I''m not sure I follow it all too well.

What I do see though, is that you will probably need, as a start, a base query that has [HomeCareAgencies] on the left, with LEFT JOINs to both of the other two tables. Something like :

展开 | 选择 | Wrap | 行号

我是第二个NeoPa关于规范化的评论。


另外，我不明白你为什么询问计数功能时听起来你只是在试图解决问题时才使用计数而不是问题本身。


问题，如果我正确读取，不是count函数，而是你要连接多行。在SQL Server 2008及更高版本中，您可以访问可以为您执行此操作的XML功能。


此示例如下：

I second NeoPa''s comment about normalization.

Also, I don''t understand why you are asking about the count function when it sounds like you are only using count in attempt to solve a problem and is not the problem itself.

The problem, if I am reading correctly, is not the count function, but that you want to concatenate multiple rows. In SQL Server 2008 and up, you have access to XML functionality that can do this for you.

An example of this is:

展开 | 选择 | Wrap | 行号

@NeoPa和@ Rabbit-感谢您的回复。我不能组合Counties表，因为它们独立地用于不同的程序。所以这是我遇到的障碍之一。我想我要解决的主要问题是，如果其中一个机构提供5种不同的服务，它会列出该机构5次。我需要它列出该机构1次与该1个机构列出的所有5项服务。我认为这就是@Rabbit试图告诉我的内容，但就像我刚开始说的那样，我只使用SQL 3天，所以不太确定。再次感谢大家的帮助。

@NeoPa and @Rabbit- Thank you both for your responses. I cant''t combine the Counties tables because they are used in a different program independently. So that is one of the hurdles I have. I guess the main issue I am trying to resolve is, that if one of the Agencies provides 5 different services it list that agency 5 times. I need it to list the agency 1 time with all 5 services listed for that 1 agency. I think that is what @Rabbit is trying to tell me but like I said at the beginning I have only been using SQL for 3 days so not quite sure. Thank you all again for your assistance.

这篇关于试图做一个COUNT / AS RANK，它将无法正常工作。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！