挺难的SQL,紧急在线等,

zhangzhimin 2009-08-28 05:36:06
卡号 时间
80005 2009-2-21
80030 2009-2-21
80030 2009-4-19
80049 2009-2-21
80051 2009-2-14
80051 2009-3-21
80124 2009-5-16
80180 2009-4-11
80180 2009-4-18
80180 2009-4-25
转成
卡号 时间 卡号 时间 卡号 时间 卡号 时间
80341 2009-3-14 80341 2009-3-14 80341 2009-2-14 80341 2009-2-21
80349 2009-3-7
卡号不固定,
在线等,帮帮忙吧,给个思路也行。
...全文
210 30 打赏 收藏 转发到动态 举报
写回复
用AI写文章
30 条回复
切换为时间正序
请发表友善的回复…
发表回复
fanzhouqi 2009-08-29
  • 打赏
  • 举报
回复
if object_id('tempdb..#t') is not null
drop table #t

create table #t(ID int,time datetime ,money int)
insert into #t
select 1 ,'2009-08-01' ,1000
union all select 1 ,'2009-08-02' ,2000
union all select 2 ,'2009-08-15' ,5000
union all select 2 ,'2009-08-16' ,6000
union all select 2 ,'2009-08-17' ,7000


alter table #t add id1 int
go


declare @i int
declare @j int
set @i = 0
set @j = 0

update #t set
id1 = case
when id = @j then @i else 1
end
,@i= case
when id = @j then @i+1 else 1
end
,@j = id

select * from #t

declare @str varchar(8000)
set @str =''

select @str = @str +',max(case when id1 ='+ltrim(id1)+' then time else null end)
,sum(case when id1 ='+ltrim(id1)+' then money else null end)'
from #t
where id = (select top 1 id from #t order by id1 desc)

set @str = 'select id '+@str+' from #t group by id'

--print @str
exec(@str)

id
---- ------------------------ ----------- ------------------------ ----------- ------------------------ -----------
1 2009-08-01 00:00:00.000 1000 2009-08-02 00:00:00.000 2000 NULL NULL
2 2009-08-15 00:00:00.000 5000 2009-08-16 00:00:00.000 6000 2009-08-17 00:00:00.000 7000


这是以前类似的需求 给LZ参考
htl258_Tony 2009-08-29
  • 打赏
  • 举报
回复
--> 生成测试数据表:tb

If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb
--添加一列
alter table tb add id int identity
go
--
declare @s varchar(8000)
select @s=isnull(@s+',','select ')+'
卡号=max(case px when '+ltrim(px)+' then ltrim(卡号) else '''' end),
时间=max(case px when '+ltrim(px)+' then convert(varchar(10),时间,23) else '''' end)'
from (select distinct px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id) from tb t) t
set @s=@s+' from (select px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id),* from tb t) t group by 卡号'
exec(@s)
/*
卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21
80030 2009-02-21 80030 2009-04-19
80049 2009-02-21
80051 2009-02-14 80051 2009-03-21
80124 2009-05-16
80180 2009-04-11 80180 2009-04-18 80180 2009-04-25

(6 行受影响)
*/
liffe 2009-08-28
  • 打赏
  • 举报
回复
MARK
百年树人 2009-08-28
  • 打赏
  • 举报
回复
楼主没试试22楼的?
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
添加一列
alter table t1 add id int identity
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
select t1.cord,ntime,address,ls,[className], [days], [order] into tb1 from t1,(
select cord,min(id) id into tt from t1 group by cord ) as tt
where t1.id=tt.id
select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--2
select t1.cord,ntime,address,ls,[className], [days], [order] into tb2 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--3
select t1.cord,ntime,address,ls,[className], [days], [order] into tb3 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--4
select t1.cord,ntime,address,ls,[className], [days], [order] into tb4 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--5

select t1.cord,ntime,address,ls,[className], [days], [order] into tb5 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt

--6

select t1.cord,ntime,address,ls,[className], [days], [order] into tb6 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt

--7

select t1.cord,ntime,address,ls,[className], [days], [order] into tb7 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt


--8

select t1.cord,ntime,address,ls,[className], [days], [order] into tb8 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt


--9

select t1.cord,ntime,address,ls,[className], [days], [order] into tb9 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt

--10

select t1.cord,ntime,address,ls,[className], [days], [order] into tb10 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt


--11

select t1.cord,ntime,address,ls,[className], [days], [order] into tb11 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.id

select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt

select * from tb1
left join tb2 on tb1.cord=tb2.cord
left join tb3 on tb1.cord=tb3.cord
left join tb4 on tb1.cord=tb4.cord
left join tb5 on tb1.cord=tb5.cord
left join tb6 on tb1.cord=tb6.cord
left join tb7 on tb1.cord=tb7.cord
left join tb8 on tb1.cord=tb8.cord
left join tb9 on tb1.cord=tb9.cord
left join tb10 on tb1.cord=tb10.cord
left join tb11 on tb1.cord=tb11.cord
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
感谢所有支持的兄弟们,我已经解决了,
现贴出来给大家参考一下,虽然是因定形式的,但还是可以转换成动态的
feixianxxx 2009-08-28
  • 打赏
  • 举报
回复
[Quote=引用 22 楼 josy 的回复:]
SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([卡号]int,[时间]datetime)insert[tb]select80005,'2009-2-21'unionallselect80030,'2009-2-21'unionallselect80030,'2009-4-19'unional¡­
[/Quote]
树哥上午写过类似的、。。
百年树人 2009-08-28
  • 打赏
  • 举报
回复
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([卡号] int,[时间] datetime)
insert [tb]
select 80005,'2009-2-21' union all
select 80030,'2009-2-21' union all
select 80030,'2009-4-19' union all
select 80049,'2009-2-21' union all
select 80051,'2009-2-14' union all
select 80051,'2009-3-21' union all
select 80124,'2009-5-16' union all
select 80180,'2009-4-11' union all
select 80180,'2009-4-18' union all
select 80180,'2009-4-25'

---查询---
declare @sql varchar(8000),@i int,@maxcnt int
select @i=1,@maxcnt=max(cnt) from (select 卡号,count(1) as cnt from tb group by 卡号) t
while @i<=@maxcnt
begin
select @sql=isnull(@sql+',','')+'卡号,时间=max(case px when '+rtrim(@i)+' then convert(varchar(10),时间,120) else '''' end)',@i=@i+1
end
set @sql= 'select '
+@sql
+' from (select *,px=(select count(1)+1 from tb where 卡号=t.卡号 and 时间>t.时间) from tb t) tt group by 卡号'

exec(@sql)



---结果---
卡号 时间 卡号 时间 卡号 时间
----------- ---------- ----------- ---------- ----------- ----------
80005 2009-02-21 80005 80005
80030 2009-04-19 80030 2009-02-21 80030
80049 2009-02-21 80049 80049
80051 2009-03-21 80051 2009-02-14 80051
80124 2009-05-16 80124 80124
80180 2009-04-25 80180 2009-04-18 80180 2009-04-11
feixianxxx 2009-08-28
  • 打赏
  • 举报
回复
[Quote=引用 20 楼 htl258 的回复:]
SQL code--> 生成测试数据表:tbIfnotobject_id('[tb]')isnullDroptable[tb]GoCreatetable[tb]([卡号]int,[时间]Datetime)Insert tbSelect80005,'2009-2-21'unionallSelect80030,'2009-2-21'unionallSelect80030,'2009-4-19'unio¡­
[/Quote]

TONY哥。。
楼主的数据是错误的。。。
他的需求是要
凡是相同的卡号,所有数据都放在一行。、
htl258_Tony 2009-08-28
  • 打赏
  • 举报
回复
--> 生成测试数据表:tb

If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb

-->SQL查询如下:
select id=identity(int),* into # from tb t where not exists(select 1 from tb where 卡号=t.卡号 and 时间>t.时间)

select
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from #
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-04-19 80049 2009-02-21 80051 2009-03-21
80124 2009-05-16 80180 2009-04-25

(2 行受影响)
*/
drop table #

feixianxxx 2009-08-28
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 zhangzhimin 的回复:]
feixianxxx 兄的想法是没错的,
但是数据量很多,卡号可能重复十几个,有没有动态的解决方法
[/Quote]
你贴点数据 还有对应的结果上去 这样人家好测试
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
htl258兄,不同的卡号,不能在同一行呢
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
ROW_NUMBER 是什么函数,我用的是SQL2000
htl258_Tony 2009-08-28
  • 打赏
  • 举报
回复
--> 生成测试数据表:tb

If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb

-->SQL查询如下:
alter table tb add id int identity
go

select
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from tb
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-02-21 80030 2009-04-19 80049 2009-02-21
80051 2009-02-14 80051 2009-03-21 80124 2009-05-16 80180 2009-04-11
80180 2009-04-18 80180 2009-04-25

(3 行受影响)
*/

zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
凡是相同的卡号,所有数据都放在一行。
zhangzhimin 2009-08-28
  • 打赏
  • 举报
回复
feixianxxx 兄的想法是没错的,
但是数据量很多,卡号可能重复十几个,有没有动态的解决方法
zc_0101 2009-08-28
  • 打赏
  • 举报
回复
下班,回家
zc_0101 2009-08-28
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 zc_0101 的回复:]
麦蒂,你理解错了。。。他要相同卡号的放在一行
[/Quote]
也不见得,原来楼主给的结果就有毛病。。。
建议楼主贴清楚
feixianxxx 2009-08-28
  • 打赏
  • 举报
回复

....那还给这些数据
加载更多回复(10)

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧