select phone_id=case when phone_id>5 then 0 else 1 end from table
update table set phone_id=case when phone_id>5 then 0 else 1 end from table
之后再 select phone_id,sum(..),sum(..) from table group by phone_id
这个phone_id是不能在数据库表里改的啊,只有在提取数据的时候临时改变一下
--测试 create table a (aa int,bb int) insert a values (6,10) insert a values (3,20) insert a values (3,20)---a表内容不改动 select * from a select aa=case when aa>5 then 0 else 1 end,bb=sum(bb) from a group by case when aa>5 then 0 else 1 end---a表内容改动 update a set aa=case when aa>5 then 0 else 1 end select aa,bb=sum(bb) from a group by aadrop table a
我的意思是: phone_id大于5的置为0,小于5的置为1后 再 select sum(c),sum(col1),... from table1 group by datetime 这样一条语句可以搞定么?
可以啊,如果你本意不是更改phone_id 那么 select phone_id=case when phone_id>5 then 0 else 1 end,sum(col1),... from table1 group by case when phone_id>5 then 0 else 1 end
就是在select中,没有sum的都要放在group by 里
SELECT SUM(CASE WHEN phone_id > 5 THEN 0 ELSE 1 END), bb = SUM(col1), datetime FROM table1 GROUP BY datetime ORDER BY datetime
set phone_id=case when phone_id>5 then 0 else 1 end from table
select phone_id,sum(..),sum(..)
from table
group by phone_id
create table a (aa int,bb int)
insert a values (6,10)
insert a values (3,20)
insert a values (3,20)---a表内容不改动
select * from a
select aa=case when aa>5 then 0 else 1 end,bb=sum(bb)
from a
group by case when aa>5 then 0 else 1 end---a表内容改动
update a
set aa=case when aa>5 then 0 else 1 end
select aa,bb=sum(bb)
from a
group by aadrop table a
phone_id大于5的置为0,小于5的置为1后
再
select sum(c),sum(col1),...
from table1
group by datetime
这样一条语句可以搞定么?
那么
select phone_id=case when phone_id>5 then 0 else 1 end,sum(col1),...
from table1
group by case when phone_id>5 then 0 else 1 end
FROM table1
GROUP BY datetime
ORDER BY datetime