您好，我有两个表用于投资组合和库存数据详细信息。 我想通过价格的变化找出用户组合的总增长量。 例如：
用户A：
股票A -4.41
股票B -1.49
股票C 0.38
股票D 1.43
用户B
股票A -2.05
股票B .05
我希望显示如下表：USER Growth
A -4.09 B -2.1这是我的两个表：一个投资组合，其中所有买卖信息都由user_id保存
+-------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+----------------+ | portfolio_ID | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | | NULL | | | contest_id | int(11) | NO | | NULL | | | company_id | int(11) | NO | | NULL | | | share_amount | int(11) | NO | | NULL | | | buy_price | decimal(9,2) | NO | | NULL | | | total_buy_price | decimal(10,4) | NO | | NULL | | | buy_date | date | NO | | NULL | | | commision | decimal(9,2) | NO | | NULL | | | sell_share_amount | int(11) | NO | | NULL | | | sell_price | decimal(10,2) | NO | | NULL | | | total_sell_price | decimal(16,2) | NO | | NULL | | | sell_date | date | NO | | NULL | | | sell_commision | decimal(9,2) | NO | | NULL | | +-------------------+---------------+------+-----+---------+----------------+接下来的每日库存表：
+-----------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+-------+ | company_id | int(11) | NO | PRI | NULL | | | entry_date | date | NO | PRI | NULL | | | entry_timestamp | int(10) unsigned | NO | | NULL | | | open | decimal(16,2) | NO | | NULL | | | high | decimal(16,2) | NO | | NULL | | | low | decimal(16,2) | NO | | NULL | | | ltp | decimal(16,2) | NO | | NULL | | | ycp | decimal(16,2) | NO | | NULL | | | cse_price | decimal(9,2) | NO | | NULL | | | cse_volume | decimal(18,2) | NO | | NULL | | | total_trade | int(30) | NO | | NULL | | | total_volume | int(30) | NO | | NULL | | | total_value | decimal(18,4) | NO | | NULL | | | changes | float(10,2) | NO | | NULL | | | floating_cap | float(16,2) | NO | | NULL | | +-----------------+------------------+------+-----+---------+-------+这是我正在使用的sql：
SELECT po.user_id, ((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock AS e LEFT OUTER JOIN portfolio AS po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id;但它只返回第一个变化而不是增长变化的总和，然后我将其修改为：
SELECT po.user_id, SUM((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock AS e LEFT OUTER JOIN portfolio AS po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY growth ORDER BY growth DESC;但它会生成一个错误代码1056，无法增长
这里是sql小提琴： http ：//sqlfiddle.com/#！2/b3a83/2
谢谢阅读。
Hello I have two tables on for portfolio and and for stock data details. I want to find out how much the total growth of users portfolio by changes of price. For example:
User A:
stock A -4.41
Stock B -1.49
Stock C 0.38
Stock D 1.43
User B
Stock A -2.05
Stock B .05
I want to show table like: USER Growth
A -4.09 B -2.1here are my two tables: one portfolio where all buy sell info's are kept of users by user_id
+-------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+----------------+ | portfolio_ID | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | | NULL | | | contest_id | int(11) | NO | | NULL | | | company_id | int(11) | NO | | NULL | | | share_amount | int(11) | NO | | NULL | | | buy_price | decimal(9,2) | NO | | NULL | | | total_buy_price | decimal(10,4) | NO | | NULL | | | buy_date | date | NO | | NULL | | | commision | decimal(9,2) | NO | | NULL | | | sell_share_amount | int(11) | NO | | NULL | | | sell_price | decimal(10,2) | NO | | NULL | | | total_sell_price | decimal(16,2) | NO | | NULL | | | sell_date | date | NO | | NULL | | | sell_commision | decimal(9,2) | NO | | NULL | | +-------------------+---------------+------+-----+---------+----------------+Next the daily stock table:
+-----------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+-------+ | company_id | int(11) | NO | PRI | NULL | | | entry_date | date | NO | PRI | NULL | | | entry_timestamp | int(10) unsigned | NO | | NULL | | | open | decimal(16,2) | NO | | NULL | | | high | decimal(16,2) | NO | | NULL | | | low | decimal(16,2) | NO | | NULL | | | ltp | decimal(16,2) | NO | | NULL | | | ycp | decimal(16,2) | NO | | NULL | | | cse_price | decimal(9,2) | NO | | NULL | | | cse_volume | decimal(18,2) | NO | | NULL | | | total_trade | int(30) | NO | | NULL | | | total_volume | int(30) | NO | | NULL | | | total_value | decimal(18,4) | NO | | NULL | | | changes | float(10,2) | NO | | NULL | | | floating_cap | float(16,2) | NO | | NULL | | +-----------------+------------------+------+-----+---------+-------+this is the sql i am using:
SELECT po.user_id, ((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock AS e LEFT OUTER JOIN portfolio AS po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id;but it return only first changes not the sum of the changes of the growth, i then modified it like this:
SELECT po.user_id, SUM((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock AS e LEFT OUTER JOIN portfolio AS po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY growth ORDER BY growth DESC;but it generates a error code 1056, cant group on growth
here is the sql fiddle: http://sqlfiddle.com/#!2/b3a83/2
Thanks for reading.
最满意答案
你不想按growth分组。 你为什么要改变这个group by ？
SELECT po.user_id, SUM((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock e LEFT OUTER JOIN portfolio po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id ORDER BY growth DESC;但是，我怀疑你可能真的想要最大值减去最小值：
SELECT po.user_id, (MAX(e.ltp-po.buy_price) - MIN(e.ltp-po.buy_price))/po.buy_price) AS growth FROM eod_stock e LEFT OUTER JOIN portfolio po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id ORDER BY growth DESC;这给出了随着时间的整体观点。 但是，它没有显示方向。 所以50到100之间的值与从100到50的值相同。这可以修复，如果这是你真正想要的。
You don't want to group by growth. Why did you change the group by?
SELECT po.user_id, SUM((e.ltp-po.buy_price)/po.buy_price) AS growth FROM eod_stock e LEFT OUTER JOIN portfolio po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id ORDER BY growth DESC;However, I suspect that you might actually want the maximum minus the minimum:
SELECT po.user_id, (MAX(e.ltp-po.buy_price) - MIN(e.ltp-po.buy_price))/po.buy_price) AS growth FROM eod_stock e LEFT OUTER JOIN portfolio po ON e.company_id = po.company_id WHERE po.contest_id = 2 GROUP BY po.user_id ORDER BY growth DESC;This gives an overall view over time. However, it doesn't show the direction. So something going from 50 to 100 gets the same value as something going from 100 to 50. This can be fixed, if this is what you are really looking for.
更多推荐
发布评论