62,050
社区成员
发帖
与我相关
我的任务
分享sql 语句怎么写！！！！
有张表
tab1
id time userId
1 2010-9-28 14:12:04 001
2 2010-9-28 14:12:22 002
3 2010-9-28 14:12:23 001
4 2010-9-28 16:16:06 001
5 2010-9-28 21:12:04 002
6 2010-9-28 13:12:04 001
我想得到下面这种情况,得到表中有的日期 每天前四次的时间 ，不够的空着
人员 日期 一次时间 二次时间 三次时间 四次时间
001 2010-9-28 13:12:04 14:12:04 14:12:23 21:12:04
002 2010-9-28 14:12:22 21:12:04
tab1
id time userId
1 2010-9-28 14:12:04 001
2 2010-9-28 14:12:22 002
3 2010-9-28 14:12:23 001
4 2010-9-28 16:16:06 001
5 2010-9-28 21:12:04 002
6 2010-9-28 13:12:04 001
我想得到下面这种情况,得到表中有的日期 每天前四次的时间 ，不够的空着
人员 日期 一次时间 二次时间 三次时间 四次时间
001 2010-9-28 13:12:04 14:12:04 14:12:23 21:12:04
002 2010-9-28 14:12:22 21:12:04
...全文
请发表友善的回复…
发表回复
SQLCenter 2010-10-08
- 打赏
- 举报
[Quote=引用 16 楼 mmm306306 的回复:]
利用该帖我问一下高手
CREATE TABLE #tb
(
id INT ,
TIME DATETIME ,
userid VARCHAR(5)
)
GO
INSERT INTO #tb
VALUES ( 1, '2010-10-06 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 2, '2010-10-07 00:00:00', '002' )
INSERT INTO #tb
VALUES ( 3, '2010-10-08 00:00:00', '003' )
INSERT INTO #tb
VALUES ( 4, '2010-10-09 00:00:00', '004' )
INSERT INTO #tb
VALUES ( 5, '2010-10-10 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 6, '2010-10-11 00:00:00', '001' )
GO
DECLARE @sql VARCHAR(8000)
--高手能否用下面的方法写下。。谢谢 ！！@@
SELECT @sql = ISNULL(@sql + ',', '') + 'MAX(case when userid=''' + userid
+ ''' then TIME else '''' end) as [CISHU]'
FROM ( SELECT DISTINCT
userid
FROM #tb
) t
PRINT @sql
SET @sql = 'select ' + @sql + ' from #tb group by userid,time'
EXEC (@sql)
[/Quote]
利用该帖我问一下高手
CREATE TABLE #tb
(
id INT ,
TIME DATETIME ,
userid VARCHAR(5)
)
GO
INSERT INTO #tb
VALUES ( 1, '2010-10-06 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 2, '2010-10-07 00:00:00', '002' )
INSERT INTO #tb
VALUES ( 3, '2010-10-08 00:00:00', '003' )
INSERT INTO #tb
VALUES ( 4, '2010-10-09 00:00:00', '004' )
INSERT INTO #tb
VALUES ( 5, '2010-10-10 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 6, '2010-10-11 00:00:00', '001' )
GO
DECLARE @sql VARCHAR(8000)
--高手能否用下面的方法写下。。谢谢 ！！@@
SELECT @sql = ISNULL(@sql + ',', '') + 'MAX(case when userid=''' + userid
+ ''' then TIME else '''' end) as [CISHU]'
FROM ( SELECT DISTINCT
userid
FROM #tb
) t
PRINT @sql
SET @sql = 'select ' + @sql + ' from #tb group by userid,time'
EXEC (@sql)
[/Quote]
DECLARE @sql VARCHAR(8000)
SELECT @sql = ISNULL(@sql + ',', '') + 'sum(case when userid=''' + userid
+ ''' then 1 else 0 end) as [CISHU'+userid+']'
FROM ( SELECT DISTINCT
userid
FROM #tb
) t
SET @sql = 'select ' + @sql + ' from #tb'
exec (@sql)
leiziaitudou 2010-10-08
- 打赏
- 举报
试一试这
select top 4 * from tab1 where id in (select top 4 id from tab1 where left(time,10) = left(getdate(),10) order by time)
select top 4 * from tab1 where id in (select top 4 id from tab1 where left(time,10) = left(getdate(),10) order by time)
打一壶酱油 2010-10-08
- 打赏
- 举报
group by userid + 行转列 到 sql 版会有完满答案
hizhl168 2010-10-08
- 打赏
- 举报
学习学习，我本来以为我懂。现在觉得我不懂了
paul_C 2010-10-08
- 打赏
- 举报
强人！
paul_C 2010-10-08
- 打赏
- 举报
强人！！
paul_C 2010-10-08
- 打赏
- 举报
强人！
tjc_333 2010-10-08
- 打赏
- 举报
select a.QuestionID Question,
a.[Content] A,b.[Content] B,
c.[Content] C,d.[Content] D
from dbo.Answer a,dbo.Answer b,dbo.Answer c,dbo.Answer d where
a.QuestionID=b.QuestionID and a.QuestionID=c.QuestionID and a.QuestionID=d.QuestionID and a.AnswerID>b.AnswerID and b.AnswerID>c.AnswerID and c.AnswerID>d.AnswerID
可以参考 参考哈。。。。
a.[Content] A,b.[Content] B,
c.[Content] C,d.[Content] D
from dbo.Answer a,dbo.Answer b,dbo.Answer c,dbo.Answer d where
a.QuestionID=b.QuestionID and a.QuestionID=c.QuestionID and a.QuestionID=d.QuestionID and a.AnswerID>b.AnswerID and b.AnswerID>c.AnswerID and c.AnswerID>d.AnswerID
可以参考 参考哈。。。。
神码浮云 2010-10-07
- 打赏
- 举报
cooooooooool
cheniwantyou 2010-10-07
- 打赏
- 举报
就是交叉表嘛
daifangci 2010-10-07
- 打赏
- 举报
哎……不懂，看了老半天，我也想知道答案
wzy528 2010-10-06
- 打赏
- 举报
什么意思，分下组不行吗，用group by
「已注销」 2010-10-06
- 打赏
- 举报
declare @sql nvarchar(max)
set @sql=N''
set @sql= @sql + N'select userid 人员 ,convert(varchar(10),time,120) 日期,'+char(10)
set @sql= @sql + N'max(case when row=1 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS 一次时间,'+char(10)
set @sql= @sql + N'max(case when row=2 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS 二次时间,'+char(10)
set @sql= @sql + N'max(case when row=3 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS 三次时间,'+char(10)
set @sql= @sql + N'max(case when row=4 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS 四次时间'+char(10)
set @sql= @sql + N'from('+char(10)
set @sql= @sql + N' select userid,time ,row_number()over(partition by userid order by getdate()) row'+char(10)
set @sql= @sql + N' from #tab )M '+char(10)
set @sql= @sql + N'GROUP BY userid,convert(varchar(10),time,120)'
----print @sql
exec(@sql)
马老虎 2010-10-06
- 打赏
- 举报
[Quote=引用 14 楼 hao1hao2hao3 的回复:]
不好意思，好像没有考虑顺序。现修改如下：
SQL code
;with cte as
(
select row_number() over(partition by userid order by [time]) id,[time],userid from tb
)
--select * from cte
select distinct cte.userid 人员,c……
[/Quote]
利用该帖我问一下高手
不好意思，好像没有考虑顺序。现修改如下：
SQL code
;with cte as
(
select row_number() over(partition by userid order by [time]) id,[time],userid from tb
)
--select * from cte
select distinct cte.userid 人员,c……
[/Quote]
利用该帖我问一下高手
CREATE TABLE #tb
(
id INT ,
TIME DATETIME ,
userid VARCHAR(5)
)
GO
INSERT INTO #tb
VALUES ( 1, '2010-10-06 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 2, '2010-10-07 00:00:00', '002' )
INSERT INTO #tb
VALUES ( 3, '2010-10-08 00:00:00', '003' )
INSERT INTO #tb
VALUES ( 4, '2010-10-09 00:00:00', '004' )
INSERT INTO #tb
VALUES ( 5, '2010-10-10 00:00:00', '001' )
INSERT INTO #tb
VALUES ( 6, '2010-10-11 00:00:00', '001' )
GO
DECLARE @sql VARCHAR(8000)
--高手能否用下面的方法写下。。谢谢 ！！@@
SELECT @sql = ISNULL(@sql + ',', '') + 'MAX(case when userid=''' + userid
+ ''' then TIME else '''' end) as [CISHU]'
FROM ( SELECT DISTINCT
userid
FROM #tb
) t
PRINT @sql
SET @sql = 'select ' + @sql + ' from #tb group by userid,time'
EXEC (@sql)
「已注销」 2010-10-06
- 打赏
- 举报
drop table #tab
create table #tab
(
id int identity,
time datetime,
userid varchar(10)
)
insert into #tab select '2010-9-28 14:12:04', '001'
insert into #tab select '2010-9-28 14:12:22', '002'
insert into #tab select '2010-9-28 14:12:23', '001'
insert into #tab select '2010-9-28 16:16:06', '001'
insert into #tab select '2010-9-28 21:12:04', '002'
insert into #tab select '2010-9-28 13:12:04', '001'
select userid 人员 ,convert(varchar(10),time,120) 日期,
max(case when row=1 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS 一次时间,
max(case when row=2 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS 二次时间,
max(case when row=3 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS 三次时间,
max(case when row=4 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS 四次时间
from(
select userid,time ,row_number()over(partition by userid order by getdate()) row
from #tab)M
GROUP BY userid,convert(varchar(10),time,120)
人员 日期 一次时间 二次时间 三次时间 四次时间
---------- ---------- ---------------- ---------------- ---------------- ----------------
001 2010-09-28 14:12:04 14:12:23 16:16:06 13:12:04
002 2010-09-28 21:12:04 14:12:22
(2 row(s) affected)
hao1hao2hao3 2010-10-06
- 打赏
- 举报
不好意思，好像没有考虑顺序。现修改如下：
;with cte as
(
select row_number() over(partition by userid order by [time]) id,[time],userid from tb
)
--select * from cte
select distinct cte.userid 人员,convert(varchar(10),b.[time],120) 日期,
max(case when cte.id=1 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第一,
max(case when cte.id=2 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第二,
max(case when cte.id=3 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第三,
max(case when cte.id=4 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第四
from cte join cte as b
on cte.id = b.id
group by cte.userid ,convert(varchar(10),b.[time],120)
--结果
001 2010-09-28 13:12:04 14:12:04 14:12:23 16:16:06
002 2010-09-28 14:12:22 21:12:04
--顺便说一下，你给出的结果有问题。
whb147 2010-10-06
- 打赏
- 举报
行列传换
hao1hao2hao3 2010-10-06
- 打赏
- 举报
;with cte as
(
select row_number() over(partition by userid order by getdate()) id,[time],userid from tb
)
--select * from cte
select distinct cte.userid 人员,convert(varchar(10),b.[time],120) 日期,
max(case when cte.id=1 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第一,
max(case when cte.id=2 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第二,
max(case when cte.id=3 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第三,
max(case when cte.id=4 then substring(convert(varchar(30),cte.[time],121),12,8) else '' end) 第四
from cte join cte as b
on cte.id = b.id
group by cte.userid ,convert(varchar(10),b.[time],120)
--结果
001 2010-09-28 14:12:04 14:12:23 16:16:06 13:12:04
002 2010-09-28 21:12:04 14:12:22
hao1hao2hao3 2010-10-06
- 打赏
- 举报
if object_id('tb')>0
drop table tb
create table tb
(
id int,
[time] datetime,
userid varchar(3)
)
insert into tb
select 1, '2010-9-28 14:12:04', '001'
union all
select 2, '2010-9-28 14:12:22', '002'
union all
select 3, '2010-9-28 14:12:23', '001'
union all
select 4, '2010-9-28 16:16:06', '001'
union all
select 5, '2010-9-28 21:12:04', '002'
union all
select 6, '2010-9-28 13:12:04', '001'
select * from tb
;with cte as
(
select row_number() over(partition by userid order by getdate()) id,[time],userid from tb
)
--select * from cte
select distinct cte.userid 人员,convert(varchar(10),b.[time],120) 日期,
max(case when cte.id=1 then substring(cast(cte.[time] as varchar(30)),12,19) else '' end) 第一,
max(case when cte.id=2 then substring(cast(cte.[time] as varchar(30)),12,19) else '' end) 第二,
max(case when cte.id=3 then substring(cast(cte.[time] as varchar(30)),12,19) else '' end) 第三,
max(case when cte.id=4 then substring(cast(cte.[time] as varchar(30)),12,19) else '' end) 第四
from cte join cte as b
on cte.id = b.id
group by cte.userid ,convert(varchar(10),b.[time],120)
结果
001 2010-09-28 2:12PM 2:12PM 4:16PM 1:12PM
002 2010-09-28 9:12PM 2:12PM
马老虎 2010-10-06
- 打赏
- 举报
Sql版块的高手多。
建议去那问问吧
建议去那问问吧
加载更多回复（9）
