如何通过SQL语句合并数据库表中的数据？

发布于 2024-07-14 20:20:33 字数 630 浏览 12 评论 0原文

我有一个关于 Interbase 的有趣问题。我有一个记录集，其中每个记录都有一个数据输入字段和一个时间戳。我希望能够从记录中复制时间戳，以便在一天结束时最终字段将按照以下内容读取：

TIMESTAMP <Carriage return> <carriage return> Original data in field

因此显示该字段时的最终外观是：

time of entry

data that was entered

遗憾的是，这必须在数据库中完成级别，因为我无法访问前端软件。

我能想到的唯一解决方案是进行选择，使用自定义软件执行合并，然后逐行生成适当的 SQL 来更新记录。由于每天有数百条记录，因此速度相当慢，因此基于 SQL 的解决方案将是理想的选择。

过去，我成功地使用 || 将固定数据添加到字符串末尾，以便与 UPDATE 指令连接（数据||额外信息与所有记录”），但无法以以下格式进行这项工作：

result of search||original data

任何帮助将不胜感激。

原文

I have an interesting problem for Interbase. I have a record set which amongst other things has a data entry field and a timestamp for each record. I would like to be able to copy the timestamp from records so that at the end of the day the final field will read along the lines of:

TIMESTAMP <Carriage return> <carriage return> Original data in field

Thus the final appearance when the field is displayed is:

time of entry

data that was entered

Sadly, this must be accomplished at the database level as I do not have access to the front end software.

The only solution I have been able to come up with is do to a select, use custom software to perform the merge and then generate the appropriate SQL, line by line, to update the record. As there are several hundred records per day this is rather slow, so a SQL based solution would be ideal.

In the past I have succeeded in adding fixed data to the end of a string using || for concatenation with an UPDATE instruction (data||extra information which is the same for all records") but have trouble making this work in the format:

result of search||original data

Any help would be much appreciated.

分享到QQ

分享到微博

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

发布评论

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

三生路 2024-07-21 20:20:33

UPDATE FOO_TABLE
  SET DATA_ENTRY_FIELD = DATA_ENTRY_FIELD || TIMESTAMP_FIELD
WHERE ...;

这应该可行（假设 DATA_ENTRY_FIELD 有连接空间）。如果不起作用，请更具体地说明您遇到的问题。

UPDATE FOO_TABLE
  SET DATA_ENTRY_FIELD = DATA_ENTRY_FIELD || TIMESTAMP_FIELD
WHERE ...;

This should work (provided DATA_ENTRY_FIELD has room for the concatenation). Please be more specific about what problems you're having if it doesn't work.

回复收藏 0 原文

能否归途做我良人 2024-07-21 20:20:33

克雷格的答案应该有效，尽管如果你想要回车符，你也需要插入它们：（

UPDATE  foo_table
SET     data_entry_field = timestamp || '\n\n' || data_entry_field
WHERE   ...

假设Interbase接受\n作为回车符——实际上应该只是\r 甚至 Windows 上的 \r\n，但 \n 是相当标准的。）

MySQL 无法识别 || 连接运算符，因此您必须使用 CONCAT() 函数。 Interbase 可能是一样的。

Craig's answer should work, although if you want the carriage returns, you'll need to insert those too:

UPDATE  foo_table
SET     data_entry_field = timestamp || '\n\n' || data_entry_field
WHERE   ...

(Assuming that Interbase accepts \n as a carriage return -- which should really be just \r or even \r\n on Windows, but \n is fairly standard.)

MySQL doesn't recognize the || concatenation operator, and so you'd have to use the CONCAT() function instead. Interbase might be the same.

回复收藏 0 原文

~没有更多了~