select id,sum(金额) 总金额,count(id) 充值次数 from table group by id;select id,sum(金额) 总金额,count(id) 充值次数 from table group by id having sum(金额)>1000 --筛选金额>1000的
select id,sum(金额) 总金额,count(id) 充值次数 from table group by id;select id,sum(金额) 总金额,count(id) 充值次数 from table group by id having sum(金额)>1000 --筛选金额>1000的 我另外想外联一张用户表查出用户的姓名怎么查询呢?请指教
外联表查询更简单,假设两表以id进行关联,在1楼的基础上:select table.id,user.username,sum(金额) 总金额,count(id) as 充值次数 from table,users where users.id=table.id group by id;大体上,SQL顺序如下: select <selected_list> from tables where 语句 group by 语句 having 语句 order by 语句 ;
select t1.id,t2.name,sum(金额) 总金额,count(id) 充值次数 from table1 t1,table2 t2 where t1.id=t2.id group by t1.id,t2.name;
select t1.id,t2.name,sum(金额) 总金额,count(id) 充值次数 from table1 t1,table2 t2 where t1.id=t2.id group by t1.id,t2.name;最后一个问题,充值记录表有个type类型 ,type=3为手续费,我要把这个手续费也一起弄出来。 显示效果 名称-总金额-总手续费-总次数
select t2.name 名称, sum(金额) 总金额, sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费, count(id) 充值次数 from table1 t1, table2 t2 where t1.id = t2.id group by t1.id, t2.name;
select t2.name 名称, sum(金额) 总金额, sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费, count(id) 充值次数 from table1 t1, table2 t2 where t1.id = t2.id group by t1.id, t2.name;真的非常感谢! 不过这个总次数可以排除掉手续费的记录吗?
select t2.name 名称, sum(金额) 总金额, sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费, SUM(decode(t1.TYPE, 3, 0, 1)) 充值次数 from table1 t1, table2 t2 where t1.id = t2.id group by t1.id, t2.name;
select t2.name 名称, sum(金额) 总金额, sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费, SUM(decode(t1.TYPE, 3, 0, 1)) 充值次数 from table1 t1, table2 t2 where t1.id = t2.id group by t1.id, t2.name; 可以解释下decode(t1.TYPE, 3, 0, 1)是什么意思吗?
我另外想外联一张用户表查出用户的姓名怎么查询呢?请指教
select <selected_list>
from tables
where 语句
group by 语句
having 语句
order by 语句
;
select t1.id,t2.name,sum(金额) 总金额,count(id) 充值次数 from table1 t1,table2 t2 where t1.id=t2.id group by t1.id,t2.name;
select t1.id,t2.name,sum(金额) 总金额,count(id) 充值次数 from table1 t1,table2 t2 where t1.id=t2.id group by t1.id,t2.name;最后一个问题,充值记录表有个type类型 ,type=3为手续费,我要把这个手续费也一起弄出来。
显示效果 名称-总金额-总手续费-总次数
select t2.name 名称,
sum(金额) 总金额,
sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费,
count(id) 充值次数
from table1 t1, table2 t2
where t1.id = t2.id
group by t1.id, t2.name;
select t2.name 名称,
sum(金额) 总金额,
sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费,
count(id) 充值次数
from table1 t1, table2 t2
where t1.id = t2.id
group by t1.id, t2.name;真的非常感谢! 不过这个总次数可以排除掉手续费的记录吗?
sum(金额) 总金额,
sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费,
SUM(decode(t1.TYPE, 3, 0, 1)) 充值次数
from table1 t1, table2 t2
where t1.id = t2.id
group by t1.id, t2.name;
sum(金额) 总金额,
sum(decode(t1.TYPE, 3, 金额, 0)) 总手续费,
SUM(decode(t1.TYPE, 3, 0, 1)) 充值次数
from table1 t1, table2 t2
where t1.id = t2.id
group by t1.id, t2.name;
可以解释下decode(t1.TYPE, 3, 0, 1)是什么意思吗?
type为3的时候为0,其他的就为1.这样进行累加的时候就排除了type为3的记录啊