我是一个MySQL新手,需要帮助做一些我想做的事情。我有三张桌子eod-stock,company和share_percentage。company表包含在证券交易所上市的公司的所有数据,代码由证券交易所指定。eod_stock具有一天中每分钟事务的数据,并且具有相同的company_code字段。share_percentage有股票数量等数据。我想要的是下面的步骤:
步骤1:将eod_stock表中的company_code字段与company表中的代码字段匹配。字段的值是相同的(为公司指定代码)。
步骤2:根据字段sector_id进行排序。此字段位于company表中。这些部门都有独特的不同ids,他们有公司在他们(有点像类别-子类别)。部门id是类别,属于某一特定部门的公司属于该类别)
步骤3:根据last_trade_price (在eod_stock表)字段和datetime (最后)对步骤2中的公司进行排序
步骤4:通过company_code字段将no_of_shares与share_percentage表匹配
步骤5:将step3与step4相乘
步骤6:第5步之和(按部门分列的公司总数)。例如,所有银行的总和)
步骤7:step 5除以step 6
步骤8:按部门分列的步骤7之和(例如:所有银行)=结果
我希望我的解释足够详细,我做不到。任何帮助都是非常感谢的。提前感谢!
表样和我想要的:
TABLE company:
code: "google", "HSBC", "yahoo", "SCB"
sector_id: "1" ,"2", "1", "2"
TABLE eod_stock:
company_code : "google", "HSBC", "yahoo", "SCB"
ltp: "110", "115.2", "122.4", 105"
datetime: "1/1/2014 11:00", "1/1/2014 11:00", "1/1/2014 11:00", "1/1/2014 11:00"
company_code : "google", "HSBC", "yahoo", "SCB"
ltp: "112", "108.2", "112.4", 105.80"
datetime: "1/1/2014 11:01", "1/1/2014 11:01", "1/1/2014 11:01", "1/1/2014 11:01"
TABLE : share_percentage
company_code: "google", "HSBC", "yahoo", "SCB"
total_share: "12000", "20000", "5000", "18000"我的密码是:
SELECT company.sector_id,
SUM(eod_stock.ltp * share_percentage.total_share) AS Market_CAP
FROM company
INNER JOIN (SELECT max(datetime) as lastTime,company_code
FROM eod_stock
GROUP BY company_code) as LAST_TRADE
ON LAST_TRADE.company_code = company.code
INNER JOIN eod_stock on eod_stock.datetime = Last_trade.lastTime
and eod_stock.company_code = company.code
INNER JOIN share_percentage on share_percentage.company_code = company.code
GROUP BY company.sector_id;我知道我的sql不好,但我希望实现的是8.3这样的最终结果。我知道我可以通过破坏操作来用php对其进行编码,但如果可能的话,我希望能够从sql中实现它,以节省时间。
发布于 2014-01-01 17:02:23
SELECT company.sector_id, sum(stock.ltp*share_percentage.total_shares)
FROM company, share_percentage, (
SELECT eod_stock.company_code, eod_stock.ltp
FROM eod_stock
INNER JOIN (
select eod_stock.company_code, max(datetime) last
from eod_stock
group by eod_stock.company_code
) as last
on (eod_stock.company_code = last.company_code and eod_stock.datetime = last.last )
) as stock
WHERE company.code = stock.company_code and company.code=share_percentage.company_code使用这个,更快更简单
发布于 2013-12-31 20:26:22
看上去,你似乎是在试图计算每一个部门的市值。如果是这样的话,下面的查询应该这样做。
SELECT COMPANY.SECTOR_ID,
SUM(eod_stoc.ltp * share_percentage.total_shares) AS Market_CAP
FROM COMPANY
INNER JOIN (
SELECT max(datetime) lastTime
,company_code
FROM eod_stock
GROUP BY company_code
) LAST_TRADE
ON Last_trade.company_code = company.company_code
INNER JOIN eod_stock on eod_stock.datetime = Last_trade.lasttime
and eod_stock.company_code = company.company_code
INNER JOIN share_percentage on share_percentage.company_code = company.company_code
GROUP BY COMPANY.SECTOR_ID;https://stackoverflow.com/questions/20861973
复制相似问题