27,580
社区成员
发帖
与我相关
我的任务
分享
select b.ddate 日期,b.contermno 终端号,a.termdept 商户部门,b.cishu 交易笔数,b.sumconmoney 交易金额 from madmterm a,
(select convert(varchar(10),condate,120) as ddate,contermno,count(*) as cishu,sum(conmoney) as sumconmoney
from conrecledroll where condate>='2009-11-01'and condate<='2009-11-21' and contype='消费'and
contermno in (select termno from madmterm where termdept='商户A')
group by contermno,convert(varchar(10),condate,120))b where a.termno=b.contermno order by b.ddate,b.contermno
CREATE TABLE [dbo].[conrecled] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[conCardNo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PeoNo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PeoName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PeoComPany] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PeoShiYeBu] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[condate] [datetime] NOT NULL ,
[conmoney] [money] NULL ,
[fundMoney] [money] NULL ,
[consurplus] [money] NOT NULL ,
[CardConCount] [int] NULL ,
[contype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[conTermNo] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[conTermName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ConBuilding] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Condept] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[conOperNo] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[conOperName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[OperNo] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OperName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[LoginUserName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ComputerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[conFlag] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[conrecled] ADD
CONSTRAINT [DF_conrecled_conmoney] DEFAULT (0) FOR [conmoney],
CONSTRAINT [DF_conrecled_fundMoney] DEFAULT (0) FOR [fundMoney],
CONSTRAINT [DF_conrecled_consurplus] DEFAULT (0) FOR [consurplus],
CONSTRAINT [DF_conrecled_conFlag] DEFAULT (0) FOR [conFlag]
GO
CREATE INDEX [IX_conrecled_1] ON [dbo].[conrecled]([condate]) ON [PRIMARY]
GO
CREATE INDEX [IX_conrecled_2] ON [dbo].[conrecled]([conCardNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_conrecled] ON [dbo].[conrecled]([PeoNo]) ON [PRIMARY]
GO
condate>='2009-11-01'and condate<='2009-11-21'
改成condate between '2009-11-01' and '2009-11-21'
最好给出表结构,测试数据,计算方法和正确结果.
select
b.ddate 日期,b.contermno 终端号,a.termdept 商户部门,b.cishu 交易笔数,b.sumconmoney 交易金额
from
madmterm a,
(select convert(varchar(10),condate,120) as ddate,contermno,count(*) as cishu,sum(conmoney) as sumconmoney
from
conrecledroll where condate>='2009-11-01'and condate<='2009-11-21' and contype='消费'and
exists (select termno from madmterm where termdept='商户A' and b.contermno=termno)
group by
contermno,convert(varchar(10),condate,120))b where a.termno=b.contermno
order by
b.ddate,b.contermno