源代码如下:
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
DECLARE @dt3 VARCHAR(10)
DECLARE @sql VARCHAR(MAX)
SET @dt1 = '2012-01-01'
SET @dt2 = '2012-01-09'
SET @dt3=@dt1IF CONVERT(VARCHAR(20),@dt2,120)<=GETDATE()
BEGIN
WHILE(@dt3 <= @dt2)
IF(SELECT COUNT(1) FROM Sale_Order WHERE dtCreate>=@dt3 AND dtCreate < DATEADD(DAY,1,@dt3))=0
BEGIN
PRINT @dt3
SET @dt3=CONVERT(VARCHAR(10),DATEADD(DAY,1,@dt3),120)
END
ELSE
BEGIN
SET @dt3=CONVERT(VARCHAR(10),DATEADD(DAY,1,@dt3),120)
END
SET @sql = 'SELECT
a.strCode AS [商品编号]'
-- , a.strProduct AS [商品名]
-- , a.strBrand AS [产品品牌]
-- , d.strSupplier AS [供应商]'SELECT @sql = @sql +
', SUM(CASE CONVERT(VARCHAR(10),c.dtCreate,120)
WHEN ''' + dtCreate + '''
THEN b.intNumber
ELSE 0
END) [' + dtCreate + ']'
FROM
(SELECT
DISTINCT CONVERT(VARCHAR(10),dtCreate,120) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate < DATEADD(DAY,1,@dt2)
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY
dtCreate)AS a
PRINT (@SQL)
SET @sql = @sql +', SUM(b.intNumber) AS [销售总量]
FROM Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
WHERE c.dtCreate >= ' + QUOTENAME(CONVERT(VARCHAR(10),@dt1,120),'''') +
'AND c.dtCreate <DATEADD(DAY,1, ' + QUOTENAME(CONVERT(VARCHAR(10),@dt2,120),'''') + ')
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
ORDER BY
[销售总量] DESC'
EXEC(@sql)
END
ELSE
PRINT('输入的时间必须在现在之前!')
运行结果如下:
产品编号 2011-1-3 2011-1-4 2011-1-5 2011-1-6 2011-1-7 销售总量
233150100007 0 10 0 0 0 10
101010100007 0 0 0 2 0 2
101010100008 0 0 1 0 0 1
101010100017 0 0 1 0 0 1
101010100019 0 0 0 1 0 1
101030100002 0 0 1 0 0 1
问:我输入的是2011-1-1 到2011-1-9号的,怎样才能让没有显示的几天销售量显示出来,并且为0呢!
108150100008 0 1 0 0 0 1
DECLARE @dt1 AS VARCHAR(10)
DECLARE @dt2 AS VARCHAR(10)
DECLARE @dt3 VARCHAR(10)
DECLARE @sql VARCHAR(MAX)
SET @dt1 = '2012-01-01'
SET @dt2 = '2012-01-09'
SET @dt3=@dt1IF CONVERT(VARCHAR(20),@dt2,120)<=GETDATE()
BEGIN
WHILE(@dt3 <= @dt2)
IF(SELECT COUNT(1) FROM Sale_Order WHERE dtCreate>=@dt3 AND dtCreate < DATEADD(DAY,1,@dt3))=0
BEGIN
PRINT @dt3
SET @dt3=CONVERT(VARCHAR(10),DATEADD(DAY,1,@dt3),120)
END
ELSE
BEGIN
SET @dt3=CONVERT(VARCHAR(10),DATEADD(DAY,1,@dt3),120)
END
SET @sql = 'SELECT
a.strCode AS [商品编号]'
-- , a.strProduct AS [商品名]
-- , a.strBrand AS [产品品牌]
-- , d.strSupplier AS [供应商]'SELECT @sql = @sql +
', SUM(CASE CONVERT(VARCHAR(10),c.dtCreate,120)
WHEN ''' + dtCreate + '''
THEN b.intNumber
ELSE 0
END) [' + dtCreate + ']'
FROM
(SELECT
DISTINCT CONVERT(VARCHAR(10),dtCreate,120) dtCreate
FROM Sale_Order
WHERE
dtCreate >= @dt1
AND dtCreate < DATEADD(DAY,1,@dt2)
AND intObjectID >= 0
AND intObjectID <> 427
AND intObjectID <> 74
AND intObjectID <> 75
GROUP BY
dtCreate)AS a
PRINT (@SQL)
SET @sql = @sql +', SUM(b.intNumber) AS [销售总量]
FROM Setting_Product a
INNER JOIN Sale_OrderDetails b ON a.intProductID=b.intProductID
INNER JOIN Sale_Order c ON b.intOrderID=c.intOrderID
INNER JOIN Setting_Supplier d ON d.intSupplierID=a.intSupplierID
WHERE c.dtCreate >= ' + QUOTENAME(CONVERT(VARCHAR(10),@dt1,120),'''') +
'AND c.dtCreate <DATEADD(DAY,1, ' + QUOTENAME(CONVERT(VARCHAR(10),@dt2,120),'''') + ')
GROUP BY
a.strCode
, a.strProduct
, a.strBrand
, d.strSupplier
ORDER BY
[销售总量] DESC'
EXEC(@sql)
END
ELSE
PRINT('输入的时间必须在现在之前!')
运行结果如下:
产品编号 2011-1-3 2011-1-4 2011-1-5 2011-1-6 2011-1-7 销售总量
233150100007 0 10 0 0 0 10
101010100007 0 0 0 2 0 2
101010100008 0 0 1 0 0 1
101010100017 0 0 1 0 0 1
101010100019 0 0 0 1 0 1
101030100002 0 0 1 0 0 1
问:我输入的是2011-1-1 到2011-1-9号的,怎样才能让没有显示的几天销售量显示出来,并且为0呢!
108150100008 0 1 0 0 0 1
解决方案 »
- SQL导入导出EXEL问题中的用户和密码
- 大胆敢问关于数据库存储换行符
- SQL SERVER数据创建过程中,数据文件与日志文件增长方式的自动与不自动的利弊
- dC#如何使用存储过程
- SQL判断如果为空就查询另外一个表
- 求教一多条件的查询语句,在线等
- 关于select into 的用法
- sql server 表中如何把ID相同的行再把的同一个字段值相加
- 求救!!!sql server使用链接服务器删除Oracle的数据表的数据时异常
- 我对数据库不熟,在BCB中对SQL2000的权限控制应该怎么做?
- 怎么将老数据库内容添加到新数据库
- Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序
这例子很多找不到去我博客
SELECT @sql = @sql +
', SUM(CASE CONVERT(VARCHAR(10),c.dtCreate,120)
WHEN ''' + dtCreate + '''
THEN b.intNumber
ELSE 0
END) [' + dtCreate + ']'
FROM
(SELECT
DISTINCT CONVERT(VARCHAR(10),dateadd(dd,number,@dt1),120) dtCreate
FROM master..spt_values
WHERE [type] = 'p' and number between 0 and datediff(dd,@dt1,@dt2))AS a
where type='P' and
number between 1 and 1000输出2008年至今以来的月份列表:create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GODECLARE
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'
SELECT * FROM MonDROP TABLE Mon