表如下:
declare @aa table( id nvarchar(10),
title varchar(20),
createdate datetime
)
insert into @aa select '1111','231233','2008-10-10' union select '1111','231188','2008-10-10' union select '1112','231188','2008-10-10'
select * from @aa 查找的结果是:
id 相同,title前两位相同,createdate为2008-10-10号的记录条数?
declare @aa table( id nvarchar(10),
title varchar(20),
createdate datetime
)
insert into @aa select '1111','231233','2008-10-10' union select '1111','231188','2008-10-10' union select '1112','231188','2008-10-10'
select * from @aa 查找的结果是:
id 相同,title前两位相同,createdate为2008-10-10号的记录条数?
title varchar(20),
createdate datetime
)
insert into @aa select '1111','231233','2008-10-10' union select '1111','231188','2008-10-10' union select '1112','231188','2008-10-10'
select id , left(title,2) , count(*) cnt from @aa where createdate = '2008-10-10' group by id , left(title,2)/*
id cnt
---------- ---- -----------
1111 23 2
1112 23 1(所影响的行数为 2 行)
*/
declare @aa table( id nvarchar(10),
title varchar(20),
createdate datetime
)
insert into @aa select '1111','231233','2008-10-10' union select '1111','231188','2008-10-10' union select '1112','231188','2008-10-10'
select id,left(title,2) from @aa where createdate='2008-10-10' group by id,left(title,2) --条数改成count 即可