表中
username password
1 11
1 12
1 11
2 99
1 11
1 12
。这样的结构 我想查出username为1时password为11和12的数量各是多少(不光是11和12两种结果)
谢谢
username password
1 11
1 12
1 11
2 99
1 11
1 12
。这样的结构 我想查出username为1时password为11和12的数量各是多少(不光是11和12两种结果)
谢谢
from table
where password in ('11','12',.....)
group by username,password
select username,password ,count(*) as counts
from table
where username='1'
and password in ('11','12',.....)
group by username,password
SELECT 1 USERNAME, 11 PASSWORD FROM DUAL
UNION ALL
SELECT 1 , 12 FROM DUAL
UNION ALL
SELECT 1 , 11 FROM DUAL
UNION ALL
SELECT 2 , 99 FROM DUAL
UNION ALL
SELECT 1 , 11 FROM DUAL
UNION ALL
SELECT 1 , 12 FROM DUAL
)
SELECT DISTINCT USERNAME, PASSWORD,
COUNT(PASSWORD) OVER (PARTITION BY PASSWORD) "count" FROM TT WHERE USERNAME=1;
create table #temptable(username int,[password] nvarchar(50));
if object_id('tempdb..#count') is null
create table #count(username int,[password] nvarchar(50),icount int);go
insert into #temptable select distinct * from [user];declare @uname int,@pass nvarchar(50),@count int ;
declare cr cursor for select * from #temptable;
open cr;
while @@fetch_status=0
begin
fetch next from cr into @uname,@pass;select @count=count(*) from [user] b where b.username=@uname and b.password = @pass;
insert into #count values(@uname,@pass,@count);
end
close cr;
deallocate cr;
select * from #count where username=1;