在they中，我如何比较积极和消极的行动，以便它们发生？

Question

我有一张这样的桌子：

;WITH CTE AS
( SELECT *
  FROM (VALUES(1,'BlueCar',NULL),
              (2,'RedCar',NULL),
              (3,NULL,'BlueCar'),
              (4,'GreenCar',NULL),
              (5,NULL,'RedCar'),
              (6,'BlueCar',NULL)
        ) AS ValuesTable(Time,Buy,Sell)
)
SELECT *
FROM CTE

Time Buy      Sell
1    BlueCar  NULL
2    RedCar   NULL
3    NULL     BlueCar
4    GreenCar NULL
5    NULL     RedCar
6    BlueCar  NULL

如何查询此表以获得仍在库存的汽车总数？时间栏是商店开张几天后的事。购买汽车的时间必须保持不变。

注意:输入的数据使库存中永远不会出现多辆汽车的情况。

预期产出

Time Buy
4    GreenCar
6    BlueCar

Answer 1

您可以使用not exists来完成这一任务。

;WITH CTE AS
( SELECT *
  FROM (VALUES(1,'BlueCar',NULL),
              (2,'RedCar',NULL),
              (3,NULL,'BlueCar'),
              (4,'GreenCar',NULL),
              (5,NULL,'RedCar'),
              (6,'BlueCar',NULL)
        ) AS ValuesTable(Time,Buy,Sell)
)
SELECT
    [Time], Buy
FROM CTE as T1
WHERE
    NOT EXISTS (SELECT 1 FROM CTE as T2 WHERE T2.TIME > T1.TIME AND T1.Buy = T2.Sell) AND
    BUY IS NOT NULL

Answer 2

在下面的查询中，我做了两个单独的聚合来获取每辆汽车的买卖计数。我离开了join买进卖出，假定交易商没有卖空任何实际上不存在的库存，这就不应该有丢失数据的风险。

然后，我加入到一个CTE的结果，它找到每辆车的最新时间。然后，这将对应于最近的汽车进入库存的时间，每种类型的汽车。

我还包括库存清点，这是您确实要求的，但是如果您以后决定扩展查询的范围，那么它可能对您很有用。

WITH yourTable AS (
    SELECT 1 AS Time, 'BlueCar' AS Buy, NULL AS Sell UNION ALL
    SELECT 2,'RedCar',NULL UNION ALL
    SELECT 3,NULL,'BlueCar' UNION ALL
    SELECT 4,'GreenCar',NULL UNION ALL
    SELECT 5,NULL,'RedCar' UNION ALL
    SELECT 6,'BlueCar',NULL
),
cte AS (
    SELECT Buy, Time
    FROM
    (
        SELECT Buy, Time,
            ROW_NUMBER() OVER (PARTITION BY Buy ORDER BY Time DESC) rn
        FROM yourTable
    ) t
    WHERE rn = 1
)

SELECT
    t1.Buy,
    t1.buy_cnt - COALESCE(t2.sell_cnt, 0) AS inventory,
    t3.Time
FROM
(
    SELECT Buy, COUNT(*) AS buy_cnt
    FROM yourTable
    GROUP BY Buy
) t1
LEFT JOIN
(
    SELECT Sell, COUNT(*) AS sell_cnt
    FROM yourTable
    GROUP BY Sell
) t2
    ON t1.Buy = t2.Sell
LEFT JOIN cte t3
    ON t1.Buy = t3.Buy
WHERE
    t1.Buy IS NOT NULL AND
t1.buy_cnt - COALESCE(t2.sell_cnt, 0) > 0
ORDER BY
    t3.Time;

输出：

​

​

演示这里：

​

雷克斯试验器

​

Answer 3

在某一时刻获得股票，你可以做到

SELECT car, SUM(Inc) total FROM
 (SELECT ID, Buy car,   1 Inc FROM tbl WHERE Buy>''
  UNION ALL
  SELECT ID, Sell car, -1 Inc FROM tbl WHERE Sell>'') coll
WHERE ID < 20  -- some cut-off time
GROUP BY car

我将两列Buy和Sell组合成一个(= car)，并随着每个操作(-1或1)的增加而添加另一列(inc)。其余的都很简单：select有一个group by小车，并在列inc上求和。

下面是一个小演示：http://rextester.com/LLQDW60692