使用rowno查询无法正常工作 [英] Using rowno query is not working correctly
本文介绍了使用rowno查询无法正常工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Rankwisecourselist](@Rank varchar(50),@FromDate datetime,@ToDate datetime)
as
declare @SNo int,
@Course varchar(100),
@Code varchar(50),
@Descr varchar(20),
@Eligbility varchar(20),
@Days varchar(20),
@Startdt datetime,
@Enddt datetime
create table #TempTable(SNo int, Course varchar(10),Code varchar(100),
Descr varchar(20),Eligbility varchar(20),Days varchar(20),Startdt datetime,Enddt datetime)
begin tran
declare batchwise cursor FOR
select b.cmn_minor_code as Course,f.cmj_major_desc as Code,c.cmn_minor_desc as Description,e.eligibility as Eligbility,c.cmn_minor_day as Days,convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b,CO_MINOR_MASTER as c, Eligibility e,CO_MAJOR_MASTER as f
where a.course = b.cmn_minor_code and a.Rank =@rank and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code and e.Minorcode = b.cmn_minor_code and f.cmj_major_code = b.cmj_major_code
and a.Active <> 'd' and b.cbm_batch_start_dt between @FromDate and @ToDate
order by b.cmn_minor_code asc
SET @SNo = 0
open Batchwise
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
While @@Fetch_status = 0
begin
SET @SNo = @SNo + 1
insert into #TempTable values(@SNo, @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt) --added
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
END
commit tran
close Batchwise
deallocate Batchwise
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN
ELSE '' END AS
,
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
当我按如下方式执行存储过程输出时
When i execute the stored procedure output as follows
exec [Rankwisecourselist] '2013-08-01 00:00:00.000','2014-12-30 00:00:00.000'
Course Eligbility Startdt Enddt
REO CHIEF ENGINEER 10 Aug 2015 21 Aug 2015
REO CHIEF ENGINEER 24 Aug 2015 04 Sep 2015
REO CHIEF ENGINEER 14 Dec 2015 25 Dec 2015
Course Eligbility Startdt Enddt
REO CHIE ENGINEER 10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
获得以上输出我尝试了以下行没有概念
for getting a above output i tried the row no concept as follows
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN
ELSE '' END AS
,
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
但是输出不正确
我尝试过：
but output is not coming correctly
What I have tried:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Rankwisecourselist](@Rank varchar(50),@FromDate datetime,@ToDate datetime)
as
declare @SNo int,
@Course varchar(100),
@Code varchar(50),
@Descr varchar(20),
@Eligbility varchar(20),
@Days varchar(20),
@Startdt datetime,
@Enddt datetime
create table #TempTable(SNo int, Course varchar(10),Code varchar(100),
Descr varchar(20),Eligbility varchar(20),Days varchar(20),Startdt datetime,Enddt datetime)
begin tran
declare batchwise cursor FOR
select b.cmn_minor_code as Course,f.cmj_major_desc as Code,c.cmn_minor_desc as Description,e.eligibility as Eligbility,c.cmn_minor_day as Days,convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b,CO_MINOR_MASTER as c, Eligibility e,CO_MAJOR_MASTER as f
where a.course = b.cmn_minor_code and a.Rank =@rank and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code and e.Minorcode = b.cmn_minor_code and f.cmj_major_code = b.cmj_major_code
and a.Active <> 'd' and b.cbm_batch_start_dt between @FromDate and @ToDate
order by b.cmn_minor_code asc
SET @SNo = 0
open Batchwise
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
While @@Fetch_status = 0
begin
SET @SNo = @SNo + 1
insert into #TempTable values(@SNo, @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt) --added
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
END
commit tran
close Batchwise
deallocate Batchwise
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN
ELSE '' END AS
,
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
When i execute the stored procedure output as follows
exec [Rankwisecourselist] '2013-08-01 00:00:00.000','2014-12-30 00:00:00.000'
Course Eligbility Startdt Enddt
REO CHIEF ENGINEER 10 Aug 2015 21 Aug 2015
REO CHIEF ENGINEER 24 Aug 2015 04 Sep 2015
REO CHIEF ENGINEER 14 Dec 2015 25 Dec 2015
Course Eligbility Startdt Enddt
REO CHIE ENGINEER 10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
for getting a above output i tried the row no concept as follows
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN
ELSE '' END AS
,
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
但输出是不正确
but output is not coming correctly
推荐答案
尝试将RowNo查询更改为以下内容
Try changing your RowNo query to something like this below
ROW_NUMBER() OVER(PARTITION BY Course, Eligibility ORDER BY sno) AS RowNo
以上带下划线的应该是您想要对数据进行分组的列名，因此如果您认为上述两个是不够的，那么添加其他列。
The above underlined should be the column names on the basis of which you want to group your data, so in case you consider the above two are not enough then add other columns.
这篇关于使用rowno查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
