mysql如何在每个投资组合，股票的一行上显示值 [英] mysql how to display values on one row for each portfolio, stock

问题描述

我需要为每个投资组合/股票组合显示一行，以显示每个月的付款，因此，如果一家公司在5月和9月分别支付两次，分别为441和215，这将显示在一行中.目前，我的查询为每笔付款输出一行，列Jan-Dec

I need to display one row for each portfolio/stock combination, showing the payment for each month, so if a company pays twice a year in May and September of 441 and 215 respectively this would be shown in one row. Currently my query is outputting a row for each payment, with columns Jan - Dec

我尝试使用子查询无济于事，关于如何实现子查询的想法已经用完了.

I have tried using a Subquery to no avail, I am running out of ideas on how to achieve this.

SELECT
    s.symbol AS Symbol,
    p.code AS Portfolio,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jan' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jan,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Feb' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Feb,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Mar' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Mar,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Apr' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Apr,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'May' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS May,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jun' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jun,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jul' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jul,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Aug' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Aug,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Sep' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Sep,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Oct' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Oct,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Nov' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Nov

    FROM
    dm_transactions t
    INNER JOIN dm_dividends d ON (d.stock_id = t.stock_id)
    INNER JOIN dm_stocks s ON (s.id = t.stock_id) 
    INNER JOIN dm_portfolios p ON (t.portfolio_id = p.id)
    WHERE
    ANY_VALUE(d.pdate) >= CURDATE()
    AND
    p.code = 'SFT_DEA_CO'
    AND
    s.symbol = 'AV..L'
    AND
    t.user_id =2
    GROUP BY
    s.symbol, d.pdate

输出

Symbol Portfolio  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct 
AV..L  SFT_DEA_CO NULL NULL NULL NULL 441  NULL NULL NULL NULL NULL 
AV..L  SFT_DEA_CO NULL NULL NULL NULL NULL NULL NULL NULL 215  NULL 

下面的必填输出

Symbol Portfolio  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct 
AV..L  SFT_DEA_CO                     441                 215 

对于给定的交易品种，投资组合，我需要一行，并且没有NULL值.

I require one row for a given Symbol, Portfolio and without the NULL values.

非常感谢您的帮助.

谢谢

科林

推荐答案

您可以在子查询上使用聚合函数

You could use an aggregation function on a subquery

select  t.Symbol, t.Portfolio
  , max(Jan)
  , max(feb)
  , max(Mar)
  , max(Apr)
  , max(May)
  , max(Jun)
  , max(Jul)
  , max(Aug)
  , max(Sep)
  , max(Oct)
  , max(Nov)
from  (
  SELECT
  s.symbol AS Symbol,
  p.code AS Portfolio,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jan' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jan,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Feb' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Feb,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Mar' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Mar,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Apr' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Apr,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'May' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS May,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jun' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jun,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jul' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jul,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Aug' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Aug,
  nax(case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Sep' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Sep,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Oct' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Oct,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Nov' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Nov

  FROM dm_transactions t
  INNER JOIN dm_dividends d ON (d.stock_id = t.stock_id)
  INNER JOIN dm_stocks s ON (s.id = t.stock_id) 
  INNER JOIN dm_portfolios p ON (t.portfolio_id = p.id)
  WHERE ANY_VALUE(d.pdate) >= CURDATE()
  AND p.code = 'SFT_DEA_CO'
  AND s.symbol = 'AV..L'
  AND t.user_id =2
  GROUP BY    s.symbol,d.pdate
) t
GROUP BY   symbol, code

这篇关于mysql如何在每个投资组合，股票的一行上显示值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！