一个SQL问题（急）

Question

数据表是这样的accountuser    payuser   invoicenum     createdate     moneytype  moneyDFGJWL东方国际物 0 00330864 2008-06-25 RMB 100
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 200
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 300
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 400
DFGJWL东方国际物 0 00331542 2008-06-25 USD 120
DFGJWL东方国际物 0 00331542 2008-06-25 USD 220
DFGJWL东方国际物 0 00331542 2008-06-25 USD 320
DFGJWL东方国际物 0 00331542 2008-06-25 USD 420结果是
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 1000 --金额相加
DFGJWL东方国际物 0 00331542 2008-06-25 USD 1080 --金额相加请各位打侠帮帮忙 3Q

Answer 1

GROUP BY 统计下就可以了

Answer 2

select accountuser,payuser,invoicenum,createdate,moneytype,sum(money) money
from tb
group by accountuser,payuser,invoicenum,createdate,moneytype

Answer 3

-->生成测试数据
 
declare @tb table([accountuser] nvarchar(11),[payuser] nvarchar(1),[invoicenum] nvarchar(8),[createdate] Datetime,[moneytype] nvarchar(3),[money] int)
Insert @tb
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',100 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',200 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',300 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',400 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',120 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',220 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',320 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',420
Select [accountuser],[payuser],[invoicenum],[createdate],[moneytype],sum([money]) as t from @tb
group by [accountuser],[payuser],[invoicenum],[createdate],[moneytype]
/*
accountuser payuser invoicenum createdate              moneytype t
----------- ------- ---------- ----------------------- --------- -----------
DFGJWL东方国际物 0       00330864   2008-06-25 00:00:00.000 RMB       1000
DFGJWL东方国际物 0       00331542   2008-06-25 00:00:00.000 USD       1080
*/

Answer 4

select accountuser,payuser,invoicenum,createdate,moneytype,sum(money)money 
group by accountuser,payuser,invoicenum,createdate,moneytype    

Answer 5

Select [accountuser],[payuser],[invoicenum],[createdate],[moneytype],sum([money]) as t from @tb
group by [accountuser],[payuser],[invoicenum],[createdate],[moneytype]
应该是物流公司吧 o(∩_∩)o...

Answer 6

declare @t table(accountuser varchar(40),payuser int,invoicenum varchar(40),createdate datetime,moneytype varchar(3),[money] numeric(12,2)) 
insert @t select 'DFGJWL东方国际物',0,'00330864','2008-06-25','RMB',100 
union all select 'DFGJWL东方国际物',0,'00330864','2008-06-25','RMB',200 
union all select 'DFGJWL东方国际物',0,'00330864','2008-06-25','RMB',300 
union all select 'DFGJWL东方国际物',0,'00330864','2008-06-25','RMB',400 
union all select 'DFGJWL东方国际物',0,'00331542','2008-06-25','USD',120 
union all select 'DFGJWL东方国际物',0,'00331542','2008-06-25','USD',220 
union all select 'DFGJWL东方国际物',0,'00331542','2008-06-25','USD',320 
union all select 'DFGJWL东方国际物',0,'00331542','2008-06-25','USD',420 select * from @tselect accountuser,payuser,invoicenum,createdate,moneytype,'money'=isnull(sum([money]),0)
from @t
group by accountuser,payuser,invoicenum,createdate,moneytype/*（所影响的行数为 8 行）accountuser                              payuser     invoicenum                               createdate                                             moneytype money          
---------------------------------------- ----------- ---------------------------------------- ------------------------------------------------------ --------- -------------- 
DFGJWL东方国际物                              0           00330864                                 2008-06-25 00:00:00.000                                RMB       100.00
DFGJWL东方国际物                              0           00330864                                 2008-06-25 00:00:00.000                                RMB       200.00
DFGJWL东方国际物                              0           00330864                                 2008-06-25 00:00:00.000                                RMB       300.00
DFGJWL东方国际物                              0           00330864                                 2008-06-25 00:00:00.000                                RMB       400.00
DFGJWL东方国际物                              0           00331542                                 2008-06-25 00:00:00.000                                USD       120.00
DFGJWL东方国际物                              0           00331542                                 2008-06-25 00:00:00.000                                USD       220.00
DFGJWL东方国际物                              0           00331542                                 2008-06-25 00:00:00.000                                USD       320.00
DFGJWL东方国际物                              0           00331542                                 2008-06-25 00:00:00.000                                USD       420.00（所影响的行数为 8 行）accountuser                              payuser     invoicenum                               createdate                                             moneytype money                                    
---------------------------------------- ----------- ---------------------------------------- ------------------------------------------------------ --------- ---------------------------------------- 
DFGJWL东方国际物                              0           00330864                                 2008-06-25 00:00:00.000                                RMB       1000.00
DFGJWL东方国际物                              0           00331542                                 2008-06-25 00:00:00.000                                USD       1080.00（所影响的行数为 2 行）*/

Answer 7

Select [accountuser],[payuser],[invoicenum],[createdate],[moneytype],sum([money]) as t from @tb
group by [accountuser],[payuser],[invoicenum],[createdate],[moneytype]

Answer 8

我用这条SQLSelect [accountuser],[payuser],[invoicenum],CONVERT(VARCHAR(10),createdate,120),[moneytype],sum([money]) as t from feeinfo
where yzno='08WGSH05612' and feeflag='1' 
group by [accountuser],[payuser],[invoicenum],[createdate],[moneytype] 结果是这样
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 620.0
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 910.0
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 125.0
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 1000.0
DFGJWL东方国际物 0 00331542 2008-06-25 USD 740.0
DFGJWL东方国际物 0 00331542 2008-06-25 USD 1224.0
DFGJWL东方国际物 0 00331542 2008-06-25 USD 12.0
DFGJWL东方国际物 0 00331542 2008-06-25 USD 8.0

Answer 9

create table dwy
(
  accountuser varchar(20),
  payuser int,
  invoicenum varchar(20),
  createdate datetime,
  moneytype varchar(20),
  [money] int
)
insert into dwy
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',100 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',200 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',300 union all
select N'DFGJWL东方国际物',N'0',N'00330864','2008-06-25',N'RMB',400 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',120 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',220 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',320 union all
select N'DFGJWL东方国际物',N'0',N'00331542','2008-06-25',N'USD',420
/*结果是 
DFGJWL东方国际物 0 00330864 2008-06-25 RMB 1000 --金额相加 
DFGJWL东方国际物 0 00331542 2008-06-25 USD 1080 --金额相加 */
select accountuser,payuser,createdate,moneytype, 
sum([money]) as '额度' from dwy group by accountuser,payuser,createdate,moneytype

Answer 10

Select [accountuser],[payuser],[invoicenum],CONVERT(VARCHAR(10),createdate,120),[moneytype],sum([money]) as t from feeinfo 
where yzno='08WGSH05612' and feeflag='1' 
group by [accountuser],[payuser],[invoicenum],CONVERT(VARCHAR(10),createdate,120),[moneytype] 

Answer 11

嫖窃别人的~
select accountuser,payuser,invoicenum,createdate,moneytype,'money'=isnull(sum([money]),0)
from @t
group by accountuser,payuser,invoicenum,CONVERT(VARCHAR(10),createdate,120),moneytype

Answer 12

整出来了 select payuser,invoicenum,createdate,moneytype,accountuser,sum(t) from (Select [accountuser],[payuser],[invoicenum],CONVERT(VARCHAR(10),createdate,120)as createdate,[moneytype],sum([money]) as t from feeinfo
where yzno='08WGSH05612' and feeflag='1' 
group by [accountuser],[payuser],[invoicenum],[createdate],[moneytype] )  b group by moneytype,payuser,invoicenum,createdate,moneytype,accountuser

Answer 13

Smack That - Akon eminem  呵呵，这首歌曲确实挺酷！