现在有两个表结构一样 有个字段是金额 这个字段的金额有可能是负的 还有个字段是代号 整个表是记录流水的 我需要将2个表一起查询 结果 是按代号分组 然后将每个代号一样的金额字段相加 然后还有要显示每个代号的记录数 这个记录数要去除金额字段为负数的记录数 谢谢 select m1.* , isnull(m.记录数,0) 记录数 from ( select 代号 , 金额 = sum(金额) from ( select 代号 , 金额 from TA union all select 代号 , 金额 from TB ) t1 group by 代号 ) m1 left join ( select 代号 , 记录数 = count(*) from ( select 代号 , 金额 from TA where 金额 >= 0 union all select 代号 , 金额 from TB where 金额 >= 0 ) t2 group by 代号 ) m2 on m1.代号 = m2.代号
select 代号,sum(金额) as 金额,sum(记录数) as 记录数 from (select 代号,金额,case when 金额>=0 then 1 else 0 end as 记录数 from t1 union all select 代号,金额 from t1) t group by 代号
select 代号,sum(金额) as 总金额,sum(case when 金额>0 then 1 else 0 end ) as 记录数 (select 代号,金额 from 表1 union all select 代号,金额 from 表2 ) group by 代号
SELECT [代号],[合计]=SUM([金额]),[金额不为负数的合计]=SUM(CASE WHEN [金额]<0 THEN 0 ELSE 1 END) ( SELECT [代号],[金额] FROM [表1] UNION ALL SELECT [代号],[金额] FROM [表1] ) [t] GROUP BY [代号]
select ticketid,sum(amount) as amount,sum(case when amount>0 then 1 else 0 end ) as num from (select ticketid,amount from 表1 union all select ticketid,amount from 表2 )tmp group by ticketid
create table t1(ticketid int,amount int) insert into t1 select 1,5 union all select 1,-5 union all select 1,2 union all select 2,1 union all select 2,7 union all select 3,1 union all select 3,-1 union all select 3,0create table t2(ticketid int,amount int) insert into t2 select 4,5 union all select 4,-5 union all select 5,2 union all select 2,1 union all select 3,7 union all select 2,1 union all select 2,-1 union all select 3,0select * from t1 select * from t2select ticketid,sum([count]) as [count],sum(amount) as amountsum from( select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t1 union all select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t2 ) t group by ticketiddrop table t1,t2 (8 行受影响)(8 行受影响) ticketid amount ----------- ----------- 1 5 1 -5 1 2 2 1 2 7 3 1 3 -1 3 0(8 行受影响)ticketid amount ----------- ----------- 4 5 4 -5 5 2 2 1 3 7 2 1 2 -1 3 0(8 行受影响)ticketid count amountsum ----------- ----------- ----------- 1 2 2 2 4 9 3 4 7 4 1 0 5 1 2(5 行受影响)
select ticketid,sum(amount) as amount,sum(case when amount>0 then 1 else 0 end ) as num from (select ticketid,amount from 表1 union all select ticketid,amount from 表2 )tmp group by ticketidnum出来的只是所有正数的count 我要的是所有正数并去掉负数的count 青锋-SS你帮我写的好像也是的 应该需要如下结果 ticketid count amountsum ----------- ----------- ----------- 1 2 2 2 3 9 3 3 7 4 0 0 5 1 2
select ticketid,sum([count]) as [count],sum(amount) as amountsum from( select ticketid,case when amount>=0/*此处的条件是非负数,若只需要正数,把=去掉*/ then 1 else 0 end as [count],amount from t1 union all/*如果要去除重复的记录,把all去掉*/ select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t2 ) t group by ticketid
select ticketid,sum([count]) as [count],sum(amount) as amountsum from( select ticketid,case when amount>=0/*此处的条件是非负数,若只需要正数,把=去掉*/ then 1 else -1 end as [count],amount from t1 union all/*如果要去除重复的记录,把all去掉*/ select ticketid,case when amount>=0 then 1 else -1 end as [count],amount from t2 ) t group by ticketid
create table t1(ticketid int,amount int) insert into t1 select 1,5 union all select 1,-5 union all select 1,2 union all select 2,1 union all select 2,7 union all select 3,1 union all select 3,-1 union all select 3,0create table t2(ticketid int,amount int) insert into t2 select 4,5 union all select 4,-5 union all select 5,2 union all select 2,1 union all select 3,7 union all select 2,1 union all select 2,-1 union all select 3,0/* 想要的结果 ticketid count amountsum 1 1 2 2 2 8 3 1 0 */ select * from t1 order by ticketid select * from t2 order by ticketidselect * from t1 union all select * from t2 order by ticketid,amountselect ticketid,sum([count]) as [count],sum(amount) as amountsum from( select ticketid,case when amount>=0 then 1 else -1 end as [count],amount from t1 union all select ticketid,case when amount>=0 then 1 else -1 end as [count],amount from t2 ) t group by ticketiddrop table t1,t2 (8 行受影响)(8 行受影响) ticketid amount ----------- ----------- 1 5 1 -5 1 2 2 1 2 7 3 1 3 -1 3 0(8 行受影响)ticketid amount ----------- ----------- 2 1 2 1 2 -1 3 0 3 7 4 5 4 -5 5 2(8 行受影响)ticketid amount ----------- ----------- 1 -5 1 2 1 5 2 -1 2 1 2 1 2 1 2 7 3 -1 3 0 3 0 3 1 3 7 4 -5 4 5 5 2(16 行受影响)ticketid count amountsum ----------- ----------- ----------- 1 1 2 2 3 9 3 3 7 4 0 0 5 1 2(5 行受影响)
(
select 代号 , 金额 = sum(金额) from
(
select 代号 , 金额 from TA
union all
select 代号 , 金额 from TB
) t1
group by 代号
) m1 left join
(
select 代号 , 记录数 = count(*) from
(
select 代号 , 金额 from TA where 金额 >= 0
union all
select 代号 , 金额 from TB where 金额 >= 0
) t2
group by 代号
) m2
on m1.代号 = m2.代号
(select 代号,金额 from 表1
union all
select 代号,金额 from 表2
)
group by 代号
SELECT [代号],[合计]=SUM([金额]),[金额不为负数的合计]=SUM(CASE WHEN [金额]<0 THEN 0 ELSE 1 END)
(
SELECT [代号],[金额] FROM [表1] UNION ALL
SELECT [代号],[金额] FROM [表1]
) [t]
GROUP BY [代号]
ticketid amount
1 5
1 -5
1 2
2 1
2 7
3 1
3 -1
3 0想要的结果
ticketid count amountsum
1 1 2
2 2 8
3 1 0谢谢
(select ticketid,amount from 表1
union all
select ticketid,amount from 表2
)tmp
group by ticketid
insert into t1
select 1,5
union all select 1,-5
union all select 1,2
union all select 2,1
union all select 2,7
union all select 3,1
union all select 3,-1
union all select 3,0create table t2(ticketid int,amount int)
insert into t2
select 4,5
union all select 4,-5
union all select 5,2
union all select 2,1
union all select 3,7
union all select 2,1
union all select 2,-1
union all select 3,0select * from t1
select * from t2select ticketid,sum([count]) as [count],sum(amount) as amountsum
from(
select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t1
union all select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t2
) t
group by ticketiddrop table t1,t2
(8 行受影响)(8 行受影响)
ticketid amount
----------- -----------
1 5
1 -5
1 2
2 1
2 7
3 1
3 -1
3 0(8 行受影响)ticketid amount
----------- -----------
4 5
4 -5
5 2
2 1
3 7
2 1
2 -1
3 0(8 行受影响)ticketid count amountsum
----------- ----------- -----------
1 2 2
2 4 9
3 4 7
4 1 0
5 1 2(5 行受影响)
(select ticketid,amount from 表1
union all
select ticketid,amount from 表2
)tmp
group by ticketidnum出来的只是所有正数的count 我要的是所有正数并去掉负数的count
青锋-SS你帮我写的好像也是的 应该需要如下结果
ticketid count amountsum
----------- ----------- -----------
1 2 2
2 3 9
3 3 7
4 0 0
5 1 2
(8 行受影响)(8 行受影响)
ticketid amount
----------- -----------
1 5
1 -5
1 2
2 1
2 7
3 1
3 -1
3 0(8 行受影响)ticketid amount
----------- -----------
2 1
2 1
2 -1
3 0
3 7
4 5
4 -5
5 2(8 行受影响)ticketid amount
----------- -----------
1 -5
1 2
1 5
2 -1
2 1
2 1
2 1
2 7
3 -1
3 0
3 0
3 1
3 7
4 -5
4 5
5 2(16 行受影响)ticketid count amountsum
----------- ----------- -----------
1 2 2
2 4 9
3 4 7
4 1 0
5 1 2(5 行受影响)
//你可能没注意上面的数据,排序后的如上所述,确实就是你要的结果.
from(
select ticketid,case when amount>=0/*此处的条件是非负数,若只需要正数,把=去掉*/ then 1 else 0 end as [count],amount from t1
union all/*如果要去除重复的记录,把all去掉*/ select ticketid,case when amount>=0 then 1 else 0 end as [count],amount from t2
) t
group by ticketid
insert into t1
select 1,5
union all select 1,-5
union all select 1,2
union all select 2,1
union all select 2,7
union all select 3,1
union all select 3,-1
union all select 3,0create table t2(ticketid int,amount int)
insert into t2
select 4,5
union all select 4,-5
union all select 5,2
union all select 2,1
union all select 3,7
union all select 2,1
union all select 2,-1
union all select 3,0/*
想要的结果
ticketid count amountsum
1 1 2
2 2 8
3 1 0 */
select * from t1 order by ticketid
select * from t2 order by ticketidselect * from t1 union all select * from t2 order by ticketid,amountselect ticketid,sum([count]) as [count],sum(amount) as amountsum
from(
select ticketid,case when amount>=0 then 1 else -1 end as [count],amount from t1
union all select ticketid,case when amount>=0 then 1 else -1 end as [count],amount from t2
) t
group by ticketiddrop table t1,t2
(8 行受影响)(8 行受影响)
ticketid amount
----------- -----------
1 5
1 -5
1 2
2 1
2 7
3 1
3 -1
3 0(8 行受影响)ticketid amount
----------- -----------
2 1
2 1
2 -1
3 0
3 7
4 5
4 -5
5 2(8 行受影响)ticketid amount
----------- -----------
1 -5
1 2
1 5
2 -1
2 1
2 1
2 1
2 7
3 -1
3 0
3 0
3 1
3 7
4 -5
4 5
5 2(16 行受影响)ticketid count amountsum
----------- ----------- -----------
1 1 2
2 3 9
3 3 7
4 0 0
5 1 2(5 行受影响)