求一sql语句.

Question

现有数据如下: date                         name            aa  
-------------------------------------------------
2010-06-03 00:00:00 maxiaofei8 301
2010-06-03 00:00:00 mazhen168 10       302
2010-06-03 00:00:00 mb5869425 20       303
2010-06-03 00:00:00 meichsh 11       304
2010-06-03 00:00:00 mianyangdianxin 305
2010-06-04 00:00:00 maxiaofei8 401
2010-06-04 00:00:00 mazhen168 10       402
2010-06-04 00:00:00 mb5869425 20       403
2010-06-04 00:00:00 meichsh 11       404
2010-06-04 00:00:00 mianyangdianxin 405
2010-06-05 00:00:00 maxiaofei8 501
2010-06-05 00:00:00 mazhen168 10       502
2010-06-05 00:00:00 mb5869425 20       503
2010-06-05 00:00:00 meichsh 11       504
2010-06-05 00:00:00 mianyangdianxin 505
....下面还有类似的数据按日期6号,7号这样排下去现在要求查询出的结果如下所示:
  name          date1    date2    date3
-----------------------------------------
maxiaofei8       301     401       501   
mazhen168 10      302     402       502        
...其中date1,date2,date3 表示某连续的3天,如:这里是表示3号,4号,5号
各位大侠帮帮忙了.

Answer 1

排版变态....现有数据如下: date                 name         aa   
-------------------------------------------------
2010-06-03 00:00:00 maxiaofei8      301
2010-06-03 00:00:00 mazhen16810     302
2010-06-03 00:00:00 mb586942520     303
2010-06-03 00:00:00 meichsh11       304
2010-06-03 00:00:00 mianyangdianxin 305
2010-06-04 00:00:00 maxiaofei8      401
2010-06-04 00:00:00 mazhen16810     402
2010-06-04 00:00:00 mb586942520     403
2010-06-04 00:00:00 meichsh11       404
2010-06-04 00:00:00 mianyangdianxin 405
2010-06-05 00:00:00 maxiaofei8      501
2010-06-05 00:00:00 mazhen16810     502
2010-06-05 00:00:00 mb586942520     503
2010-06-05 00:00:00 meichsh11       504
2010-06-05 00:00:00 mianyangdianxin 505
....下面还有类似的数据按日期6号,7号这样排下去现在要求查询出的结果如下所示:
  name      date1     date2     date3
-----------------------------------------
maxiaofei8   301       401       501   
mazhen16810  302       402       502   
...其中date1,date2,date3 表示某连续的3天,如:这里是表示3号,4号,5号
各位大侠帮帮忙了.

Answer 2

if object_id('tb') is not null drop table tb
go
create table tb( date datetime, name varchar(20),aa varchar(5))
insert tb
select '2010-6-3','maxiaofei8','301' union all
select '2010-6-3','mazhen16810','302' union all
select '2010-6-3','mb586942520','303' union all
select '2010-6-3','meichsh11','304' union all
select '2010-6-3','mianyangdianxin','305' union all
select '2010-6-4','maxiaofei8','401' union all
select '2010-6-4','mazhen16810','402' union all
select '2010-6-4','mb586942520','403' union all
select '2010-6-4','meichsh11','404' union all
select '2010-6-4','mianyangdianxin','405' union all
select '2010-6-5','maxiaofei8','501' union all
select '2010-6-5','mazhen16810','502' union all
select '2010-6-5','mb586942520','503' union all
select '2010-6-5','meichsh11','504' union all
select '2010-6-5','mianyangdianxin','505'
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case when datediff(d,date,''' + convert(varchar(10),date,120) + ''')=0 then aa else '''' end) [date' + ltrim(number) + ']'
from ( 
select number+1 as number,(select min(date) from tb)+number as date
from master..spt_values where type='p' 
and number<=datediff(d,(select min(date) from tb),(select max(date) from tb))
) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) /*
name                 date1 date2 date3 
-------------------- ----- ----- ----- 
maxiaofei8           301   401   501
mazhen16810          302   402   502
mb586942520          303   403   503
meichsh11            304   404   504
mianyangdianxin      305   405   505（所影响的行数为 5 行）
*/

Answer 3

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([date] [datetime],[name] [nvarchar](20),[aa] [int])
INSERT INTO [tb]
SELECT '2010-06-03 00:00:00','maxiaofei8','301' UNION ALL
SELECT '2010-06-03 00:00:00','mazhen16810','302' UNION ALL
SELECT '2010-06-03 00:00:00','mb586942520','303' UNION ALL
SELECT '2010-06-03 00:00:00','meichsh11','304' UNION ALL
SELECT '2010-06-03 00:00:00','mianyangdianxin','305' UNION ALL
SELECT '2010-06-04 00:00:00','maxiaofei8','401' UNION ALL
SELECT '2010-06-04 00:00:00','mazhen16810','402' UNION ALL
SELECT '2010-06-04 00:00:00','mb586942520','403' UNION ALL
SELECT '2010-06-04 00:00:00','meichsh11','404' UNION ALL
SELECT '2010-06-04 00:00:00','mianyangdianxin','405' UNION ALL
SELECT '2010-06-05 00:00:00','maxiaofei8','501' UNION ALL
SELECT '2010-06-05 00:00:00','mazhen16810','502' UNION ALL
SELECT '2010-06-05 00:00:00','mb586942520','503' UNION ALL
SELECT '2010-06-05 00:00:00','meichsh11','504' UNION ALL
SELECT '2010-06-05 00:00:00','mianyangdianxin','505'-->SQL查询如下:
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s = STUFF(
           (
               SELECT DISTINCT ','+QUOTENAME(DENSE_RANK()OVER(ORDER BY date)) 
               FROM tb FOR XML PATH('')
           ), 1, 1, ''
       ), @s1 = STUFF(
           (
               SELECT DISTINCT ','+QUOTENAME(DENSE_RANK()OVER(ORDER BY date))+' AS Date'+LTRIM(DENSE_RANK()OVER(ORDER BY date)) 
               FROM tb FOR XML PATH('')
           ), 1, 1, ''
       )
EXEC('
SELECT name,'+@s1+'
FROM (
SELECT rn=DENSE_RANK()OVER(ORDER BY date),name,aa
FROM [tb] t
) a
PIVOT(MAX(aa) FOR rn IN('+@s+'))b
')
/*
name                 Date1       Date2       Date3
-------------------- ----------- ----------- -----------
maxiaofei8           301         401         501
mazhen16810          302         402         502
mb586942520          303         403         503
meichsh11            304         404         504
mianyangdianxin      305         405         505(5 行受影响)*/

Answer 4

select name,max([date1])[date1],max([date2])[date2],max([date3])[date3] from 
(
select name,
(case when row_number()over(partition by name order by date)=1 then aa else '' end) [date1],
(case when row_number()over(partition by name order by date)=2 then aa else '' end) [date2],
(case when row_number()over(partition by name order by date)=3 then aa else '' end) [date3]
from tb) t
group by name/*
name                 date1 date2 date3 
-------------------- ----- ----- ----- 
maxiaofei8           301   401   501
mazhen16810          302   402   502
mb586942520          303   403   503
meichsh11            304   404   504
mianyangdianxin      305   405   505（所影响的行数为 5 行）
*/

Answer 5

select name,max([date1])[date1],max([date2])[date2],max([date3])[date3] from 
(
select name,
(case when row_number()over(partition by name order by date)=1 then aa else '' end) [date1],
(case when row_number()over(partition by name order by date)=2 then aa else '' end) [date2],
(case when row_number()over(partition by name order by date)=3 then aa else '' end) [date3]
from tb) t
group by name引用永生哥哥的。不过，连续三天具体没明白啥意思。

Answer 6

问题解决了,用pivot函数:
alter PROCEDURE pp

@beginDate datetime,
@endDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
        -- 查询7天数据
DECLARE 
@sql varchar(1000),
@day1 varchar(20),
@day2 varchar(20),
@day3 varchar(20),
@day4 varchar(20),
@day5 varchar(20),
@day6 varchar(20),
@day7 varchar(20)

set @day1 = CONVERT(varchar(10),@beginDate,120);
set @day2 = DATEADD("DAY", 1,@day1);
set @day3 = DATEADD("DAY", 1,@day2);
set @day4 = DATEADD("DAY", 1,@day3);
set @day5 = DATEADD("DAY", 1,@day4);
set @day6 = DATEADD("DAY", 1,@day5);
set @day7 = CONVERT(varchar(10),@endDate,120);



set @sql = 'select name,['
  +@day1+'] as day1,['
  +@day2+'] as day2,['
  +@day3+'] as day3,['
  +@day4+'] as day4,['
  +@day5+'] as day5,['
  +@day6+'] as day6,['
  +@day7+'] as day7 '+
  'from tb t '+
  ' pivot(sum(aa) for date in (['+
  +@day1+'],['
  +@day2+'],['
  +@day3+'],['
  +@day4+'],['
  +@day5+'],['
  +@day6+'],['
  +@day7+'])) as a'
  
exec(@sql);     
END
GO