MatBailie

这个 WHERE 第条适用 之后 这个 JOIN .

为了满足您的需要,您也需要过滤userlocation映射 之前 或 在期间 加入。要么在联接谓词中包含用户筛选器,要么在筛选用户的子查询中联接 (前者是通常的、更干净的方法) .

     FROM tbl_location
LEFT JOIN tbl_userlocation
       ON tbl_userlocation.fk_location = tbl_location.id 
      AND tbl_userlocation.fk_user     = :id

或

     FROM tbl_location
LEFT JOIN (SELECT * FROM tbl_userlocation WHERE fk_user = :id) AS tbl_userlocation 
       ON tbl_userlocation.fk_location = tbl_location.id

我想你想要这样的东西。。。

SELECT
  c.id,
  COUNT(p.id)
FROM
  categories        c
LEFT JOIN
  client_profiles   p
    ON  JSON_CONTAINS(p.areas,      '1',                    '$')
    AND JSON_CONTAINS(p.categories, CAST(c.id AS CHAR(32)), '$')
GROUP BY
  c.id

你不能,不能直接。

但是,如果将整个查询包装在子查询中,则它可以正常工作。

SELECT
  *
FROM
(
  SELECT
    Trade.TradeId, 
    Isnull(Securities.SecurityType,'Other') SecurityType, 
    TableName,
    CASE 
      WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId
                                                ELSE Trade.SecurityId
    END AS PricingSecurityID,
    sum(Trade.Quantity)OVER(Partition by Securities.SecurityType,
    SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity,
    --added porfolio id for Getsumofqantity
    Trade.PortfolioId,
    Trade.Price,
    case
      when (Buy = 1 and Long = 1) then 1
      when (Buy = 0 and Long = 0) then 1
                                  else 0
    end Position
  from
    Fireball_Reporting..Trade
  where
    porfolioid = 5
)
  AS data
WHERE
  Position = 1   

这意味着你不需要重复 CASE 语句在 WHERE 条款。(可维护且干燥)。

它也是一种结构,允许乐观者表现 犹如 你 有 只是重复你自己 哪里 条款。

它对其他RDBMS也是非常便携的。

在SQL Server中,您还有另一个选项…

SELECT
  Trade.TradeId, 
  Isnull(Securities.SecurityType,'Other') SecurityType, 
  TableName,
  CASE 
    WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId
                                              ELSE Trade.SecurityId
  END AS PricingSecurityID,
  sum(Trade.Quantity)OVER(Partition by Securities.SecurityType,
  SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity,
  --added porfolio id for Getsumofqantity
  Trade.PortfolioId,
  Trade.Price,
  position.val AS Position
from
  Fireball_Reporting..Trade
CROSS APPLY
(
  SELECT
    case
      when (Buy = 1 and Long = 1) then 1
      when (Buy = 0 and Long = 0) then 1
                                  else 0
    end AS val
)
  AS position
where
  porfolioid = 5
  AND position.val = 1