我想知道是否可以AND在同一列上使用多個條件Servicestack.OrmLite。這是SELECT我列印出來的陳述句，但它總是回傳 0。我應該從具有 id 1016 和 17 兩種規格的產品中獲取產品數量。
SELECT COUNT(DISTINCT "Product"."Id")
FROM "Product"
INNER JOIN "ProductManufacturer"
ON ("Product"."Id" = "ProductManufacturer"."ProductId")
INNER JOIN "ProductSpecificationAttribute"
ON ("Product"."Id" = "ProductSpecificationAttribute"."ProductId")
WHERE ("ProductManufacturer"."ManufacturerId" = 6)
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 1016)
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 17)
uj5u.com熱心網友回復：
單個列值不可能同時具有兩個值。
你想要的是：
AND
(
ProductSpecificationAttribute.SpecificationAttributeOptionId = 1016
OR
ProductSpecificationAttribute.SpecificationAttributeOptionId = 17
)
或者，更簡潔地說：
AND
(
ProductSpecificationAttribute.SpecificationAttributeOptionId
IN (1016, 17)
)
并關閉任何迫使您的工具“注入”“雙”“引號”“圍繞”“每個”“物體”“名稱”的選項，因為它使查詢文本無法管理。您也可以考慮使用別名和模式前綴，例如INNER JOIN dbo.ProductSpecificationAttribute AS psa...
在進一步澄清之后......目標是找到它們在不同行上同時具有這兩個屬性的產品，這從描述或 ORMLite 吐出的代碼中并不清楚。在這種情況下，這就是您想要的（有幾種方法可以做到這一點，但是將所有內容轉換為EXISTS也允許您DISTINCT從中洗掉COUNT，這永遠不會免費）：
SELECT COUNT(Product.Id) FROM dbo.Product AS p
WHERE EXISTS
(
SELECT 1 FROM dbo.ProductManufacturer AS pm
WHERE pm.ProductId = p.Id AND pm.ManufacturerId = 6
)
AND EXISTS
(
SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
WHERE psa.ProductId = p.Id
AND psa.SpecificationAttributeOptionId = 1016
)
AND EXISTS
(
SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
WHERE psa.ProductId = p.Id
AND psa.SpecificationAttributeOptionId = 17
);
如果索引很差，這會導致兩次掃描，您可以通過這樣說來更改它（未經測驗，但如果您可以生成 db<>fiddleProductSpecificationAttribute，我很樂意對其進行測驗：
SELECT COUNT(Product.Id) FROM dbo.Product AS p
WHERE EXISTS
(
SELECT 1 FROM dbo.ProductManufacturer AS pm
WHERE pm.ProductId = p.Id
AND pm.ManufacturerId = 6
)
AND EXISTS
(
SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
WHERE psa.ProductId = p.Id
AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY psa.ProductId, psa.SpecificationAttributeOptionId
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) > 1
);
表格中有一個指向 sProductManufacturer的串列也很奇怪- 通常會有一個指向另一個方向的 s 。ProductIDProductProductManufacturerID
無論如何，如果在創建超出基本 CRUD 的查詢時遇到問題，您可能會考慮使用 ORM 可以呼叫的存盤程序（不幸的是，這在某種程度上是所有 ORM 的限制 - 它們非常擅長基礎知識，涵蓋了 80% 的用例，但其他 20% 的情況很糟糕——不幸的是，我們中的大多數人很快就會需要另外 20%）。
uj5u.com熱心網友回復：
如果按產品分組并在HAVING子句中設定條件，則可以獲得所需的所有產品 ID：
SELECT p.Id
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2; -- both specifications must exist
如果您想計算這些產品，您可以將上述查詢用作子查詢或 cte 并計算行數：
WITH cte AS (
SELECT p.Id
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2;
)
SELECT COUNT(*) FROM cte;
或者，使用COUNT()視窗函式：
SELECT DISTINCT COUNT(*) OVER ()
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422531.html
標籤：