如何增加我在数据库中创建的新列？ [英] how to increment the new column i created in database?

问题描述

请在这种情况下帮助我。



表1

Please help me in this scenario.

table 1

id|name|age|status
1 |red |10 | m
2 |ben |30 | s
3 |app |90 | s
4 |low |10 | s
5 |son |50 | g

表2

table 2

id|master|status
10|chuka | s
20|pogi  | b
30|bell  | t

如果我要创建一个新的表/查询，它看起来像这样吗？

If I will create a new table/query it looks like this?

DECLARE @power INT
SET @power = 40;
SELECT @power AS Level5, name, status, age 
FROM table1 inner join table2 on table2.status = table1.status
WHERE table1.status = 's'

输出看起来像这样

表3

The output is looks like this
table 3

Level5|name|status|age
40|ben | s    |30
40|app | s    |90
40|low | s    |10

我的问题是，我想让每一行的Level5列增加10



预计输出我要显示

My problem is, I want to make the column Level5 will increment by 10 for every row

Expected OUTPUT I want to display

Level5|name|status|age
50|ben | s    |30
60|app | s    |90
70|low | s    |10

请帮助我！谢谢！

Please Help me! Thank you!

推荐答案

你可以借助递归CTE [ ^ ]

You can do this with a help of recursive CTE[^]

;WITH CTE AS(
  SELECT @power AS Level5, 1 AS rn
  UNION ALL
  SELECT Level5 + 10, rn + 1 FROM cte WHERE rn < 100
  )
SELECT CTE.Level5, t.name, t.status, t.age FROM
(SELECT name, table2.status, age,
ROW_NUMBER() OVER (ORDER BY table1.id) AS rn
FROM table1 inner join table2 on table2.status = table1.status
WHERE table2.status = 's') AS t
INNER JOIN CTE ON t.rn=cte.rn

SQL小提琴 [ ^ ]

您好，



以下是几个应该引导你的链接

• http://stackoverflow.com/questions/534240/mssql-select-statement-with -incremental-integer-column-not-from-a-table [ ^ ]
• http://msdn.microsoft.com/en-us/library/ms186734.aspx [ ^ ]
• http://stackoverflow.com/questions/3214544/how-t o-increment-in-a-select-query [ ^ ]
• http://stackoverflow.com/questions/16555454/how-to-generate-auto-increment-field-in-select-query [ ^ ]
• http://msdn.microsoft.com/en-us/library /aa933208(v=sql.80).aspx [ ^ ]

Hello,

Here are couple of link's which should guide you

• http://stackoverflow.com/questions/534240/mssql-select-statement-with-incremental-integer-column-not-from-a-table[^]
• http://msdn.microsoft.com/en-us/library/ms186734.aspx[^]
• http://stackoverflow.com/questions/3214544/how-to-increment-in-a-select-query[^]
• http://stackoverflow.com/questions/16555454/how-to-generate-auto-increment-field-in-select-query[^]
• http://msdn.microsoft.com/en-us/library/aa933208(v=sql.80).aspx[^]

检查出来

增加colu mn with sql developer

这篇关于如何增加我在数据库中创建的新列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！