这个查询语句怎么写呀?求助 ~~o(>_<)o ~~
CREATE TABLE #phoneNote(ID INT ,PhoneNumber VARCHAR(11))INSERT INTO #phoneNote(ID,[PhoneNumber])(SELECT 1,'13800000001' UNIONSELECT 2,'13800000001' UNIONSELECT 3,'13800000002' UNIONSELECT 4,'13800000002' UNIONSELECT 5,'13800000002' UNIONSELECT 6,'13800000003' UNIONSELECT 7,'13800000004' UNIONSELECT 8,'13800000005')希望得到的结果:phoneNumber 总数----------- -----------13800000001 213800000002 313800000003 113800000001 213800000000 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 213800000002 313800000003 113800000001 213800000000 0*/
[解决办法]
中午试试写一个,感觉动态的实现不怎么好
declare @sql varchar(max)='13800000001,13800000002,13800000003,13800000001,13800000000'declare @str xmlset @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 013800000001 213800000001 213800000002 313800000003 1
[解决办法]
CREATE TABLE #phoneNote(ID INT ,PhoneNumber VARCHAR(11))INSERT INTO #phoneNote(ID,[PhoneNumber])(SELECT 1,'13800000001' UNIONSELECT 2,'13800000001' UNIONSELECT 3,'13800000002' UNIONSELECT 4,'13800000002' UNIONSELECT 5,'13800000002' UNIONSELECT 6,'13800000003' UNIONSELECT 7,'13800000004' UNIONSELECT 8,'13800000005')CREATE TABLE #phoneNoteSearch(PhoneNumber VARCHAR(11))INSERT INTO #phoneNoteSearch(PhoneNumber)select '13800000001' union allselect '13800000002' union allselect '13800000003' union allselect '13800000001' union allselect '13800000000'select PhoneNumber,(select count(*) from #phoneNote where #phoneNote.PhoneNumber=#phoneNoteSearch.PhoneNumber) as 总数 from #phoneNoteSearch 13800000001 213800000002 313800000003 113800000001 213800000000 0