按会员进行分组并统计总消费额按消费额进行排序 :如题现有两张表一张表tblCardConsumeList【会员卡的消费记录】
一张表tblMemberCode【会员信息】
tblCardConsumeList表创建SQL语句USE [BasketBall]
GO
/****** 对象: Table [dbo].[tblCardConsumeList] 脚本日期: 07/16/2009 17:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCardConsumeList](
[fldSerialID] [bigint] IDENTITY(1,1) NOT NULL,
[fldBookSerialID] [bigint] NULL,
[fldMemberCardNo] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldConsumeMoney] [money] NOT NULL,
[fldAgio] [money] NOT NULL,
[fldTrueMoney] [money] NOT NULL,
[fldSetUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldUpdateTime] [datetime] NOT NULL,
[fldpaytype] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[fldItemName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_tblCardConsumeList] PRIMARY KEY CLUSTERED
(
[fldSerialID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
tblMemberCode创建SQL语句USE [BasketBall]
GO
/****** 对象: Table [dbo].[tblMemberCode] 脚本日期: 07/16/2009 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMemberCode](
[fldMemberID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldSex] [char](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldBirthday] [datetime] NOT NULL,
[fldCertificateTypeID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCertificateID] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCountry] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCity] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPostCode] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPhone] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldAddress] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldMobilePhone] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldEmail] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPhoto] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[fldCompany] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[fldOwnUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[fldSetUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldUpdateTime] [datetime] NOT NULL,
CONSTRAINT [PK__tblMemberCode__30F848ED] PRIMARY KEY NONCLUSTERED
(
[fldMemberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF--------------------------------------------------
现在要取得字段如下;
fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime,'总的消费金额'
如是按项目分组多一个fldItemName
要3条SQL语句:
1.按各个会员的总消费金额进行排序
2.按项目分组进行统计【统计总的消费记录中那一项消费项目消费金额最高】条件:1.按照fldMemberCardNo会员卡号进行分组
2.对分组后的数据进行消费金额统计【消费金额为fldConsumeMoney】
3.按照分组后消费金额进行降序排序
要求:1.必须能显示出所有的字段
2.我是放在程序里的,要传过来后能接受到值的比如说,用了 as 别名的字段能否接受到等麻烦各位!应该是个很简单的SQL语句,我分组后就不知道前面怎么写了,总报什么字段不在聚合函数内不清楚的可以加我QQ540255090急着用谢谢各位!!!!
一张表tblMemberCode【会员信息】
tblCardConsumeList表创建SQL语句USE [BasketBall]
GO
/****** 对象: Table [dbo].[tblCardConsumeList] 脚本日期: 07/16/2009 17:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCardConsumeList](
[fldSerialID] [bigint] IDENTITY(1,1) NOT NULL,
[fldBookSerialID] [bigint] NULL,
[fldMemberCardNo] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldConsumeMoney] [money] NOT NULL,
[fldAgio] [money] NOT NULL,
[fldTrueMoney] [money] NOT NULL,
[fldSetUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldUpdateTime] [datetime] NOT NULL,
[fldpaytype] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[fldItemName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_tblCardConsumeList] PRIMARY KEY CLUSTERED
(
[fldSerialID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
tblMemberCode创建SQL语句USE [BasketBall]
GO
/****** 对象: Table [dbo].[tblMemberCode] 脚本日期: 07/16/2009 17:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMemberCode](
[fldMemberID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldSex] [char](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldBirthday] [datetime] NOT NULL,
[fldCertificateTypeID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCertificateID] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCountry] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldCity] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPostCode] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPhone] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldAddress] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldMobilePhone] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldEmail] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldPhoto] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[fldCompany] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[fldOwnUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[fldSetUserID] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fldUpdateTime] [datetime] NOT NULL,
CONSTRAINT [PK__tblMemberCode__30F848ED] PRIMARY KEY NONCLUSTERED
(
[fldMemberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF--------------------------------------------------
现在要取得字段如下;
fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime,'总的消费金额'
如是按项目分组多一个fldItemName
要3条SQL语句:
1.按各个会员的总消费金额进行排序
2.按项目分组进行统计【统计总的消费记录中那一项消费项目消费金额最高】条件:1.按照fldMemberCardNo会员卡号进行分组
2.对分组后的数据进行消费金额统计【消费金额为fldConsumeMoney】
3.按照分组后消费金额进行降序排序
要求:1.必须能显示出所有的字段
2.我是放在程序里的,要传过来后能接受到值的比如说,用了 as 别名的字段能否接受到等麻烦各位!应该是个很简单的SQL语句,我分组后就不知道前面怎么写了,总报什么字段不在聚合函数内不清楚的可以加我QQ540255090急着用谢谢各位!!!!
from tblCardConsumeList a join tblMemberCode b on a.fldMemberCardNo=fldMemberID
group by fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime
from tblCardConsumeList a join tblMemberCode b on a.fldMemberCardNo=fldMemberID
group by fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime
order by 总的消费金额
from (
select fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime,总的消费金额=SUM(你消费的那个字段)
from tblCardConsumeList a join tblMemberCode b on a.fldMemberCardNo=fldMemberID
group by fldMemberID,fldName,fldBirthday,fldMobilePhone,fldUpdateTime,你的那个项目列名) t
这是第二题
第二个结果是:进行了排序,但是分组好像没作用,001会员编号还是不在一块
001
刘利 2009-07-11 00:00:00.000 00000000 2009-07-12 14:57:35.543 5.0000 001
刘利 2009-07-11 00:00:00.000 00000000 2009-07-12 18:51:13.060 5.0000 001
刘利 2009-07-11 00:00:00.000 00000000 2009-07-12 21:59:14.580 5.0000 002
aa 2009-07-16 00:00:00.000 13789514589 2009-07-16 16:42:50.680 5.0000 004
004 2009-07-16 00:00:00.000 13778957842 2009-07-16 16:04:13.470 20.0000 003
003 2009-07-16 00:00:00.000 13245789586 2009-07-16 16:04:24.410 30.0000 001
刘利 2009-07-11 00:00:00.000 00000000 2009-07-12 14:44:22.250 40.0000
第三个说什么fldUpdateTime不明确,我用另名它说前缀不对我在仔细看看麻烦你了
5
NULL 001 40.0000 1.0000 32.0000 001 2009-07-12 14:44:22.250 会员卡 NULL 7
NULL 001 5.0000 1.0000 4.0000 001 2009-07-12 14:57:35.543 会员卡 NULL 8
NULL 001 5.0000 1.0000 4.0000 001 2009-07-12 18:51:13.060 会员卡 NULL 9
NULL 001 5.0000 1.0000 4.0000 001 2009-07-12 21:59:14.580 会员卡 NULL 10
NULL 004 20.0000 2.0000 20.0000 001 2009-07-16 16:04:13.470 会员卡 浴资 11
NULL 003 30.0000 10.0000 30.0000 001 2009-07-16 16:04:24.410 会员卡 矿泉水 12
NULL 002 5.0000 1.0000 4.0000 001 2009-07-16 16:42:50.680 会员卡 门票
tblMemberCode中数据
001
刘利 1 2009-07-11 00:00:00.000 001 00000000 00000000 001 001 2009-07-11 15:54:03.810 002
aa 1 2009-07-16 00:00:00.000 001 13478956285 13789514589 001 001 2009-07-16 15:33:43.850 003
003 1 2009-07-16 00:00:00.000 001 13478541245 13245789586 001 001 2009-07-16 15:50:12.420 004
004 1 2009-07-16 00:00:00.000 001 13678752453 13778957842 001 001 2009-07-16 15:51:16.857 005
005 1 2009-07-16 00:00:00.000 001 13245678523 13478541256 001 001 2009-07-16 15:55:44.373 ---------------------------------------------
要得到的数据fldMemberID fldName fldBirthday fldMobilePhone 总的消费金额
001 刘利 2009-07-11 00:00:00.000 00000000 55
003 aa 2009-07-16 00:00:00.000 13789514589 30
004 004 2009-07-16 00:00:00.000 13778957842 20
002 005 2009-07-16 00:00:00.000 13478541256 5
from (select fldMemberCardNo,sum(fldConsumeMoney) 消费金额 from tblCardConsumeList group by fldMemberCardNo ) t join tblMemberCode a on t.fldMemberCardNo=a.fldMemberID order by 消费金额