34,571
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[LID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
select no=(select COUNT(1) from Table_2 where PName=a.PName and EName=a.EName and LID<=a.LID),
* from Table_2 a
select row_number()over(order by getdate()) as no,LID1=max( case when no%6=1 then lid end),
LID2=max( case when no%6=2 then lid end),
LID3=max( case when no%6=3 then lid end),
LID4=max( case when no%6=4 then lid end),
LID5=max( case when no%6=5 then lid end),Pname,ename
from
(select no=(select COUNT(1) from Table_2 where PName=a.PName and EName=a.EName and LID<=a.LID),
* from Table_2 a) a
group by Pname,ename,no/6 order by EName,PName
/*
no LID1 LID2 LID3 LID4 LID5 Pname ename
-------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 A0002 NULL NULL NULL NULL 钢 北京
2 A0001 A0003 A0004 A0005 A0006 铁 北京
3 A0008 A0009 NULL NULL NULL 铁 北京
4 A0021 A0022 NULL NULL NULL 铜 北京
5 A0010 A0011 A0012 A0013 NULL 铁 上海
6 A0014 A0024 NULL NULL NULL 铜 西安
CREATE TABLE [Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
)
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0001','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0002','钢','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0003','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0004','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0005','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0006','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0007','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0008','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0009','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0010','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0011','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0012','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0013','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0014','铜','西安')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0021','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0022','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0024','铜','西安')
select PID = right('00' + ltrim((select count(1) from
(
select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
Pname ,
ename
from
(
select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5
) p
where EName < q.EName or (EName = q.EName and PName < q.PName) or (EName = q.EName and PName = q.PName and lid1 < q.lid1)) + 1),3),q.* from
(
select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
Pname ,
ename
from
(
select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5
) q
drop table [Table_2]
/*
PID lid1 lid2 lid3 lid4 lid5 Pname ename
------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
001 A0002 钢 北京
002 A0001 A0003 A0004 A0005 A0006 铁 北京
003 A0007 A0008 A0009 铁 北京
005 A0010 A0011 A0012 A0013 铁 上海
004 A0021 A0022 铜 北京
006 A0014 A0024 铜 西安
(所影响的行数为 6 行)
*/
CREATE TABLE [Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
)
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0001','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0002','钢','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0003','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0004','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0005','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0006','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0007','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0008','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0009','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0010','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0011','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0012','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0013','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0014','铜','西安')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0021','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0022','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0024','铜','西安')
select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
Pname ,
ename
from
(
select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5
drop table [Table_2]
/*
lid1 lid2 lid3 lid4 lid5 Pname ename
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
A0002 钢 北京
A0001 A0003 A0004 A0005 A0006 铁 北京
A0007 A0008 A0009 铁 北京
A0010 A0011 A0012 A0013 铁 上海
A0021 A0022 铜 北京
A0014 A0024 铜 西安
(所影响的行数为 6 行)
*/