MySql 使用现有列创建并填充新列

发布于 2024-11-25 09:35:56 字数 994 浏览 3 评论 0原文

我有一张带有值的表。我正在尝试创建一个新列，其行值将取决于现有列的行值。例如，在下表中，如果 id 列从索引 6 到索引 7 的子字符串大于 10，则该行的新列的值应为索引 1-4 处 id 的子字符串 < em>连字符 substr(id, 1, 4)+1。否则，该值应为 substr(id, 1, 4) -1 连字符 substr(id, 1, 4)。

id          New Column  Name
M20061012   2006-2007   Jude
K20070212   2006-2007   Anne
D20071214   2007-2008   John

这是表中每一行的算法：

if substr(id, 5, 1) < 10
   New Column value at that row = substr(id, 1, 4) - substr(id, 1, 4)+1
else
   New Column value at that row = substr(id, 1, 4)-1 - substr(id, 1, 4)

任何帮助将不胜感激谢谢

编辑： 所以我按照@scwagner的建议尝试了这个，

SELECT
id,
(CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) 
ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1)   END) AS `New Column`,
Name
FROM new

其中new是表的名称。但这会返回一个包含所有行值 = BLOB 的列。我该如何解决这个问题？

原文

I have a table with values. I am trying to create a new column whose row value will depend on that of an existing column. For example, in the table below, if the substr of the id column from index 6 to index 7 is greater than 10, then the value of the new column at that row should be the sub string of the id at index 1-4 hyphen substr(id, 1, 4)+1. Else the value should be substr(id, 1, 4) -1 hyphen substr(id, 1, 4).

id          New Column  Name
M20061012   2006-2007   Jude
K20070212   2006-2007   Anne
D20071214   2007-2008   John

Here is the algorithm for each row of the table:

if substr(id, 5, 1) < 10
   New Column value at that row = substr(id, 1, 4) - substr(id, 1, 4)+1
else
   New Column value at that row = substr(id, 1, 4)-1 - substr(id, 1, 4)

Any help will be greatly appreciated
Thanks

EDIT:
So I tried this as suggested by @scwagner

SELECT
id,
(CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) 
ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1)   END) AS `New Column`,
Name
FROM new

Where new is the name of the table. But this returns a column with all row values = BLOB.
How can I fix that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

审判长 2024-12-02 09:35:56

您可能想在更多数据上进行测试，但这适用于 MySQL 5.1.41-3ubuntu12.8，并使用您提供的测试数据：

SELECT
  id,
  (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) END) AS `New Column`,
  Name
FROM
  <your table>

You may want to test this on more data, but this worked on MySQL 5.1.41-3ubuntu12.8 with the test data that you provided:

SELECT
  id,
  (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) END) AS `New Column`,
  Name
FROM
  <your table>

回复收藏 0 原文

只是在用心讲痛 2024-12-02 09:35:56

想通了：

 SELECT
    id,
    (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
    THEN CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1 AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR)) 
    ELSE CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) AS CHAR)   END) AS `New Column`,
    Name
    FROM new

Figured it out:

 SELECT
    id,
    (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
    THEN CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1 AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR)) 
    ELSE CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) AS CHAR)   END) AS `New Column`,
    Name
    FROM new

回复收藏 0 原文

~没有更多了~