MySQL包含来自另一个表的重复值

Question

I have a user profile which stores a number representing a portfolio value (currency). I have another table which lists recommended investment purchases, with the percentage of the portfolio to be allocated to each fund by risk factor. I need to recalculate the dollar value of the fund purchases when ever the percentages change, or when the portfolio value changes.

I don't want to create a "real" table for every client just to repeat the portfolio value for the calculations. How can I construct a query to select the portfolio value for the client and include it for every row for the calculations to use?

User   PortfolioValue
1      200,000
2      50,000
-----------------------------

Asset     Fund  Risk1Pct   Risk2Pct  
CashE     MM       6%         4%
Fixed     PIMCO    4%         6%
US Equity UEF     50%        75%

Desired Sample Output for User 1 via table generator (visual table, not database table)

Asset     Fund  Risk1Pct  R1Calculation    Risk2Pct  R2Calculation
CashE     MM       6%    (6% * 200,000)     4%      (4% * 200,000)
Fixed     PIMCO    4%    (4% * 200,000)     6%      (6% * 200,000)
US Equity UEF     50%    (50% * 200,000)   75%     (75% * 200,000)

The portfolio value has been added to wp_usermeta, which has that key - value structure. The portfolio information is in a table called demodb.

The Risk Percentages are in floating point.

Update -------------------------------------------- I have the values I need added into usermeta (user_id, meta_key, meta_value).

There are 9 company tables with the investment options for each company (company_id, assetclass, fundname, risk1pct, risk1action, risk2pct, risk2action, risk3pct, risk3action, risk3pct, risk4action).

I created a table userportfolio (user_id, company_id, total401k, companystockamt, loansamt, withdrawalsamt).

I'm trying to get everything connected so that I have out put like user_id, company_id, assetclass, fundname, (risk1pct * total401k), risk1action, (risk2pct * total401k), risk2action, (risk3pct * total401k), risk3action, (risk4pct * total401k), risk4action.

I can't tell whether I actually need the userportfolio table. Is this info what was meant by the schema?

Answer 1

Assuming your % values are decimals (and that I understand the question), we can do this with a simple join:

Let's call your first table u (users) and your second table ri (recommended investments)

SELECT
    u.User,
    u.PortfolioValue,
    ri.Asset,
    ri.Fund,
    ri.Risk1Pct,
    (u.PortfolioValue * ri.Risk1Pct) AS `R1Calculation`,
    ri.Risk2Pct,
    (u.PortfolioValue * ri.Risk2Pct) AS `R2Calculation`
FROM table1 `u`
JOIN table2 `ri`