根据某些条件交换行位置的SQL查询

Question

我正在获取SQL格式报告的输出，如下所述:-

create table ReportCustom (RoleID int, Supervisor varchar(250),UserECode int, UserName varchar(250),TimeSpent float,Activityname varchar(250))
go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0,'Process Training%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0.25,'L & D Training%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',3.12,'System Downtime%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',100,'Grand Total%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.22,'System Downtime%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.32,'Process Support%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.36,'Process Training%')
 Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',100,'Grand Total%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0,'L & D Training%') 
Go
insert into ReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0.1,'System Downtime%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',0.05,'Non Production%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',100,'Grand Total%')
 Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21697,'Maria Elizabeth David',5.48,'L & D Training%')
 Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',4.48,'Process Support%')
 Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',93.61,'Production%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',100,'Grand Total%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.34,'System Downtime%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.6,'Process Training%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',1.17,'L & D Training%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',2.46,'Non Production%')
 Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',95.76,'Production%') 
Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',100,'Grand Total%')
 Go
insert into ReportCustom values(1,'Subba Rayudu Yadavalli',21645,'Sujatha Basa',0.71,'System Downtime%') 
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',100,'Grand Total%') 
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',1.508125,'L & D Training%') 
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',100,'Grand Total%') 
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',0.512857142857143,'L & D Training%') 
Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',46.2442857142857,'Production%')
 Go
insert into ReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',6.8525,'System Downtime%') 
Go
insert into ReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',100,'Grand Total%') 
Go
insert into ReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',1.01049107142857,'L & D Training%')

但我需要OutPutReportCustomTable中提到的输出：

create table OutPutReportCustom (RoleID int, Supervisor varchar(250),UserECode int, UserName varchar(250),TimeSpent float,Activityname varchar(250))
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0,'Process Training%')
 Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',0.25,'L & D Training%') 
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',3.12,'System Downtime%')
 Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20201,'Uma Maheshwar Askula',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.22,'System Downtime%') 
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.32,'Process Support%')
 Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',0.36,'Process Training%')
 Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20207,'Sunil Kumar Damireddy',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0,'L & D Training%') 
Go
insert into OutPutReportCustom values(1,'Naveen Kumar Chitrapu',20394,'Satya Kumari Rajanala',0.1,'System Downtime%') 
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',0.512857142857143,'L & D Training%')
 Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',46.2442857142857,'Production%') 
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20231,'Naveen Kumar Chitrapu',6.8525,'System Downtime%')
 Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',0.05,'Non Production%')
 Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',80120,'Maria Elizabeth David',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21697,'Maria Elizabeth David',5.48,'L & D Training%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',4.48,'Process Support%')
 Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',93.61,'Production%')
 Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21307,'Asmitha Rani Arra',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.34,'System Downtime%')
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',0.6,'Process Training%')
 Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',1.17,'L & D Training%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21413,'Pankaj Kumar',2.46,'Non Production%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',95.76,'Production%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21510,'Saleem Mohammed',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(1,'Subba Rayudu Yadavalli',21645,'Sujatha Basa',0.71,'System Downtime%')
 Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',100,'Grand Total%') 
Go
insert into OutPutReportCustom values(2,'Bala Krishna Narayana Sadula',20050,'Subba Rayudu Yadavalli',1.508125,'L & D Training%') 
Go
insert into OutPutReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',100,'Grand Total%')
 Go
insert into OutPutReportCustom values(3,'Rajanikanth Neelakanta Koka',20015,'Bala Krishna Narayana Sadula',1.01049107142857,'L & D Training%')

去

ReportCustom表中的数据是我的输入，我希望数据的顺序为OutPutReportCustom table.In ReportCustom表您可以看到6个不同的UserECode (80120,21697,21307,21413,21510,21645) RoleID=1正在向'Subba Rayudu Yadavalli‘报告。’Subba Rayadu‘的详细信息也在同一个表中。现在我想要所有的6个用户的详细资料首先，然后'Subba Rayudu‘的详细资料，其用户代码是20050和roleid=2。然后，我们将采取其他用户的详细资料，之后，他们的主管详细资料，等等，直到我们完成所有的RoleID=1用户的详细资料和他们的直接主管的详细资料。因此OutPut将包含一系列以RoleID=2为前缀的RoleID=1。最后，如果存在，我们将以顺序方式显示剩余的RoleID，即RoleID=3或4或5。

Answer 1

我不确定我是否正确理解了你的问题。看起来你想要以一种层次化的方式排序。您不能在单个SQL查询中解决此问题。从主管开始(哪一个？)然后继续与其主管联系。

我想知道你是不是真的想要主管，然后是所有的工人。

您不能在单个SQL查询中解决这个问题，除非存在一个已知的最大层次深度。如果不是，则需要对层次结构中的每个级别(甚至每个分支)执行SQL查询。可能有一些SQL扩展可以处理层次结构，但我不知道它是如何工作的。

层次结构的3个级别(可能不是全部正确，但可能会对您有所帮助)：

Select worker.* 
FROM OutPutReportCustom worker
  left outer join OutPutReportCustom supervisor 
      on worker.Supervisor = supervisor.userName
  left outer join OutPutReportCustom supersupervisor 
      on supervisor.Supervisor = supersupervisor.userName
order by
  worker.UserName,
  supervisor.UserName,
  supersupervisor.UserName

顺便说一句:您可能应该考虑规范化您的数据。(特别是:使用外键而不是名称。)

编辑

请注意，除非您指定了order by子句，否则不能保证以任何特定顺序从SQL查询返回记录。您不能依赖于向表中插入数据的顺序。(即使在您的所有测试中，它们可能以相同的顺序返回，但这只在数据出现碎片之前有效，这是无法控制的)。

最常见的方法是在读取时对数据进行排序。如果数据如何排序的逻辑在于写入数据的部分，请考虑添加另一个“排序”列，该列保存一个数值。在查询数据时，您可以很容易地按此进行排序。

Answer 2

在查询中，将其指定为

Select * from (your table) order by SupervisorName;

但你不会得到想要的顺序(我的意思是你不会先得到Subba Rayudu，然后是Avinash，然后是Rajani，等等)。它将按正确的顺序排列。)但它会一起显示具有相同Supervisor名称的行。