表结构:
id(自增) empid(人员id) subject(标题) fdate(开始时间) tdate(结束时间)
1 220 内容1 2009-01-01 8:00 2009-01-01 17:00
2 220 内容2 2009-01-01 9:00 2009-01-01 13:00
3 330 内容3 2009-01-03 8:00 2009-01-05 17:00
4 221 内容4 2009-01-01 8:00 2009-02-08 17:00
-----------------------------------------------------------------------------------
需求:给出一个时间段比如:2009-01-01 至 2009-01-31
查出所有人员在这个时间段里的:subject 结构如下:
empid 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 一直到2009-01-31 这是表结构,数据如下:
220 内容1 + 内容2
330 内容3 内容3 内容3
331 内容4 内容4 内容4 内容4 内容4 ....... 内容4
请问该怎样写?就是把选择的时间范围作为列名去查询数据.
id(自增) empid(人员id) subject(标题) fdate(开始时间) tdate(结束时间)
1 220 内容1 2009-01-01 8:00 2009-01-01 17:00
2 220 内容2 2009-01-01 9:00 2009-01-01 13:00
3 330 内容3 2009-01-03 8:00 2009-01-05 17:00
4 221 内容4 2009-01-01 8:00 2009-02-08 17:00
-----------------------------------------------------------------------------------
需求:给出一个时间段比如:2009-01-01 至 2009-01-31
查出所有人员在这个时间段里的:subject 结构如下:
empid 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 一直到2009-01-31 这是表结构,数据如下:
220 内容1 + 内容2
330 内容3 内容3 内容3
331 内容4 内容4 内容4 内容4 内容4 ....... 内容4
请问该怎样写?就是把选择的时间范围作为列名去查询数据.
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb
****************************************
* Date: 2009-02-11 16:40:48 *
* Author: kimi *
* Type: Test *
****************************************
*/
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT,EMPID INT,SUBJECT VARCHAR(10),FDATE DATETIME,TDATE DATETIME)
Insert tb
SELECT 1,220,N'内容1','2009-01-01 8:00','2009-01-01 17:00' UNION ALL
SELECT 2,220,N'内容2','2009-01-01 9:00','2009-01-01 13:00' UNION ALL
SELECT 3,330,N'内容3','2009-01-03 8:00','2009-01-05 17:00' UNION ALL
SELECT 4,221,N'内容4','2009-01-01 8:00','2009-02-08 17:00'IF NOT OBJECT_ID('F_TEST') IS NULL DROP FUNCTION F_TEST
GO
CREATE FUNCTION F_TEST(@EMPID VARCHAR(10))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @RE VARCHAR(50)
SELECT @RE=ISNULL(@RE,'')+','+SUBJECT FROM TB WHERE EMPID=@EMPID
RETURN(STUFF(@RE,1,1,''))
END
GODECLARE @SQL VARCHAR(8000),@BDT DATETIME,@EDT DATETIME
SELECT @SQL='',@BDT='2009-01-01',@EDT='2009-01-31'
WHILE @BDT<=@EDT
BEGIN
SELECT @SQL=@SQL+ ','+ QUOTENAME(CONVERT(VARCHAR(10),@BDT,120))+
'=MAX(CASE WHEN CONVERT(VARCHAR,DATEDIFF(DAY,'''+CONVERT(VARCHAR,@BDT,120)+''',TDATE))>-1 THEN +DBO.F_TEST(EMPID) ELSE '''' END)'
SET @BDT=@BDT+1
ENDEXEC('SELECT EMPID'+@SQL+'FROM TB
GROUP BY EMPID') /*EMPID 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
220 内容1,内容2
221 内容4 内容4 内容4 内容4 内容4 内容4
330 内容3 内容3 内容3 内容3 内容3 （所影响的行数为 3 行）
*/
SELECT @SQL='',@BDT='2009-01-01',@EDT='2009-01-31'
WHILE @BDT<=@EDT
BEGIN
SELECT @SQL=@SQL+ ','+ QUOTENAME(CONVERT(VARCHAR(10),@BDT,120))+
'=MAX(CASE WHEN '''+CONVERT(VARCHAR(10),@BDT,120)+''' BETWEEN CONVERT(VARCHAR(10),FDATE,120) AND FDATE THEN DBO.F_TEST(EMPID) ELSE '''' END)'
SET @BDT=@BDT+1
END
EXEC('SELECT EMPID'+@SQL+'FROM TB
GROUP BY EMPID')
/*
EMPID 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
220 内容1,内容2
221 内容4
330 内容3 （所影响的行数为 3 行）*/
@SBDT VARCHAR(10),@SEDT VARCHAR(10)
SELECT @SQL1='',@SQL2='',@BDT='2009-01-01',@EDT='2009-01-20'
--动态字符太长了，超过8000个字节，分两个第一次到20号
WHILE @BDT<=@EDT
BEGIN
SELECT @SQL1=@SQL1+ ','+ QUOTENAME(CONVERT(VARCHAR(10),@BDT,120))+
'=MAX(CASE WHEN ('''+CONVERT(VARCHAR(10),@BDT,120)+'''>=CONVERT(VARCHAR(10),FDATE,120) AND '''+CONVERT(VARCHAR(10),@BDT,120)+'''<=(TDATE)) THEN DBO.F_TEST(EMPID) ELSE '''' END)'
SET @BDT=@BDT+1
END--接着从21号开始再接
SET @EDT='2009-01-21'
WHILE @BDT<=@EDT
BEGIN
SELECT @SQL2=@SQL2+ ','+ QUOTENAME(CONVERT(VARCHAR(10),@BDT,120))+
'=MAX(CASE WHEN ('''+CONVERT(VARCHAR(10),@BDT,120)+'''>=CONVERT(VARCHAR(10),FDATE,120) AND '''+CONVERT(VARCHAR(10),@BDT,120)+'''<=(TDATE)) THEN DBO.F_TEST(EMPID) ELSE '''' END)'
SET @BDT=@BDT+1
ENDEXEC('SELECT EMPID'+@SQL1+@SQL2+'FROM TB
GROUP BY EMPID')
/*
EMPID 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
220 内容1,内容2
221 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4
330 内容3 内容3 内容3 （所影响的行数为 3 行）*/
insert into tb values(1 , '220' , '内容1' , '2009-01-01 8:00:00' , '2009-01-01 17:00:00')
insert into tb values(2 , '220' , '内容2' , '2009-01-01 9:00:00' , '2009-01-01 13:00:00')
insert into tb values(3 , '330' , '内容3' , '2009-01-03 8:00:00' , '2009-01-05 17:00:00')
insert into tb values(4 , '221' , '内容4' , '2009-01-01 8:00:00' , '2009-02-08 17:00:00')
godeclare @fdate as datetime
declare @tdate as datetime
set @fdate = '2009-01-01'
set @tdate = '2009-01-31'--1、建立一个辅助的临时表1
SELECT TOP 8000 id = identity(int,0,1) INTO tmp1 FROM syscolumns a, syscolumns b --2、按照fdate,tdate生成连续的数据插入临时表2
select empid , subject , convert(varchar(10),dt,120) dt into tmp2 from
(
select m.empid , m.subject , dt = dateadd(day , n.id , m.fdate) from tb m , tmp1 n where convert(varchar(10),m.fdate,120) between @fdate and @tdate and convert(varchar(10),dateadd(day , n.id , m.fdate),120) <= convert(varchar(10),m.tdate,120)
) t
where convert(varchar(10),dt,120) between @fdate and @tdate
order by empid , dt--3、使用动态SQL进行行列转换
declare @sql varchar(8000)
set @sql = 'select empid '
select @sql = @sql + ' , max(case dt when ''' + dt + ''' then subject else '''' end) [' + dt + ']'
from (select distinct dt from tmp2) as a
set @sql = @sql + ' from tmp2 group by empid'
exec(@sql) drop table tb , tmp1 , tmp2/*
empid 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10 2009-01-11 2009-01-12 2009-01-13 2009-01-14 2009-01-15 2009-01-16 2009-01-17 2009-01-18 2009-01-19 2009-01-20 2009-01-21 2009-01-22 2009-01-23 2009-01-24 2009-01-25 2009-01-26 2009-01-27 2009-01-28 2009-01-29 2009-01-30 2009-01-31
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
220 内容2
221 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4
330 内容3 内容3 内容3 */
if object_id('tb')>0 drop table tbcreate table tb(id int,empid int,subject varchar(10),fdate datetime,tdate datetime)
insert into tb values(1 , '220' , '内容1' , '2009-01-01 8:00:00' , '2009-01-01 17:00:00')
insert into tb values(2 , '220' , '内容2' , '2009-01-01 9:00:00' , '2009-01-01 13:00:00')
insert into tb values(3 , '330' , '内容3' , '2009-01-03 8:00:00' , '2009-01-05 17:00:00')
insert into tb values(4 , '221' , '内容4' , '2009-01-01 8:00:00' , '2009-02-08 17:00:00')
gocreate table #1 (id int identity(1,1),dd datetime)
declare @beginDate datetime ,@endDate datetime ,@t datetime
select @beginDate = '2009-1-1',@endDate = '2009-1-31'
set @t = @beginDate
while @t <= @endDate
begin
insert #1 values(@t)
set @t = dateadd(dd,1,@t)
enddeclare @sql nvarchar(max)
set @sql = ' select empid '
select @sql = @sql + ',['+convert(nvarchar(10),dd,120)+'] = case when convert(nvarchar(10),fdate,120) <= ''' +convert(nvarchar(10),dd,120)+''' and tdate >= '''+convert(nvarchar(10),dd,120)+''' then subject else null end ' from #1
set @sql =@sql +' from tb '
exec(@sql)empid 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10 2009-01-11 2009-01-12 2009-01-13 2009-01-14 2009-01-15 2009-01-16 2009-01-17 2009-01-18 2009-01-19 2009-01-20 2009-01-21 2009-01-22 2009-01-23 2009-01-24 2009-01-25 2009-01-26 2009-01-27 2009-01-28 2009-01-29 2009-01-30 2009-01-31
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
220 内容1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
220 内容2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
330 NULL NULL 内容3 内容3 内容3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
221 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4(4 row(s) affected)
谢谢"dawugui",你的答案不是我想要的结果,内容1不见了!
谢谢"yangsnow_rain_wind "你的答案也不是我想要的结果.另外也要谢谢各位顶贴的朋友,包括"水族杰伦"各位说说,我是用sql去处理好,还是到前台去用C#去处理好一点?
INSERT gcontent SELECT 1,220,'内容1','2009-01-01 8:00','2009-01-01 17:00'
union all select 2,220,'内容2','2009-01-01 9:00','2009-01-01 13:00'
union all select 3,330,'内容3','2009-01-03 8:00','2009-01-05 17:00'
union all select 4,221,'内容4','2009-01-01 8:00','2009-02-08 17:00'
go
CREATE FUNCTION getConts(@id int,@s VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @l VARCHAR(1000)
SELECT @l=ISNULL(@l+'+','') +SUBJECT FROM gcontent WHERE empid=@id and @s between LEFT(fdate,10) AND LEFT(tdate,10)
RETURN @l
END
goDECLARE @s VARCHAR(10),@e VARCHAR(10)
SET @s='2009-01-01 00:00:01'
SET @e='2009-01-31 23:59:59'DECLARE @t TABLE(id int IDENTITY(0,1),a VARCHAR(20))INSERT @t SELECT TOP 31 CAST('' AS VARCHAR(20)) FROM syscolumns s
UPDATE @t SET a=convert(varchar(10),DATEADD(DAY,id,@s),120)DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','') + ' max(case when '''+a+''' between left(fdate,10) and left(tdate,10) then dbo.getconts(empid,'''+a+''') ELSE '''' end) ['+a+']' FROM @t g ORDER BY a exec('select empid,'+@sql+' from gcontent group by empid')
--result
/*empid 2009-01-01 2009-01-02 2009-01-03 2009-01-04 2009-01-05 2009-01-06 2009-01-07 2009-01-08 2009-01-09 2009-01-10 2009-01-11 2009-01-12 2009-01-13 2009-01-14 2009-01-15 2009-01-16 2009-01-17 2009-01-18 2009-01-19 2009-01-20 2009-01-21 2009-01-22 2009-01-23 2009-01-24 2009-01-25 2009-01-26 2009-01-27 2009-01-28 2009-01-29 2009-01-30 2009-01-31
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
220 内容1+内容2
221 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4 内容4
330 内容3 内容3 内容3
*/