一个sql求助
来源:百度知道 编辑:UC知道 时间:2024/05/19 18:46:35
----取val_a最大请参考下面的
--按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
----------------取和
select consumer_id, sum(val_a) suma, sum(val_b) sumb
from tb
group by consumer_id
第一种情况:
SELECT a.[id],a.consumer_id,a.val_a,a.val_b FROM consumer AS a
WHERE a.[id] IN (SELECT TOP 1 [id] FROM consumer WHERE consumer_id = a.consume