有一张表,设结构如下:id
name
count
insertdate假设数据
1 a 1 2006-02-03
2 b 1 2006-02-05
3 c 2 2006-03-06
4 d 1 2006-04-01
. . . .
. . . .现在想检索出以下结构数据:检索出一年中某天如果有count值则显示其值,如果此天没有值则显示""行:一个月中的31天
列:一年中的12个月
如下: 日 1 2 3 4 5 6 7 8 9 10....
1
2 1 1
3 2
4 1
.
.
.
想从ResultSet中得到一Vector(只包含数据count即可),最终在VTable中显示成上面的样子。即能够将select出的语句利用vector.add(rs.getString(""))添加即可。
这个sql怎么写呢?
(idNO int IDENTITY(1,1) not null,
IDNAME CHAR(1),
QTY INT,
SDATE DATETIME)INSERT TEST(IDNAME,QTY,SDATE)
SELECT 'A',1,'2006-02-03'
UNION ALL SELECT 'B',1,'2006-02-05'
UNION ALL SELECT 'C',2,'2006-03-06'
UNION ALL SELECT 'D',1,'2006-04-01'
SELECT * FROM TEST
SELECT MONTH(SDATE) AS SMONTH,DAY(SDATE) AS SDAY,QTY INTO TEST2 FROM TEST
--SELECT * FROM TEST2
select SDAY,sum(case SMONTH when 1 then QTY else 0 end) as [1],
sum(case SMONTH when 2 then QTY else 0 end) as [2],
sum(case SMONTH when 3 then QTY else 0 end) as [3],
sum(case SMONTH when 4 then QTY else 0 end) as [4],
sum(case SMONTH when 5 then QTY else 0 end) as [5],
sum(case SMONTH when 6 then QTY else 0 end) as [6],
sum(case SMONTH when 7 then QTY else 0 end) as [7],
sum(case SMONTH when 8 then QTY else 0 end) as [8],
sum(case SMONTH when 9 then QTY else 0 end) as [9],
sum(case SMONTH when 10 then QTY else 0 end) as [10],
sum(case SMONTH when 11 then QTY else 0 end) as [11],
sum(case SMONTH when 12 then QTY else 0 end) as [12]
from test2
group by SDAY
DROP TABLE TEST2
DROP TABLE TEST
YiZhiNet写的月份都统计出来了,不出不出现,是日没有统计出来
Create Table TEST
(id Int,
name Varchar(10),
[count] Int,
insertdate DateTime)
Insert TEST Select 1, 'a', 1, '2006-02-03'
Union All Select 2, 'b', 1, '2006-02-05'
Union All Select 3, 'c', 2, '2006-03-06'
Union All Select 4, 'd', 1, '2006-04-01'
GO
--建立存儲過程
Create ProceDure SP_TEST(@Year Int)
As
Begin
Select TOP 31 ID=Identity(Int,0,1) Into # From SysColumns
Select DateAdd(dd,ID,Rtrim(@Year)+'-01-01') As TempDate Into #T From #
Select
Day(A.TempDate) As 日,
SUM(Case Month(insertdate) When 1 Then [count] Else 0 End) As [1],
SUM(Case Month(insertdate) When 2 Then [count] Else 0 End) As [2],
SUM(Case Month(insertdate) When 3 Then [count] Else 0 End) As [3],
SUM(Case Month(insertdate) When 4 Then [count] Else 0 End) As [4],
SUM(Case Month(insertdate) When 5 Then [count] Else 0 End) As [5],
SUM(Case Month(insertdate) When 6 Then [count] Else 0 End) As [6],
SUM(Case Month(insertdate) When 7 Then [count] Else 0 End) As [7],
SUM(Case Month(insertdate) When 8 Then [count] Else 0 End) As [8],
SUM(Case Month(insertdate) When 9 Then [count] Else 0 End) As [9],
SUM(Case Month(insertdate) When 10 Then [count] Else 0 End) As [10],
SUM(Case Month(insertdate) When 11 Then [count] Else 0 End) As [11],
SUM(Case Month(insertdate) When 12 Then [count] Else 0 End) As [12]
From #T A Left Join TEST B
On Day(A.TempDate)=Day(B.insertdate) And Year(A.TempDate)= Year(insertdate)
Group By Day(A.TempDate)
Drop Table #T,#
End
GO
--測試
EXEC SP_TEST 2006
GO
--刪除測試環境
Drop Table TEST
Drop ProceDure SP_TEST
paoluo(一天到晚游泳的鱼) 是不是看错了,,
YiZhiNet写的月份都统计出来了,不出不出现,是日没有统计出来
-------------------------
是說錯了,是沒有出現的日沒有統計。
(id INT,
name VARCHAR(10),
count INT,
insertdate DATETIME
)
INSERT tb SELECT 1, 'a' ,1, '2006-02-03'
UNION ALL SELECT 2, 'b' ,1,'2006-02-05'
UNION ALL SELECT 3, 'c' ,2, '2006-03-06'
UNION ALL SELECT 4, 'd' ,1, '2006-04-01'
SELECT count,b.id m,DAY(insertdate) d
INTO tb1
FROM
(SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) b
LEFT JOIN tb a
ON b.id=MONTH(insertdate)DECLARE @MaxFieldNum INT,@i INT
SELECT * FROM tb1
SELECT @i=1,@MaxFieldNum=31
DECLARE @sql VARCHAR(8000),@sql1 VARCHAR(1000)
SET @sql=''
WHILE @i<=@MaxFieldNum
SELECT @sql=@sql + ',[' + RTRIM(@i) + ']=' + 'ISNULL(RTRIM((SELECT [count] FROM tb1 b WHERE b.m=a.m AND ' + RTRIM(@i) + '=b.d)),'''')',@i=@i+1
PRINT @sql1
SELECT @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM tb1 a GROUP BY m'
EXEC(@sql)DROP TABLE tb
DROP TABLE tb1/*
结果
1 2 3 4 5 6 7 ... 31
1
2 1 1
3 2
4 1
.
.
12
*/
WHILE 那一段生成那个@SQL语句,用连表可以直接得出结果.
这样要简单些,方法联机帮助上有