mysql - 如何动态地将行值显示为列

Question

表:'坦克'

Tank_ID     Tank
1           Tank 1
2           Tank 2
3           Tank 3
4           Tank 4 
5           Tank 5

表:'Tank_Details'

Tank_ID      In_Stock       Receipt       Date
1            1000           1000          2019-07-10
2            2000           2000          2019-07-10
3            3000           3000          2019-07-10
1            300             0            2019-07-10
1            250             0            2019-07-15
3            2500            0            2019-07-15
2            1800            0            2019-07-15
3            2200            0            2019-07-15
1            500             250          2019-07-15
2            2000            200          2019-07-15
3            3000            800          2019-07-15
1            750             250          2019-07-15
4            350             350          2019-07-15
1            700             0            2019-07-20
2            1800            0            2019-07-20
3            2800            0            2019-07-20
4            300             0            2019-07-20
1            1000            300          2019-07-20
2            3000            1200         2019-07-20
3            4200            1400         2019-07-20
4            1200            900          2019-07-20
5            20              20           2019-07-20
1            1500            500          2019-07-20

它显示刚开始的库存:

select DATE_FORMAT(DATE( s.day), '%d/%m/%Y') as Date, s.`Opening Stock`
from (
  select g.day, sum(t.in_stock) `Opening Stock`
  from diesel_tank_details t inner join (
    select date(date) day, tank_id, min(date) mindate
    from diesel_tank_details
    where in_stock <> 0
    group by date(date), tank_id
  ) g on g.tank_id = t.tank_id and g.mindate = t.date   
  group by g.day
) s inner join (SELECT DATE(Date) as "day", sum(Receipt) as "Receipt" FROM `diesel_tank_details` GROUP BY DATE(Date)) r on r.day = s.day and YEAR(r.day) = 2019 AND MONTH(r.day) = 7 GROUP BY DATE(r.day)

我想显示所有坦克个体“instock”每天的第一条记录。

期望的输出:

Date             tank1   tank2  tank3  tank4  tank5   OpeningStock  
10/07/2019        1000    2000   3000    0      0        6000          
15/07/2019         250    2500   1800    350    0        4900          
20/07/2019         700    1800   2800    300    20       5620

Answer 1

试试这个:-

    with cte as (
    select row_number() over (partition by tank.tank_id order by date) as Slno,  Tank.Tank_id, tank.Tank, TD.In_stock, Td. Receipt, Td.Date 
    from Tank inner join Tank_details as TD on tank.Tank_Id=td.tank_Id
    )
    select date, [tank1], [tank2], [tank3], [tank4], [tank5], ([tank1] + [tank2] + [tank3] + [tank4] + [tank5]) as OpeningBalance from 
    (select tank, date, In_stock from cte where Slno = 1 )
    as d
    pivot 
    ( max(in_stock) for tank in ( [tank1], [tank2], [tank3], [tank4], [tank5] )
    ) as Pv

您只需为您的日期创建数据透视表。然后计算您在规定日期的余额总和。