Sql server：SQL查询帮助 [英] Sql server : SQL query help

问题描述

我的表格如下

My table is as following

item | date       | q_in | q_out |
----------------------------------
 a   | 25-08-2016 |  100 |  50
 b   | 26-09-2016 |  100 |   0 
----- upto 
 b   | 10-09-2016 |    0 | 100

我需要这样的输出： -

I need output like this :-

item | open_stock | inward | outward | balance|
-----|------------|--------|---------|--------|
 a   |    1500    |  10000 |  500    | 1100   |
 b   |     500    |   5000 | 1000    | 4500   | 

其中



open_stock = q_in的平衡 - q_out高达31- 08-2016

向内=从2016年9月1日到2016年9月10日的总q_in

向外=从2016年9月1日到10月9日的总q_out- 2016

余额= open_stock +向内 - 向外



我尝试过：



我的查询如下，需要你的帮助

where

open_stock = balance of q_in - q_out upto 31-08-2016
inward = total q_in from 01-09-2016 to 10-09-2016
outward = total q_out from 01-09-2016 to 10-09-2016
balance = open_stock + inward - outward

What I have tried:

my query is as follows which needs your help

select 
    item,
    (select 
         (SUM(q_in) - SUM(q_out))
     from 
         sale_table
     where 
         date >= '2016-08-25' and date <= '2016-08-31') as open_stock,
    SUM(q_in) as inward,
    SUM(q_out) as outward ,
    (open_stock + inward - outward) as balance
from 
    sale_table
where 
    date >= '2016-09-01' and date <= '2016-09-10'
group by 
    item

i需要值而不是open_stock，向内并且向外



提前感谢......

i need values instead open_stock , inward and outward

thanks in advance ......

推荐答案

也许像

Perhaps something like

SELECT a.item,
       a.open_stock,
       a.inward,
       a.outward,
       a.open_stock + a.inward - a.outward
FROM (select 
            item,
            (select 
                 (SUM(q_in) - SUM(q_out))
             from 
                 sale_table s2
             where 
                     s2.date >= '2016-08-25' 
                 and s2.date <= '2016-08-31'
                 and s2.item = sale_table.item) as open_stock,
            SUM(q_in) as inward,
            SUM(q_out) as outward 
        from 
            sale_table
        where 
            date >= '2016-09-01' and date <= '2016-09-10'
        group by 
            item) AS a

好像你忘记了查询和子查询之间的正确关系：

Seems you forgot to define proper relationship between query and subquery:

SELECT a.item, a.open_stock, a.inward, a.outward, a.open_stock + a.inward - a.outward as balance
FROM (select item, 
            (
             select (SUM(q_in) - SUM(q_out))
             from sale_table
             where date >= '2016-08-25' and date <= '2016-08-31' and item = first.item
            ) as open_stock,
            SUM(q_in) as inward,
            SUM(q_out) as outward 
        from sale_table As first
        where date >= '2016-09-01' and date <= '2016-09-10'
        group by first.item) AS a

这篇关于Sql server：SQL查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！