Subject only one topic, leave the interview 5 minutes to write, can't write directly to let me go, HR timing on the spot, the feeling is very simple, but not implement as required. Subject to the following two charts the users table and money, can only use a single SQL computing each user every month [not sure a few months] consumption amount, as shown in the result table results. Requires the use of the stored procedure,


I was trying to write is
Select a.u sername 'user name', ifnull (b.y e, 0) as' in January, ifnull (b2. Ye, 0) as' in February, ifnull (b3. Ye, 0) as' march from the users a
LEFT the JOIN money b on a.u serid=b.u serid and b.m onth='January'
LEFT the JOIN money b2 on a.u serid=b2. The userid and b2. ` month `='in February'
LEFT the JOIN money b3 on a.u serid=b3. Userid and b3. ` month `=' 'in March but do not conform to the requirements, if you use the stored procedure, a week did not write, hair white low self-esteem, who can help me!

CodePudding user response:

This will become the grammar of the column pivot line with this thing

CodePudding user response:

Select *
The from
(
The SELECT a.u serid, b.u sername, [month], the sum (ye) as ye from [money] a left join [users] b on a.u serid=b.u serid group by a.u serid, b.u sername, a. [month]
) t
Pivot (sum (ye) for t. [month] in [[January], [February], [in], [in])) as ourpivot
The order by userid

CodePudding user response:

Should be realize dynamic splicing SQL combining case when mysql

CodePudding user response:

MySQL no pivot syntax,

Can be written as the following syntax
Sum (case when make every January then num else 0=end)

CodePudding user response:

Who has examined the mysql 8.0 seems to pivot

CodePudding user response:

Key is to give how many money, less the estimated five hours also could not write, money is in place, points in minutes

CodePudding user response:

Haven't returned to the point, is to specify the mysql database, requires the use of stored procedure...

CodePudding user response:

Sorry, forgot to illustrate the database type,

CodePudding user response:

Dynamic splicing the month the column for the case when rows to columns
Because the topic has said very clearly not sure how many months so write dead sure is wrong

CodePudding user response:

Twelve months is common sense, not sure how many months, said is not sure don't know what month

CodePudding user response:

` ` in January, the SELECT u.u sername, a. a. ` ` in February, a. ` march `
The from (SELECT
M.u serid,
The sum (CASE WHEN m.M ONTH='in January THEN m.y e ELSE NULL END) AS' in January,
The sum (CASE WHEN m.M ONTH='in February THEN m.y e ELSE NULL END) AS' in February,
The sum (CASE WHEN m.M ONTH='march THEN m.y e ELSE NULL END) AS' march '
The FROM
Money m
GROUP BY
M.u serid
The ORDER BY
M.u serid) a left JOIN the users u on a.u serid=u.u serid