我有一个消费表,里边有 ID,num,money,stype具体内容为,而此表还关联四五个表,并显示其他表的内容和以下内容,现在想把
stype相同的记录进行计算,然后显示计算结果来,我要如何写这个SQL?
1 2 100 1918
2 -3 -103 1917
3 5 50 1919
4 7 78 1912
5 -9 -150 1918
6 1 100 1917现在要求显示出
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912
stype相同的记录进行计算,然后显示计算结果来,我要如何写这个SQL?
1 2 100 1918
2 -3 -103 1917
3 5 50 1919
4 7 78 1912
5 -9 -150 1918
6 1 100 1917现在要求显示出
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912
select sum(money) from table1
正与负sum就得到了你要计算的结果!
select sum(money) from table1
group by stype
declare @t table (col1 int,col2 int,col3 int,col4 int)
insert into @t
select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917select col1,col2=(SELECT SUM(col3) FROM @t WHERE col1<=a.col1),
col3,col4 from @t a
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 100 100 1918
2 -3 -103 1917
3 47 50 1919
4 125 78 1912
5 -25 -150 1918
6 75 100 1917
*/--显示不出楼主要的结果...
stype分组,money相加
insert into @t
select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917select a.col1,a.col2,b.col3,b.col4 from @t a
right join (
select col1=min(col1),col4,col3=sum(col3) from @t group by col4
) b on a.col1=b.col1 and a.col4=b.col4 order by 1/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912
*/
declare @t table
(
id int,num int,money int,stype int
)
insert into @t
select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917select a.id,a.num,b.money,b.stype from @t a
right join (
select id=min(id),stype,money=sum(money) from @t group by stype
) b on a.id=b.id and a.stype=b.stype order by 1/*
id num money stype
----------- ----------- ----------- -----------
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912
*//*
--你可以把你的联查结果写成视图,然后从视图里查询处理即可select a.id,a.num,b.money,b.stype from 你的视图名称 a
right join (
select id=min(id),stype,money=sum(money) from 你的视图名称 group by stype
) b on a.id=b.id and a.stype=b.stype order by 1
*/
drop table tb
go
create table tb (ID INT,num int,money int,stype int)
insert into tb select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917
-- 现在要求显示出
--1 2 -50 1918
--2 -3 -3 1917
--3 5 50 1919
--4 7 78 1912
select ID=ROW_NUMBER()over(order by getdate()), SUM(num)as [num],SUM(money)as [money],stype from tb group by stype
ID num money stype
-------------------- ----------- ----------- -----------
1 7 78 1912
2 -2 -3 1917
3 -7 -50 1918
4 5 50 1919(4 行受影响)
drop table tb
go
create table tb (ID INT,num int,money int,stype int)
insert into tb select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917
-- 现在要求显示出
--1 2 -50 1918
--2 -3 -3 1917
--3 5 50 1919
--4 7 78 1912
select t.ID,t.num,s.money,s.stype from tb t join
(select id=min(id),money=sum(money),stype from tb group by stype ) s on t.ID=s.id
ID num money stype
----------- ----------- ----------- -----------
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912(4 行受影响)
(
id int,num int,money int,stype int
)
insert into @t
select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917select id=min(id),stype,money=sum(money) from @t group by stype
/*
按stype分组,得到money的和,和stype对应的最小的id
*/--结果如下
/*
id stype money
----------- ----------- -----------
4 1912 78
2 1917 -3
1 1918 -50
3 1919 50
*/--然后连接查询得到对应的num,并order by 排序select a.id,a.num,b.money,b.stype from @t a
right join (
select id=min(id),stype,money=sum(money) from @t group by stype
) b on a.id=b.id and a.stype=b.stype order by 1
/*
id num money stype
----------- ----------- ----------- -----------
1 2 -50 1918
2 -3 -3 1917
3 5 50 1919
4 7 78 1912
*/
使用完 会自动消失
(id int,num int,money int,stype int)
insert into @t
select 1,2,100,1918 union all
select 2,-3,-103,1917 union all
select 3,5,50,1919 union all
select 4,7,78,1912 union all
select 5,-9,-150,1918 union all
select 6,1,100,1917
select min(id) id,min(num) num,sum(money) money,stype from @t group by stype order by id
from tb
group by stype