将行中的行从查询插入到MySQL表中，并将SQL函数返回到其他字段 [英] Insert row into MySQL table result from query into one field and SQL function returned values into other fields

问题描述

在一个查询中插入一行可以在MYSQL表中以及其他字段中从MySQL函数返回的值是可能的吗？

例如，我有下表：

  CREATE TABLE monthly_hours 
（
 ProjectID int，
 Year int， 
 Month int，
 monthTotalTime int 
）; 
  

• 其中显示了一个特定月份的项目工作时间给定的一年。

我有一个查询，总计花费在一个项目上的时间：

  INSERT INTO time_monthly_hours（`ProjectID`，`monthTotalTime`）
 SELECT jiraissue.PROJECT，SUM（worklog.timeworked）
 FROM worklog，jiraissue 
 WHERE worklog.issueid = jiraissue.ID 
 AND jiraissue.PROJECT = 13262 
 AND worklog.startdate BETWEEN'2017-01-01 00:00:00'AND'2017-01-18 23:59:59' 
  

而且我想在月/月表中插入projectID和monthTotalTime到一行，一年。

我试过：

  INSERT INTO time_monthly_hours（ProjectID， monthTotalTime，Year，Month）VALUES 
（
 SELECT jiraissue.PROJECT，SUM（worklog.timeworked）
 FROM worklog，jiraissue 
 WHERE worklog.issueid = jiraissue.ID 
 AND jiraissue.PROJECT = 13262 
 AND worklog.startdate BETWEEN'2017-01-01 00:00:00'AND'2017-01-18 23:59:59'
），
 MONTH（CURRENT_DATE（）），YEAR（CURRENT_YEAR（））; 
  

但这是一个无效的查询。我搜索了很多，但找不到这样的解决方案，将SQL查询结果AND值从SQL函数同时放入一行。

<请你建议。我需要在MySQL级别执行此操作，而不是通过更高级别的工具。谢谢。

演示文稿更新：

（问题内容保持不变）

• 我尝试根据BeNice的要求增强格式化程度


• 我更正了标题中的语法略（需要使用复数为字段）的第一行问题的查询已更正为查询。谢谢！

解决方案

您的查询无效，因为您没有正确使用括号。当您使用 INSERT ... VALUES（）时，语法必须为：

  INSERT INTO< table> （col，col，col，col）VALUES（val，val，val，val）; 
  

但是，您本质上具有无效的语法，如：

  INSERT INTO< table> （col，col，col，col）VALUES（val，val），val，val; 
  

使用一个返回两列的子查询还有一个问题。这不能代替INSERT语句的标量值。

此外，您将MONTH（）表达式与Year列和YEAR（）表达到您的月份列。

但它比您制作的更简单。您可以将常量表达式放入SELECT中，因此SELECT有四列。

  INSERT INTO time_monthly_hours（ProjectID，monthTotalTime，Year，月）
 SELECT jiraissue.PROJECT，SUM（worklog.timeworked），
 YEAR（CURRENT_DATE（）），MONTH（CURRENT_DATE（））
 FROM worklog，jiraissue 
 WHERE worklog。 issueid = jiraissue.ID 
 AND jiraissue.PROJECT = 13262 
 AND worklog.startdate BETWEEN'2017-01-01 00:00:00'AND'2017-01-18 23:59:59'
  

当您使用 INSERT ... SELECT 不需要 VALUES 关键字。

Is it possible, in one query to insert a row, into a MYSQL table and also values returned from MySQL function in other fields?

For example, I have the following table:

CREATE TABLE monthly_hours
(
  ProjectID int,
  Year int,
  Month int,
  monthTotalTime int
);

• which shows the time worked on a project for a given month in a given year.

And I have a query that sums the total time spent on a project:

INSERT INTO time_monthly_hours ( `ProjectID`, `monthTotalTime` ) 
 SELECT jiraissue.PROJECT, SUM(worklog.timeworked) 
 FROM worklog, jiraissue 
 WHERE worklog.issueid = jiraissue.ID 
  AND jiraissue.PROJECT = 13262 
  AND worklog.startdate BETWEEN '2017-01-01 00:00:00' AND '2017-01-18 23:59:59';

And I want to insert the projectID and the monthTotalTime into a row in monthly_hours table, with the month and year.

I tried:

INSERT INTO time_monthly_hours ( ProjectID, monthTotalTime,  Year, Month ) VALUES
( 
 SELECT jiraissue.PROJECT, SUM(worklog.timeworked) 
 FROM worklog, jiraissue 
 WHERE worklog.issueid = jiraissue.ID 
  AND jiraissue.PROJECT = 13262 
  AND worklog.startdate BETWEEN '2017-01-01 00:00:00' AND '2017-01-18 23:59:59' 
), 
  MONTH(CURRENT_DATE()), YEAR(CURRENT_YEAR() );

But this was an invalid query. I've searched quite a bit but can't find a solution like this to put both an SQL query result AND values from a SQL function into a row at the same time.

Please can you advise. I need to do this at the MySQL level, not via higher level tools. Thank you.

presentational update:

(question content remains the same)

• I tried to enhance the formatting to be more readable on request from BeNice
• I corrected the grammar in the title slightly (needed to use plural for fields) also "queryto" in first line of question was corrected to query to. Thanks!

解决方案

Your query is invalid because you didn't use the parentheses correctly. When you use INSERT...VALUES(), the syntax must be:

INSERT INTO <table> (col, col, col, col) VALUES (val, val, val, val);

But you essentially had invalid syntax, something like:

INSERT INTO <table> (col, col, col, col) VALUES (val, val), val, val;

There's another problem with using one subquery that returns two columns. That can't be used in place of a scalar value for an INSERT statement.

Also, you matched your MONTH() expression to your Year column and your YEAR() expression to your Month column.

But it's simpler than you're making it. You can put constant expressions into a SELECT, so the SELECT has four columns.

INSERT INTO time_monthly_hours ( ProjectID, monthTotalTime,  Year, Month )     
 SELECT jiraissue.PROJECT, SUM(worklog.timeworked),
  YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE())
 FROM worklog, jiraissue 
 WHERE worklog.issueid = jiraissue.ID 
  AND jiraissue.PROJECT = 13262 
  AND worklog.startdate BETWEEN '2017-01-01 00:00:00' AND '2017-01-18 23:59:59' 

When you use INSERT...SELECT you don't need the VALUES keyword.

这篇关于将行中的行从查询插入到MySQL表中，并将SQL函数返回到其他字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！