返回重复列值(Returning The Duplicate Column Values)

返回重复列值(Returning The Duplicate Column Values)

在下表中，我有一个id字段名称字段和DateofBirth。 dateofbirth的数据类型是“date”。

在表格中，Alen's＆Krizte的DateOfBirth是相同的（用蓝色标记）。

http://s24.postimg.org/j2wsw3r11/123.jpg （请不要编辑这个，我意外地把另一张桌子也有2个类似的日期分娩，我在另一张桌子上幸运和斯图尔特有同样的dob）

现在我的问题是这个。 我想回复在同一天过生日的人的名字，就像我之前说过的那样。 ＆iam使用sql server 2008

我也想知道，如果表中有多个像这样的实例那么我们怎么能找到呢？ 我的英语不是那么好，如果你有任何问题，那么问我，我会试着解释一下.. !! 希望你明白我刚刚解释的内容。

In the table below i have an id field name field and DateofBirth. The data type of dateofbirth is 'date'.

In the table, Alen's & Krizte's DateOfBirth are same (marked in Blue).

http://s24.postimg.org/j2wsw3r11/123.jpg (Please dont edit this, i accidently put another table which has 2 similar dateofbirths too,i in another table lucky & stuart has same dob)

Now my question is this. I want to return the name of persons who have birthday on same day, like i said before. & iam using sql server 2008

I also want to know, if there are multiple instance like this in a table then how can we find that too ?? My english is not that great, if you have any questions then ask me, i will try to explain it more..!! Hope you understand what i just explained.

最满意答案

SQL2005 +

SELECT src.* FROM ( SELECT p.Id, p.Name, p.DateOfBirth, COUNT(*) OVER(PARTITION BY p.DateOfBirth) AS CountPerDateOfBirth FROM dbo.Person p ) src WHERE src.CountPerDateOfBirth > 1

注意＃1：我使用COUNT函数和OVER子句 （需要SQL2005分钟）。

注意＃2： PARTITION BY p.DateOfBirth将根据DateOfBirth值将源行（dbo.MyTable）拆分为多个组（17-07-1976,13-​​01-1978,26-04-1982,26-04-1988） ）：

----------------------- 1, Alen, 17-07-1976 4, Krizte, 17-07-1976 ----------------------- 3, Sona, 13-01-1978 ----------------------- 5, Lucky, 26-04-1982 ----------------------- 2, Stuart, 26-04-1988 -----------------------

对于每个组， COUNT(*)将......计算行数：

----------------------- 1, Alen, 17-07-1976, 2 4, Krizte, 17-07-1976,2 ----------------------- 3, Sona, 13-01-1978 ,1 ----------------------- 5, Lucky, 26-04-1982 ,1 ----------------------- 2, Stuart, 26-04-1988,1 -----------------------

SQL2005+

SELECT src.* FROM ( SELECT p.Id, p.Name, p.DateOfBirth, COUNT(*) OVER(PARTITION BY p.DateOfBirth) AS CountPerDateOfBirth FROM dbo.Person p ) src WHERE src.CountPerDateOfBirth > 1

Note #1: I used COUNT function with OVER clause (requires SQL2005 min.).

Note #2: PARTITION BY p.DateOfBirth will split the source rows (dbo.MyTable) into many groups based on DateOfBirth values (17-07-1976, 13-01-1978, 26-04-1982, 26-04-1988):

----------------------- 1, Alen, 17-07-1976 4, Krizte, 17-07-1976 ----------------------- 3, Sona, 13-01-1978 ----------------------- 5, Lucky, 26-04-1982 ----------------------- 2, Stuart, 26-04-1988 -----------------------

and for every group COUNT(*) will ... count the number of rows:

----------------------- 1, Alen, 17-07-1976, 2 4, Krizte, 17-07-1976,2 ----------------------- 3, Sona, 13-01-1978 ,1 ----------------------- 5, Lucky, 26-04-1982 ,1 ----------------------- 2, Stuart, 26-04-1988,1 -----------------------