CREATE TABLE #phoneNote
(
ID INT ,
PhoneNumber VARCHAR(11)
)

INSERT INTO #phoneNote(ID,[PhoneNumber])
(SELECT 1,'13800000001' UNION
SELECT 2,'13800000001' UNION
SELECT 3,'13800000002' UNION
SELECT 4,'13800000002' UNION
SELECT 5,'13800000002' UNION
SELECT 6,'13800000003' UNION
SELECT 7,'13800000004' UNION
SELECT 8,'13800000005')


希望得到的结果：
phoneNumber 总数
----------- -----------
13800000001 2
13800000002 3
13800000003 1
13800000001 2
13800000000 0

(5 行受影响)

declare @str varchar(8000) = '13800000001,13800000002,13800000003,13800000000'
set @str = replace(@str, ',', ''' p union all select ''')
exec('select p, cn=isnull(cn,0) from (select '''+@str + ''') a left join (select pn=PhoneNumber, cn=count(1) from #phoneNote group by PhoneNumber) b on p=pn')
/*
phoneNumber 总数
----------- -----------
13800000001 2
13800000002 3
13800000003 1
13800000001 2
13800000000 0
*/

------解决方案--------------------
中午试试写一个，感觉动态的实现不怎么好
探讨


引用:

SQL code
declare @str varchar(8000) = '13800000001,13800000002,13800000003,13800000000'
set @str = replace(@str, ',', ''' p union all select ''')
exec('select p, cn=isnull(cn,……


------解决方案--------------------
探讨

中午试试写一个，感觉动态的实现不怎么好

引用:

引用:

SQL code
declare @str varchar(8000) = '13800000001,13800000002,13800000003,13800000000'
set @str = replace(@str, ',', ''' p union……


------解决方案--------------------
探讨


引用:

@str 有长度限制哦
只能输入几百个手机号码...


------解决方案--------------------

SQL code
declare @sql varchar(max)='13800000001,13800000002,13800000003,13800000001,13800000000'
declare @str xml
set @str=convert(xml,'<root><v>'+REPLACE(@sql,',','</v><v>')+'</v></root>')
select a.num,COUNT(b.PhoneNumber) from
(select ROW_NUMBER() over(order by getdate()) id,num=N.v.value('.','varchar(100)')  from @str.nodes('/root/v')N(v)) a 
left join #phoneNote b on a.num=b.PhoneNumber group by a.id,a.num
结果显示：
13800000000    0
13800000001    2
13800000001    2
13800000002    3
13800000003    1

------解决方案--------------------
SQL code

CREATE TABLE #phoneNote
(
ID INT ,
PhoneNumber VARCHAR(11)
)

INSERT INTO #phoneNote(ID,[PhoneNumber])
(SELECT 1,'13800000001' UNION
SELECT 2,'13800000001' UNION
SELECT 3,'13800000002' UNION
SELECT 4,'13800000002' UNION
SELECT 5,'13800000002' UNION
SELECT 6,'13800000003' UNION
SELECT 7,'13800000004' UNION
SELECT 8,'13800000005')


CREATE TABLE #phoneNoteSearch
(
PhoneNumber VARCHAR(11)
)
INSERT INTO #phoneNoteSearch(PhoneNumber)
select '13800000001' union all
select '13800000002' union all
select '13800000003' union all
select '13800000001' union all
select '13800000000'


select PhoneNumber,(select count(*) from  #phoneNote where #phoneNote.PhoneNumber=#phoneNoteSearch.PhoneNumber) as 总数 from #phoneNoteSearch 

13800000001    2
13800000002    3
13800000003    1
13800000001    2
13800000000    0