每行计数的SQL查询 [英] Sql query with count per row

问题描述

这是我的桌子结构

Here is my table structure

ID   Name      Phone_No 
1    ABC        123
2    ABC        123
3    XYZ        222
4    PQR       444

和所需的输出是这样的;这里是我想添加另一个colunm，根据电话号码给出计数

and required output is like this; Here is i want to add another colunm which gives count as per Phone No

ID   Name      Phone_No   Count
1    ABC        123         2
2    ABC        123         2
3    XYZ        222         1
4    PQR        444         1

所以PLZ帮我如何写sql查询

So plz help me how to write sql query

推荐答案

试试这个：

Try this one:

select t1.ID, t1.Name, t1.Phone_No, (select count(*) from mytable t2 where t2.Name=t1.Name and t2.Phone_No=t1.Phone_No) as Count from mytable t1

你可以先做 Scalar值函数，它会返回计数。 />

Hi,

you can first make the Scalar valued function which returns the count.

CREATE FUNCTION fn_GetPhoneCount 
(
	@Phone_No nvarchar(50) = ''
)
RETURNS int
AS
BEGIN

	Declare @Cnt INT
	SET @Cnt = 0

	Select @Cnt = COUNT(*) from Table_1 where Phone_No = @Phone_No

	Return @Cnt
END

然后写下这样的查询。

Then write the query like this.

Select [ID], [Name], [Phone_No], dbo.fn_GetPhoneCount([Phone_No]) AS [Count] from Table_1

我希望这能帮助你得到你的答案。

I hope this will help you to get your answer.

看看这里......你怎么样......：）

Look at here ... How do u like that...:)

SELECT T11.ID , T11.Name , T11.Phone_No , COUNT(T11.Phone_No)  FROM T11 (NOLOCK)
INNER JOIN T11 as B (NOLOCK)
    ON B.Name = T11.Name
GROUP BY T11.ID , T11.Name , T11.Phone_No

问候，

Vijay

Regards,
Vijay

这篇关于每行计数的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！