常用的一些数据统计SQL语句

只有在作统计分析的时候，你才能感觉到数据库的强大，才能感觉到数字游戏的趣味性所在。当然，对于企业而言，则是用于进行决策分析的最好支持之一. 最近刚完工的一个项目中对SQL的运用，让我又对SQL的理解与使用加深了一层。很高兴与大家分享其中的乐趣.
废话少说，我们先建一个原始表吧。


新建表：

程序代码

Create TABLE [dbo].[DezaiCn_Sale] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [日期] datetime NOT NULL,
  [销售量] int NOT NULL,
  [店铺名] char(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  CONSTRAINT [PK_Test_Sale] PRIMARY KEY CLUSTERED ([ID])
)
ON [PRIMARY]
GO

如图：





生成测试数据：

程序代码

/* Data for the `dbo.DezaiCn_Sale` table  (Records 1 - 18) */


Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080101', 2500, N'A')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080101', 2700, N'B')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080201', 3600, N'C')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080201', 5800, N'D')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080301', 2500, N'A')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080301', 2700, N'B')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080401', 3600, N'C')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080401', 5800, N'D')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080301', 2500, N'A')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080101', 2700, N'B')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080501', 3600, N'C')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080701', 4000, N'D')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20081201', 2500, N'A')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080301', 2700, N'B')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20081101', 6500, N'C')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20081001', 5800, N'D')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080601', 3800, N'D')
GO

Insert INTO [dbo].[DezaiCn_Sale] ([日期], [销售量], [店铺名])
VALUES ('20080801', 5400, N'B')
GO

有了数据，游戏就开始了.呵呵.一个一个来吧，哈哈.

<1>查询所有数据

Select *
FROM DezaiCn_Sale




<2>查询指定销售商的数据

Select *
FROM DezaiCn_Sale where 店铺名='A'




<3>查询指定销售商某日的数据

Select *
FROM DezaiCn_Sale
Where (店铺名 = 'A') AND (日期 = '2008-12-1')





<4>查询指定销售商某时间段的数据

Select *
FROM DezaiCn_Sale
Where (店铺名 = 'A') AND (日期 BETWEEN '2008-1-1' AND '2008-9-1')

注意：前面的日期要小于后面的日期,这里也可以用>= 与<=来实现同样的结果




<5>查询所有销售商按月统计的销售量

Select 日期, 店铺名, 销售量
FROM DezaiCn_Sale
GROUP BY 日期, 店铺名, 销售量
orDER BY 店铺名




这里其实就是排列了一下数据.与<1>中的结果没有什么多大变化.


<6>查询各销售商的销售总量


Select 店铺名, SUM(销售量) AS 销售总量
FROM DezaiCn_Sale
GROUP BY 店铺名



<6>查询指定销售商指定时间段的销售总量

Select 店铺名, SUM(销售量) AS 销售总量
FROM DezaiCn_Sale
Where (店铺名 = 'A') AND (日期 BETWEEN '2008-1-1' AND '2008-9-1')
GROUP BY 店铺名





<7>查询具体时间的总的销售总量

Select SUM(销售量) AS 销售总量, 日期
FROM DezaiCn_Sale
Where (日期 = '2008-3-1')
GROUP BY 日期






上面列了这么多，我觉得大家应该很清楚其中的变化了。无非就是Sum(),Count(),Max()函数及Between的利用 再加Group By 的使用，而以上这些都是很基础又很实用的。上面这些在统计中一般用于进行数据统计时用.如果再增加一个字段列为产品名称或产品分类，也是要利用Group By去实现统计数据功能。

下面这个表基于增加了产品ID字段进行数据统计分析功能,主要是横向的比较了.关键是行转列，列转行的操作，如果是用存储过程，那就是拼字符串了。






<1>指定店铺在某时间段的销售数据(横向)

我这里用存储数据来实现,这里要注意的是拼字符串时单双引号的使用，我在此吃了不少苦头。

程序代码

Create procedure  DezaiCnTestSale_SalerCompareSaleByDate  /*代理销售产品比较分析*/


@ProductIDArray varchar(1000), /*产品ID组*/

@begindate datetime, /*比较开始日期*/

@endDate datetime,/*比较结束日期*/

@Shop varchar(20) /*店铺名*/

as



declare @sql varchar(8000)

set @sql = 'select 产品ID '


select @sql = @sql + ' , max(case Convert(Varchar(10),日期,120) when ''' + Convert(Varchar(10),日期,120) + ''' then 销售量 else 0 end) [' + Convert(Varchar(10),日期,120) + ']'


from (select distinct 日期 from DezaiCn_Sale where 日期 between @begindate  and @endDate  ) as a

set @sql = @sql + ' from DezaiCn_Sale where   产品ID in ('+@ProductIDArray+') and 店铺名='''+@Shop+'''  group by 产品ID'


exec(@sql)
GO

运行效果：

程序代码

DECLARE @RC int
DECLARE @ProductIDArray varchar(1000)
DECLARE @begindate datetime
DECLARE @endDate datetime
DECLARE @Shop varchar(20)
Select @ProductIDArray = '1,3,4'
Select @begindate = '2008-1-1'
Select @endDate = '2008-9-1'
Select @Shop = 'A'
EXEC @RC = [TEST].[dbo].[DezaiCnTestSale_SalerCompareSaleByDate] @ProductIDArray, @begindate, @endDate, @Shop
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: TEST.dbo.DezaiCnTestSale_SalerCompareSaleByDate'
Select @PrnLine = '    返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

效果：





<2>指定产品在某时间段的所选销售商的销售量数据比较

在这里我加了一个店铺名ID的字段，因为用数字进行拼字符串比用字符拼要顺畅得多。(个人之见)

程序代码

/*
作者: Dezai
日期: 2008-12-01
作用:对具体某产品根据所选择的代理商进行销售比较分析
*/

Create procedure DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct

@ShopArray varchar(1000), /*店铺ID数组*/

@begindate datetime, /*开始时间*/

@endDate datetime,/*结束时间*/

@产品ID varchar(10) /*具体产品ID*/

as



declare @sql varchar(8000)

set @sql = 'select 店铺ID '


select @sql = @sql + ' , max(case Convert(Varchar(10),日期,120) when ''' + Convert(Varchar(10),日期,120) + ''' then 销售量 else 0 end) [' + Convert(Varchar(10),日期,120) + ']'


from (select distinct 日期 from DezaiCn_Sale where 日期 between @begindate  and @endDate  ) as a

set @sql = @sql + ' from DezaiCn_Sale where 产品ID='+@产品ID+' and  店铺ID in ('+@ShopArray+') group by 店铺ID'


exec(@sql)
GO

效果:

程序代码

DECLARE @RC int
DECLARE @ShopArray varchar(1000)
DECLARE @begindate datetime
DECLARE @endDate datetime
DECLARE @product_ID varchar(10)
Select @ShopArray = '1,3'
Select @begindate = '2008-1-1'
Select @endDate = '2008-12-1'
Select @product_ID = '3'
EXEC @RC = [TEST].[dbo].[DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct] @ShopArray, @begindate, @endDate, @product_ID
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: TEST.dbo.DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct'
Select @PrnLine = '    返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

结果：





我还是再次推荐这篇文章，非常实用:

SQL行列转换大全
http://www.dezai.cn/article_show.asp?ArticleID=32432



一点经验，与大家分享，欢迎指正.
http://www.dezai.cn/blog/article.asp?id=242