有标tab
deptid name val
1 a 1
1 a 1
1 b 2
1 c 1
2 d 3
2 e 4
我要得到
deptid tot
1 4
2 7
按deptid分组,name相同则只算一行 如deptid=1,tot=1+2+1
这个sql怎么写效率最高?谢谢!
deptid name val
1 a 1
1 a 1
1 b 2
1 c 1
2 d 3
2 e 4
我要得到
deptid tot
1 4
2 7
按deptid分组,name相同则只算一行 如deptid=1,tot=1+2+1
这个sql怎么写效率最高?谢谢!
select deptid,sum(val)
from ( select distinct deptid,name,val
from 表
) a
group by a.deptid
from (select distinct * from tab) a
group by deptid
有标tab
deptid name price sl val
1 a 1 2 1
1 a 1 3 1
1 b 1 4 2
1 c 1 5 1
2 d 1 6 3
2 e 1 7 4
我要得到
deptid all tot
1 14 4
2 13 7
按deptid分组,name相同则只算一行 如deptid=1,tot=1+2+1
all = price*sl 不管name相同与否 如deptid=1,all=1*2+1*3+1*4+1*5
这个sql怎么写效率最高?谢谢!
(SELECT deptid, SUM(price * sl) AS all FROM tab GROUP BY deptid) AS B
INNER JOIN
(SELECT A.deptid, SUM(A.price) AS tot FROM (SELECT DISTINCT * FROM tab) AS A GROUP BY A.deptid) AS C
ON B.deptid = C.deptid
from (
select deptid,[all]=sum(price*sl)
from tab
group by deptid
) a
left join (
select deptid,tot=sum(val)
from tab a
where not exists(select 1 from tab where name=a.name and deptid=a.deptid and sl>a.sl)
group by deptid
) b
on a.deptid=b.deptid
create table tab(deptid int,name varchar(10),price int,sl int,val int)
insert tab select 1,'a',1,2,1
insert tab select 1,'a',1,3,1
insert tab select 1,'b',1,4,2
insert tab select 1,'c',1,5,1
insert tab select 2,'d',1,6,3
insert tab select 2,'e',1,7,4
from (select distinct * from tab) a
group by a.deptid