select ID,max(col1), max(col2)...., count(金额) from table group by ID
select id,max(金额).... from table group by id
max 貌似不行,如果同一个id存在多个记录,你使用max的记录很乱。 建议使用分析函数,计算金额也不能用count select id,col1,cole2,col3... sum(金额)over(partition by id order by 1) totalAmt from table
select a.sum,c.* (select id as , count(金额) sum from table group by ID) a inner join (select * from (select *,over(partition by id order by 金额) num from table ) b where b.num=1) c on a.id=c.id
select a.id,a.total,b... from (select id,sum(金额) as total from b group by id) a,b where b.id=a.id;
到现在还没清楚楼主的需求 查询所有字段 = select * from table 按ID分组 = group by id 这两条本身就是矛盾的,结果想要怎么展示?
我弄好了,把要显示的字段全部放到group by后面就行了
select id,sum(金额) from tab group by id
count()这个函数说白了是数个数(计数用的) 将金额累加起来用sum,然后用group by ,至于你要的粒度到哪个级别的就group by到那个级别
select count(金额) from table group by ID
select ID,max(col1), max(col2)...., count(金额) from table group by ID
建议使用分析函数,计算金额也不能用count
select id,col1,cole2,col3... sum(金额)over(partition by id order by 1) totalAmt from table
select a.sum,c.*
(select id as , count(金额) sum from table group by ID) a
inner join
(select * from
(select *,over(partition by id order by 金额) num from table ) b
where b.num=1) c
on a.id=c.id
where b.id=a.id;
查询所有字段 = select * from table
按ID分组 = group by id
这两条本身就是矛盾的,结果想要怎么展示?
将金额累加起来用sum,然后用group by ,至于你要的粒度到哪个级别的就group by到那个级别