表中内容如下:
FOLI CASH DATE
10001 1000 2010-1-1
10001 1000 2010-1-1
10002 1200 2010-1-1
10003 1300 2010-1-1
10004 1500 2010-1-2想求出2010-1-1这一天中,CASH的和,即SUM(CASH)
但是像FOLI 10001 中1000出现了两次,就只算一次即最终结果是1000+1200+1300=3500谢谢。
FOLI CASH DATE
10001 1000 2010-1-1
10001 1000 2010-1-1
10002 1200 2010-1-1
10003 1300 2010-1-1
10004 1500 2010-1-2想求出2010-1-1这一天中,CASH的和,即SUM(CASH)
但是像FOLI 10001 中1000出现了两次,就只算一次即最终结果是1000+1200+1300=3500谢谢。
from
(select distinct * from tb)t
group by date
from
(
select foli,cash,convert(varchar(10),[date],120) as [date]
from tb
group by foli,cash,convert(varchar(10),[date],120)
) t
group by foli,[date]
from (
select distinct FOLI,CASH
from tb
where DATE = '2010-1-1'
) as t
from (select distinct * from tablename) as tmp
(
FOLI int,
CASH int,
DATE datetime
)insert into #T
select 10001,1000,'2010-1-1'
union select 10001,1000,'2010-1-1'
union select 10002,1200,'2010-1-1'
union select 10003,1300,'2010-1-1'
union select 10004,1400,'2010-1-2'
select DATE,CASH=sum(CASH) from
(
select distinct CASH,DATE from #T
) A
where DATE='2010-1-1'
group by DATE
SUM ( DISTINCT cash)语法
create table tablename4
(
FOLI int ,
CASH int,
DATE datetime
)insert into tablename4 values(10001 ,1000 ,'2010-1-1')
insert into tablename4 values(10001 ,1000 ,'2010-1-1')
insert into tablename4 values(10001 ,1200 ,'2010-1-1')
insert into tablename4 values(10001 ,1300 ,'2010-1-1')
insert into tablename4 values(10001 ,1500 ,'2010-1-2')--drop table tablename4
select DATE ,sum(CASH) as CASHSum
from
(
select distinct CASH,DATE from tablename4
) aa
group by date
select DATE ,sum(distinct CASH) as CASHSum
from tablename4
group by date
上面的貌似有问题吧,distinct cash有什么用,应该distinct FOLI select DATE ,sum(CASH) as CASHSum
from
(
select distinct FOLI, CASH, DATE from tablename4
) aa
group by date
from (
select distinct CASH,DATE
from tb
group by CASH,DATE
) as t
group by date
create table #T
(
FOLI int,
CASH int,
DATE datetime
)insert into #T
select 10001,1000,'2010-1-1' union
select 10001,1000,'2010-1-1' union
select 10002,1200,'2010-1-1' union
select 10003,1300,'2010-1-1' union
select 10004,1400,'2010-1-2'--查询
SELECT SUM(CASH) cash
FROM (
SELECT DISTINCT* FROM #T
) a
WHERE DATE ='2010-1-1'
--结果
cash
3500