RECCODE是固定5列么?
你用的是2000还是2005?
你用的是2000还是2005?
解决方案 »
- mssql是怎么走索引的
- 数据库查询问题.
- 数据中比平常数据多了个''号
- .
- 如何锁定一个数据库,因为我要修改它的名字
- 急帮一个简单的问题,如何让表中非主键字段内容不重复,在Access里设置索引-不重复就可以,但在SQL Server里怎么设置呢?
- 急,sqlserver安装
- 请教报表查询语句
- 极度困惑!请一定要帮忙!多谢!在线等待中!
- 如何实现有条件的sql多表连接查询
- Msg 8621: The query processor ran out of stack space during query optimization. Please simplify the query.
- 关于execl操作的小问题
表二的RECCODE的每条记录在表一对应的有5条记录我要查询出来 分 6列显示是2000还是2005?
嗯,其实我这是一个mdb文件非常感谢朋友
max(case n.px when 1 then n.title else '' end) title1,
max(case n.px when 2 then n.title else '' end) title2,
max(case n.px when 3 then n.title else '' end) title3,
max(case n.px when 4 then n.title else '' end) title4,
max(case n.px when 5 then n.title else '' end) title5
from tb2,
(select * , px = (select count(1) from tb1 where RECCODE = t.RECCODE and RECNO < t.RECNO) + 1 from tb1 t) n
where tb2.reccode = n.reccode
group by tb2.name
insert into tb1 select '001','ABCD',1
insert into tb1 select '002','DFEF',1
insert into tb1 select '003','DERF',1
insert into tb1 select '004','34DF',1
insert into tb1 select '005','D34F',1
insert into tb1 select '006','DERF',2
insert into tb1 select '007','3DRT',2
insert into tb1 select '008','76RY',2
insert into tb1 select '009','235F',2
insert into tb1 select '010','34HY',2
insert into tb1 select '011','7DFE',3
insert into tb1 select '012','4544',3
insert into tb1 select '013','76fg',3
insert into tb1 select '014','678j',3
insert into tb1 select '015','4534',3
create table tb2(RECCODE int,NAME nvarchar(10))
insert into tb2 select 1,'李强'
insert into tb2 select 2,'王强'
insert into tb2 select 3,'张三'
insert into tb2 select 4,'王五'
go
select a.name,
(select title from tb1 where recno=b.recno),
(select title from tb1 where cast(recno as int)-1=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-1=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-1=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-1=cast(b.recno as int))
from tb2 a inner join (
select reccode,min(recno) as recno from tb1 group by reccode
) b on a.reccode=b.reccode
go
drop table tb1,tb2
/*
name
---------- ---------- ---------- ---------- ---------- ----------
李强 ABCD DFEF DFEF DFEF DFEF
王强 DERF 3DRT 3DRT 3DRT 3DRT
张三 7DFE 4544 4544 4544 4544
*/
insert into tb1 select '001','ABCD',1
insert into tb1 select '002','DFEF',1
insert into tb1 select '003','DERF',1
insert into tb1 select '004','34DF',1
insert into tb1 select '005','D34F',1
insert into tb1 select '006','DERF',2
insert into tb1 select '007','3DRT',2
insert into tb1 select '008','76RY',2
insert into tb1 select '009','235F',2
insert into tb1 select '010','34HY',2
insert into tb1 select '011','7DFE',3
insert into tb1 select '012','4544',3
insert into tb1 select '013','76fg',3
insert into tb1 select '014','678j',3
insert into tb1 select '015','4534',3
create table tb2(RECCODE int,NAME nvarchar(10))
insert into tb2 select 1,'李强'
insert into tb2 select 2,'王强'
insert into tb2 select 3,'张三'
insert into tb2 select 4,'王五'
go
select a.name,
(select title from tb1 where recno=b.recno),
(select title from tb1 where cast(recno as int)-1=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-2=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-3=cast(b.recno as int)),
(select title from tb1 where cast(recno as int)-4=cast(b.recno as int))
from tb2 a inner join (
select reccode,min(recno) as recno from tb1 group by reccode
) b on a.reccode=b.reccode
go
drop table tb1,tb2
/*
---------- ---------- ---------- ---------- ---------- ----------
李强 ABCD DFEF DERF 34DF D34F
王强 DERF 3DRT 76RY 235F 34HY
张三 7DFE 4544 76fg 678j 4534
*/
if not object_id('Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([RECNO] nvarchar(3),[TITLE] nvarchar(4),[RECCODE] int)
Insert #T1
select N'001',N'ABCD',1 union all
select N'002',N'DFEF',1 union all
select N'003',N'DERF',1 union all
select N'004',N'34DF',1 union all
select N'005',N'D34F',1 union all
select N'006',N'DERF',2 union all
select N'007',N'3DRT',2 union all
select N'008',N'76RY',2 union all
select N'009',N'235F',2 union all
select N'010',N'34HY',2 union all
select N'011',N'7DFE',3 union all
select N'012',N'4544',3 union all
select N'013',N'76fg',3 union all
select N'014',N'678j',3 union all
select N'015',N'4534',3
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([RECCODE] int,[NAME] nvarchar(2))
Insert #T2
select 1,N'李强' union all
select 2,N'王强' union all
select 3,N'张三' union all
select 4,N'王五'
Go;with c
as
(
select
*
from
(select row=row_number()over(partition by [RECCODE] order by [RECNO]),[TITLE],[RECCODE] from #T1)t1
pivot
(max([TITLE]) for row in([1],[2],[3],[4],[5]))t3)
select
t2.*,[1],[2],[3],[4],[5]
from
c
join
#T2 t2 on t2.[RECCODE]=c.[RECCODE]
(15 行受影响)(4 行受影响)
RECCODE NAME 1 2 3 4 5
----------- ---- ---- ---- ---- ---- ----
1 李强 ABCD DFEF DERF 34DF D34F
2 王强 DERF 3DRT 76RY 235F 34HY
3 张三 7DFE 4544 76fg 678j 4534(3 行受影响)
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
表一的recno是wchar类型
insert into @tb1 select '001','ABCD',1
union all select '002','DFEF',1
union all select '003','DERF',1
union all select '004','34DF',1
union all select '005','D34F',1
union all select '006','DERF',2
union all select '007','3DRT',2
union all select '008','76RY',2
union all select '009','235F',2
union all select '010','34HY',2
union all select '011','7DFE',3
union all select '012','4544',3
union all select '013','76fg',3
union all select '014','678j',3
union all select '015','4534',3
declare @tb2 table(RECCODE int,NAME nvarchar(10))
insert into @tb2 select 1,N'李强'
union all select 2,N'王强'
union all select 3,N'张三'
union all select 4,N'王五' select
B.[name]
,max(case when cast(A.recno as int) % 5 =1 then title else null end) as TITLE1
,max(case when cast(A.recno as int) % 5 =2 then title else null end) as TITLE2
,max(case when cast(A.recno as int) % 5 =3 then title else null end) as TITLE3
,max(case when cast(A.recno as int) % 5 =4 then title else null end) as TITLE4
,max(case when cast(A.recno as int) % 5 =0 then title else null end) as TITLE5
from
@tb2 B
inner join
@tb1 A
on
B.reccode = A.reccode
group by
B.[name]
order by
B.reccode表1中recno的类型并不重要,适当的调整一下就好了.