有一表ttt,字段如下:
-----------------------
cxid cxdh5
3 K
4 J
5 U
6 K
7 K
8 P
10 S
11 S
13 S
------------
怎样查询出最后的一个字母次数
上面最后的字母是S
我要统计在ttt表里面的cxid=3到8的个数 ,即是6
(我只是要最后一个字母前的次数
(如果最后有几个连续相同,我就取最前(即cxid=10以上的)个数),其它字母不需要)
显示结果:
n cxdh5
6 S
------解决方案--------------------
declare @id int,@idnum int,@cxdn char(2)
select @id=max(cxid) from ttt
select @cxdn=cxdh5 from ttt where [email protected]
select @id=cxid from ttt where [email protected]
select @idnum=count(*) from ttt where cxid <@id
select @idnum,@cxdn
------解决方案--------------------
select count(*),_y.cxdh5
from ttt _x,(select top 1 cxdh5 order by cxid desc) _y
where _x.cxdh5 not in (select top 1 cxdh5 order by cxid desc)
group by _y.cxdh5
------解决方案--------------------
declare @t table(cxid int,cxdh5 char(10))
insert into @t select 3, 'K '
union all select 4, 'J '
union all select 5, 'U '
union all select 6, 'K '
union all select 7, 'K '
union all select 8, 'P '
union all select 10, 'S '
union all select 11, 'S '
union all select 13, 'S '
select count(*)as n,cxdh5=(select top 1 cxdh5 from @t order by cxid desc) from @t where cxdh5 not in (select top 1 cxdh5 from @t order by cxid desc)
result:
n cxdh5
----------- ----------
6 S
（所影响的行数为 1 行）
------解决方案--------------------
create table #t
(cxid int,
cxdh5 varchar(50)
)
insert #t
select '3 ', 'K ' union all select '4 ', 'J ' union all select '5 ', 'U ' union all select '6 ', 'K ' union all select '7 ', 'K ' union all select '8 ', 'P ' union all select '10 ', 'S ' union all select '11 ', 'S ' union all select '13 ', 'S '
select * from #t
declare @id1 int
declare @dh1 varchar(10)
declare @count int
select @id1= max(cxid) from #t
select @dh1= cxdh5 from #t where [email protected]
select @count= count(cxid) from #t where cxid not in (select distinct cxid from #t where [email protected] )
-- print @id1
select @count n,@dh1 cxdh5
----
n cxdh5
6 S
------解决方案--------------------
select top 1 cxdh5=cxdh5, n=(select count(*) from ttt where cxid <min(tmp.cxid))
from
(
select tmpA.cxdh5, tmpA.cxid, count(*)-sum(case when tmpA.cxdh5=tmpB.cxdh5 then 1 else 0 end) as groupid
from ttt as tmpA, ttt as tmpB
where tmpA.cxid> =tmpB.cxid
group by tmpA.cxdh5, tmpA.cxid
)tmp group by cxdh5, groupid
order by 2 desc
--result
cxdh5 n
---------- -----------
S 6