求一个简单sql语句，请指教~~~~~~~~~~~~谢谢
表的信息
myID qufen
----------- ----------
0010111 1
0010222 9
0010333 1
0020111 9
0020222 1
0030111 8
-----------------------------------------------
希望得到的
↓
myID前4位 总数 qufen不等于9的总数
--------- --- ----------------
0010 3 2
0020 2 1
0030 1 0
谢谢~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[解决办法]
Select
Left(myID, 4) As myID,
Count(*) As 总数,
SUM(Case When qufen != 9 Then 1 Else 0 End) As qufen不等于9的总数
From
表
Group By
Left(myID, 4)
[解决办法]
select left(myID,4),count(1),sum(case when qufen <> 9 then 1 else 0 end )
from 表 group by left(myID,4)
[解决办法]
select left(myId,4) [前4],count(1) [总数],sum(case when qufen <> 9 then 1 else 0 end ) [不等于9的数] from [Table] Group by left(myId,4)
[解决办法]
select a.[myID前4位],a.总数,b.[qufen不等于9的总数]
from
(
select [myID前4位]=left(myID,4),总数=count(1) from test group by left(myID,4)
)a
left join
(
select [myID前4位]=left(myID,4),[qufen不等于9的总数]=count(1) from test where qufen <> 9 group by left(myID,4)
)b
on a.[myID前4位]=b.[myID前4位]
