统计属于同一城市的员工人数 [英] Count Number of Employees belonging to same city

问题描述

你好，



我有2张桌子：

员工

 ID名称CityID 
  1  asd  1  

City

 ID名称
  1 孟买
  2  Pune 

我希望员工随身物品更多超过3个城市。但是，同一城市的员工应该算作1.

例如

员工表数据

 ID名称CityID 
  1  asd  1  
  2  asd  1  

this计数将是1.



谢谢

解决方案

首先，感谢您指定这是作业。我们在为家庭作业提供多少帮助方面受到限制，但这对您有利。由于导师从这些网站的抄袭中直接寻求帮助，我知道有人会失败。另外，如果你告诉我们，我们的方法对你的教育更好:)



所以：两个问题。第二个很容易，一旦你处理了第一个：表有重复。



重复将使你的计数不准确。您尝试使用的任何聚合都是错误的。你需要的是一个干净的表格分组。



你需要在查询中创建这个'干净'表，然后运行计数查询。在这里你有几个选择：



普通表格表格：

我的个人收藏。它们可能很棘手，但是非常有效！

这个主题有很多资源。这篇文章是为了帮助您入门：

SQL SERVER 2008中的公用表表达式（CTE） [ ^ ]



内联表查询：

这可能是最简单的实行。它只是意味着您在使用之前将表定义为选择。它可能看起来像这样：

 选择 count（*） ，groupid 
 来自 
（选择  distinct  id，groupid 来自 mytable）
  group   by  groupid 

最后，你可以创建一个临时表在您选择之前存储您的不同数据。有几种方法可以定义临时表：

create table #table（id int，groupid int）将在tempdb中创建

delcare @table表（id int， groupid int）将仅在内存中创建

等...



这种技术非常重，但有时候最容易理解。





查看每个并选择你的收藏



希望有所帮助^ _ ^

1。首先，城市表很好。

2.至于员工表，它应该只存储员工资料，cityID在这里没有位置。

3.两者都是city和employee表具有多对多关系。可以把它想象成每个员工都可以分配到一个以上的城市，每个城市都可以分配给一个以上的员工。知道了吗？

4.如何链接它们？不是直接的。你需要第三个表，让它称为emp_city表，至少包含2个字段：

emp_id和city_id，两者都形成复合主键，每个表都指向员工和城市表中相应的主键。换句话说，它们分别是员工和城市表的外键。

员工表emp_city表城市表
 id ---------- ------------ emp_id cityName 
 name city_id ------------------- cityId 

了解更多：

1. http： //www.datanamic.com/support/lt-dez005-introduction-db-modeling.html [ ^ ]

2. http://www.studytonight.com/dbms/database-normalization.php [ ^ ]

首先获得设计权。

Hello,

I have 2 table :
Employees

ID Name CityID
1  asd  1

City

ID Name
1  Mumbai
2  Pune

I want Employees belongings to more than 3 cities.But Employees assinging to same City should be count as 1.
For Example
Employee Table Data

ID Name CityID
1  asd  1
2  asd  1

this Count will ne 1.

Thank You

解决方案

First, thank you for specifying that this is homework. We are limited in how much help we can give for homework, but this is for your benefit. I have know people to fail due to tutors calling direct help from these sites 'plagiarism'. Also, our approach is better for your education if you let us know :)

So: two issues. The second is easy once you have dealt with the first: The table has duplicates.

Duplicates are going to make your counts inaccurate. Any aggregate you attempt to use will be wrong. What you need is a clean table to group.

You need to create this 'clean' table in a query and then run the count query. Here you have several options:

Common Table Expressions:
My personal fav. They can be tricky to get your head around but are really efficient!
There are plenty of resources on this subject. Here's an article to get you started:
Common Table Expressions(CTE) in SQL SERVER 2008[^]

Inline table query:
This is probably the easiest to implement. It just means that you define your table as a select before you use it. It might look something like this:

select count(*), groupid
from
    (select distinct id, groupid from mytable)
group by groupid

Finally, you can create a temporary table to store your distinct data before you select from it. There are several ways of defining temporary tables:
create table #table(id int, groupid int) will be created in the tempdb
delcare @table table (id int, groupid int) will be created in memory only
etc...

This technique is very heavy but is sometimes the easiest to understand.


Look into each and pick your fav

Hope that helps ^_^

1. First, the city table is fine.
2. As for the employee table, it should just stores only employee particulars, cityID has no place here.
3. Both the city and employee tables have a many-to-many relationship. Think of it as "each employee can be assigned to more than one city, and each city can be assigned to more than one employee." Got it?
4. How to link them? Not directly. You need a third table, let call it emp_city table with at least 2 fields:
emp_id and city_id, both form the composite primary key and each points to the respective primary keys in the employee and city tables. In other words, they are foreign keys to employee and city table respectively.

employee table        emp_city table           city table
id----------------------emp_id                   cityName
name                    city_id-------------------cityId

Learn more:
1. http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]
2. http://www.studytonight.com/dbms/database-normalization.php[^]
Get the design right first.

这篇关于统计属于同一城市的员工人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！