每行计数的SQL查询 [英] Sql query with count per row
本文介绍了每行计数的SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的桌子结构
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 theScalar 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屋!
查看全文