mysql函数返回查询字段,将行中的行从查询插入到MySQL表中，并将SQL函数返回到其他字段...

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

But you essentially had invalid syntax, something like:

INSERT INTO

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.