SQL返回每行，即使Null值也是如此 [英] SQL Return every row even if Null values

问题描述

我想从Contracts表返回所有行，但是第二个WHERE子句仅产生不为null的行. (换句话说，在低于"CAD"限制的代码中，意味着大约一半的可能行没有以加元进行交易的值，因此不会返回-而我希望返回所有可能的行，并在适用的情况下显示NULL值.)

I want to return all rows from a Contracts table but a second WHERE clause yields only rows which are not null. (In other words in the code below the 'CAD' restriction means approx half the possible rows have no value traded in Canadian Dollars and thus are not returned --whereas I want all possible rows returned showing NULL values where applicable).

我认为这是一个左自连接，但是在语法(和/或是否需要进行内部选择)方面感到挣扎，

I figure it's a Left Self Join but am struggling with the syntax (and/or whether I need to do an Inner Select),

 SELECT MeasurableID,
     EntityID,
     MIN (ContractPrice) AS LowPrice,
     MAX (ContractPrice) AS HighPrice

 FROM dbo.Contracts

 WHERE dbo.Contracts.MeasurableID = 2018
  AND  Contracts.CurrencyCode IN ( 'CAD' ) 

 GROUP BY
  dbo.Contracts.MeasurableID,
  dbo.Contracts.EntityID  

推荐答案

使用条件聚合:

SELECT  
  MeasurableID,
  EntityID,
  MIN (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS LowPrice,
  MAX (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS HighPrice
FROM dbo.Contracts
WHERE MeasurableID = 2018
GROUP BY MeasurableID, EntityID
ORDER BY MeasurableID, EntityID;

这篇关于SQL返回每行，即使Null值也是如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！