MS-SQL Server 疑难问题

有一表A
id date name
1 2007-01-01 CCC
1 2007-01-01 DDD
1 2007-01-01 VV
1 2007-02-01 TTTTT
2 2007-01-01 GG
3 2007-01-02 CCC
3 2007-01-01 CCC
4 2007-01-04 YY

输入1月要得到
日期 CCC DDD VV GG YY
1号 2 1 1 1 0
2号 1 0 0 0 0
3号 0 0 0 0 0
4号 0 0 0 0 1
. 0 0 0 0 0
. 0 0 0 0 0
. 0 0 0 0 0
31号 0 0 0 0 0
合计 3 1 1 1 1

CREATE TABLE A(id int, date datetime, name varchar(10))
INSERT A SELECT 1, '2007-01-01', 'CCC'
UNION ALL SELECT 1, '2007-01-01', 'DDD'
UNION ALL SELECT 1, '2007-01-01', 'VV'
UNION ALL SELECT 1, '2007-02-01', 'TTTTT'
UNION ALL SELECT 2, '2007-01-01', 'GG'
UNION ALL SELECT 3, '2007-01-02', 'CCC'
UNION ALL SELECT 3, '2007-01-01', 'CCC'
UNION ALL SELECT 4, '2007-01-04', 'YY'
GO

-- 查询的存储过程
CREATE PROC p_qry
@yearmonth int
AS
SET NOCOUNT ON
DECLARE @BeginDate datetime, @EndDate datetime, @row int
SELECT
@BeginDate = CONVERT(datetime, RIGHT(@yearmonth * 100 + 1, 8)),
@EndDate = DATEADD(Month, 1, @BeginDate),
@row = Day(@EndDate - 1)

DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

SELECT
日期 = CASE GROUPING(D.id)
WHEN 0 THEN CONVERT(varchar(10), D.id) + '号'
ELSE '合计' END,
CCC = ISNULL(SUM(CASE A.name WHEN 'CCC' THEN 1 ELSE 0 END), 0),
DDD = ISNULL(SUM(CASE A.name WHEN 'DDD' THEN 1 ELSE 0 END), 0),
VV = ISNULL(SUM(CASE A.name WHEN 'VV' THEN 1 ELSE 0 END), 0),
GG = ISNULL(SUM(CASE A.name WHEN 'GG' THEN 1 ELSE 0 END), 0),
YY = ISNULL(SUM(CASE A.name WHEN 'YY' THEN 1 ELSE 0 END), 0)
FROM(
SELECT * FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
RIGHT JOIN @dt D
ON A.date = D.date
GROUP BY D.ID WITH ROLLUP
ORDER BY GROUPING(D.id), D.id
GO

-- 调用
EXEC p_qry 200601
GO

-- 删除测试
DROP TABLE A
DROP PROC P_qry
-- 调用
EXEC p_qry 200701

-- 结果:

日期CCCDDDVVGGYY
1号00000
2号10000
3号00000
4号00001
5号00000
6号00000
7号00000
8号00000
9号00000
10号00000
11号00000
12号00000
13号00000
14号00000
15号00000
16号00000
17号00000
18号00000
19号00000
20号00000
21号00000
22号00000
23号00000
24号00000
25号00000
26号00000
27号00000
28号00000
29号00000
30号00000
31号00000
合计10001
如果要根据满足条件的name的值来动态确定列名, 则存储过程改为:


-- 查询的存储过程
CREATE PROC p_qry
@yearmonth int
AS
SET NOCOUNT ON
DECLARE @BeginDate datetime, @EndDate datetime, @row int
SELECT
@BeginDate = CONVERT(datetime, RIGHT(@yearmonth * 100 + 1, 8)),
@EndDate = DATEADD(Month, 1, @BeginDate),
@row = Day(@EndDate - 1)

DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

DECLARE @s nvarchar(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(name)
+ '= ISNULL(SUM(CASE A.name WHEN ' + QUOTENAME(name, '''') + ' THEN 1 ELSE 0 END), 0)'
FROM(
SELECT DISTINCT name FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
IF @@ROWCOUNT = 0
RETURN
SET @s = '
DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

SELECT
日期 = CASE GROUPING(D.id)
WHEN 0 THEN CONVERT(varchar(10), D.id) + ''号''
ELSE ''合计'' END' + @s + '
FROM(
SELECT * FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
RIGHT JOIN @dt D
ON A.date = D.date
GROUP BY D.ID WITH ROLLUP
ORDER BY GROUPING(D.id), D.id
'
EXEC sp_executesql @s, N'@row int, @BeginDate datetime, @EndDate datetime', @row, @BeginDate, @EndDate
GO
WHERE date > =@BeginDate
AND date <= @EndDate

这句改了就还了,差个"="
好的给分

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值