SQL Server查询问题 [英] Sql server Query Issue

问题描述

亲爱的所有人
早上好，


我在sql数据处理方面有问题..我的表数据如下所示

路线天数

Dear all
Good morning ,


i have issue with sql data processing ....my table data is given below

Route Days

113007  1
113007  1
113007  1
113007  1
113007  1
113007  1
113007  2
113007  2
113007  2
113007  2
113007  2
113007  2
113008  1
113008  1
113008  1
113008  1
113008  1
113008  1
113008  2
113008  2
113008  2
113008  2
113008  2
113008  2

但是我需要使用带有列记录的alter table，之后我的表必须看起来像...

路线天数记录

but i need alter table with column record,after that my table must be look like...

Route Days record

113007  1   1
113007  1   2
113007  1   3
113007  1   4
113007  1   5
113007  1   6
113007  2   1
113007  2   2
113007  2   3
113007  2   4
113007  2   5
113007  2   6
113008  1   1
113008  1   2
113008  1   3
113008  1   4
113008  1   5
113008  1   6
113008  2   1
113008  2   2
113008  2   3
113008  2   4
113008  2   5
113008  2   6

所以请帮我如何归档以上结果，路由数据通过参数(多个值)来提供.

请帮助我

so please help me how can i archive the above result ,Route Data come through parameter (multiple value).

please help me

推荐答案

我认为您的表数据是按照上面列出的 Route，Days 标准排序的，因此我将其命名为route

所以这里是:

I supposed that your table data is sorted by Route , Days criteria as you listed above, and I named it route

So here it is :

with a(r, d, cnt) 
as
(
select route, days , COUNT(*) from route
group by route, Days
union all
select r , d , cnt-1 from a where cnt >1 
)
select * from a
order by r,d, cnt

感谢CTE的:)


a =我们CTE的输出表名称，其中包含3个字段r，d，cnt
r =是您的路线表中的路线ID
d =是您的路线表中的天数字段
cnt =递减计数器

递归CTE中的锚定语句:

Thanks to CTE''s :)

EDIT :
a = output table name of our CTE which have 3 fields r,d,cnt
r = is route id from your route table
d = is days field from your route table
cnt = a decreasing counter

our anchor statement in a recursive CTE :

select route, days , COUNT(*) from route
group by route, Days

我们CTE的递归部分:
从

our recursive part of our CTE :

select r , d , cnt-1 from a where cnt >1 

在这两部分之后，我们可以在另一个CTE或普通的SQL语句中使用CTE输出

所以这是我们使用CTE输出的普通SQL语句:

after these two parts we can use our CTE output in another CTE or in an ordinary SQL statement

so this is our ordinary SQL statement that uses our CTE output :

select * from a
order by r,d, cnt

尝试用问题中提到的数据制作测试表，然后对它运行此查询:)



看到您的数据后，请尝试以下方法:

Try to make a test table with your mentioned data in the question and run this query against it :)


EDIT 2 :
After seeing your data , try this one :

SELECT DepotCode, RouteCode, RouteName , ArbRouteName, TotalSale, DayNames, DayNumber ,ROW_NUMBER() OVER(PARTITION BY DepotCode, DayNumber  ORDER BY DepotCode, DayNumber) AS 'record'
FROM routes

如果解决了您的问题，请不要忘记接受它作为答案或投票；)

If it was solved your problem do not forget to accept it as an answer or vote it up ;)

这篇关于SQL Server查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！