我正在对具有几列和几行数据的SQL表进行查询，并且查询根据查询中给出的条件为每个唯一的第一列和第二列返回一行。
例如，我有下表 CC
product term bid offer bidcp offercp
AA sep14 20 10 x y
AA Sep14 15 9 p q
BA Sep14 30 15 as ps
XY Sep14 25 15 r t
XY Oct14 30 20 t r
XY Oct14 25 22 p q
当我在上表中运行查询时，它应返回以下数据
product term bid offer bidcp offercp
AA sep14 20 9 x q(coming from a record which has lowest offer)
BA Sep14 30 15 as ps
XY Sep14 25 15 r t
XY Oct14 30 20 t r
当我执行以下查询其分组的数据，CC甚至bidcp和offercp并返回几乎所有的行既offercp与bidcp一种或另一种方式是独一无二的，但我只是想bidcp和offercp是在那里bid和offer从假设对两者的来了bid，并offer每个产品和字词都是唯一的
select product,term,max(bid) as bid,min(offer) as offer,bidcp,offercp from canadiancrudes where product like '%/%' group by product,term,bidcp,offercp
但是，当我从groupby子句中删除了bidcp和offercp时，它给我带来了一个明显的错误
Column 'CC.BidCP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
有更好的解决方法吗？
在这种情况下，您需要2个CTE：
WITH o AS (
SELECT product,term,offer,offercp, ROW_NUMBER() OVER (PARTITION BY product, term ORDER BY offer ASC) AS rn
FROM canadiancrudes where product like '%/%'
)
, b AS (
SELECT product,term,bid,bidcp, ROW_NUMBER() OVER (PARTITION BY product, term ORDER BY bid DESC) AS rn
FROM canadiancrudes where product like '%/%'
)
SELECT o.product,o.term,b.bid,o.offer,b.bidcp,o.offercp
FROM o
INNER JOIN b
ON o.product=b.product
AND o.term=b.term
WHERE o.rn=1
AND b.rn=1
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句