这是源数据,根据不同的adminid,算出每个adminid有多少条数据,并且state为0的有几条,为1的有几条
返回结果是这样的:adminid total state0 state1
1 100 90 10
2 111 55 56
total是state的总数,state0是state=0的数量,state1是state=1的数量,adminid是adminid
然后将以下得到的数据全部插入表2内:表2字段为:adminid s_total s_state0 s_state1请教各位大神如何做了?
返回结果是这样的:adminid total state0 state1
1 100 90 10
2 111 55 56
total是state的总数,state0是state=0的数量,state1是state=1的数量,adminid是adminid
然后将以下得到的数据全部插入表2内:表2字段为:adminid s_total s_state0 s_state1请教各位大神如何做了?
count(case when state=0 then 1 end) as state0,
count(case when state=1 then 1 end) as state1
from tb
group by adminid
select adminid,count(*) as total,
count(case when state=0 then 1 end) as state0,
count(case when state=1 then 1 end) as state1
from tb
group by adminid