Альтернативный заголовок мог бы быть: Проверить на существование нескольких строк?
Используя комбинацию SQL и C# я хочу, чтобы метод возвратил true, если все продукты в списке существуют в таблице. Если бы это может быть сделано все в SQL, который был бы предпочтителен. Я записал метод, который возвращается ли сингл productID
существует с помощью следующего SQL:
SELECT productID FROM Products WHERE ProductID = @productID
Если это возвращает строку, то c# метод возвращает true, ложь иначе.
Теперь я задаюсь вопросом, есть ли у меня список идентификаторов продукта (не огромный список, обратите внимание, обычно под 20). Как я могу записать запрос, который возвратит строку, если весь идентификатор продукта будет существовать и никакая строка, если один или несколько идентификатор продукта не существует?
(Maybe something involving "IN" like:
SELECT * FROM Products WHERE ProductID IN ('1', '10', '100', 'ABC'))
Править:
То, как результат выражается, не важно для меня. Возвращает ли запрос a 1
или 0
, пустой набор результатов или непустой, TRUE или FALSE не имеет значения. Я предпочел бы ответ, который является 1) легкий читать и понять и 2) производительный
Я предполагал конкатенацию списка идентификатора продукта с SQL. Очевидно, это открывает код до Внедрения SQL (идентификатор продукта на самом деле varchar
. в этом случае шанс является небольшим, но все еще хотят избежать той возможности). Таким образом, если бы существует путь вокруг этого, которое было бы лучше. Использование SQL Server 2005.
Идентификатор продукта varchar
Учитывая ваш обновленный вопрос, это простейшие формы:
Если ProductID
уникален, вы хотите
SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)
, а затем сверьте этот результат с 3
, количество продуктов вы запрашиваете (эта последняя часть может быть выполнена на SQL, но может быть проще сделать это на C #, если вы не делаете еще больше в SQL).
Если ProductID
не уникален, то он равен
SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)
Когда считалось, что вопрос требует возврата строк, когда присутствуют все ProductIds
, и ни один другой:
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)
или
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)
если вы действительно собираетесь что-то делать с результатами. В противном случае простой SELECT 1 WHERE (SELECT ...) = 3
будет делать то, что указано или подразумевается в других ответах.
// not familiar with C#, but C#'s equivalent of PHP's:
$count = count($productIds); // where $productIds is the array you also use in IN (...)
SELECT IF ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)) = $count, 1, 0)
Где этот список продуктов, существование которых вы пытаетесь определить? Если этот список существует в другой таблице, вы можете сделать это
declare @are_equal bit
declare @products int
SELECT @products =
count(pl.id)
FROM ProductList pl
JOIN Products p
ON pl.productId = p.productId
select @are_equal = @products == select count(id) from ProductList
Изменить:
Затем проделайте ВСЮ работу на C #. Кэшируйте где-нибудь фактический список продуктов в вашем приложении и выполните LINQ-запрос.
var compareProducts = new List<Product>(){p1,p2,p3,p4,p5};
var found = From p in GetAllProducts()
Join cp in compareProducts on cp.Id equals p.Id
select p;
return compareProducts.Count == found.Count;
Это предотвращает создание SQL-запросов вручную и сохраняет всю логику вашего приложения в приложении.
Если вы используете SQL Server 2008, я бы создал хранимую процедуру, которая принимает параметр с табличным значением . Тогда запрос должен иметь особенно простую форму:
CREATE PROCEDURE usp_CheckAll
(@param dbo.ProductTableType READONLY)
AS
BEGIN
SELECT CAST(1 AS bit) AS Result
WHERE (SELECT COUNT(DISTINCT ProductID) FROM @param)
= (SELECT COUNT(DISTINCT p.ProductID) FROM @param AS p
INNER JOIN Products
ON p.ProductID = Products.ProductID)
END
Я изменил это, чтобы вернуть строку, как вам кажется. Есть и другие способы сделать это с помощью WHERE NOT EXISTS (LEFT JOIN здесь WHERE rhs IS NULL):
CREATE PROCEDURE usp_CheckAll
(@param dbo.ProductTableType READONLY)
AS
BEGIN
SELECT CAST(1 AS bit) AS Result
WHERE NOT EXISTS (
SELECT * FROM @param AS p
LEFT JOIN Products
ON p.ProductID = Products.ProductID
WHERE Products.ProductID IS NULL
)
END
Если предложение IN является параметром (либо для SP, либо для встроенного SQL), то это всегда можно сделать:
SELECT (SELECT COUNT(1)
FROM product_a
WHERE product_id IN (1, 8, 100)
) = (number of commas in product_id as constant)
Если предложение IN является таблицей, то это всегда можно сделать:
SELECT (SELECT COUNT(*)
FROM product_a
WHERE product_id IN (SELECT Products
FROM #WorkTable)
) = (SELECT COUNT(*)
FROM #WorkTable)
Если предложение IN сложное, то либо поместите его в таблицу, либо запишите дважды.
Вашему C # нужно будет немного поработать (подсчитать количество переданных идентификаторов), но попробуйте следующее:
select (select count(*) from players where productid in (1, 10, 100, 1000)) = 4
Изменить:
4 определенно можно параметризовать, так как может список целых чисел.
Если вы не генерируете SQL из строки, введенной пользователем, вам не нужно беспокоиться об атаках. Если да, вам просто нужно убедиться, что вы получаете только целые числа. Например, если вы взяли строку «1, 2, 3, 4», вы бы сделали что-то вроде
String.Join(",", input.Split(",").Select(s => Int32.Parse(s).ToString()))
. Это вызовет ошибку, если вы ошиблись. Затем просто установите это как параметр.
Также убедитесь, что используется особый случай, если items.Count == 0, так как ваша БД захлебнется, если вы отправите его , где ParameterID in ()
.
Если у вас есть идентификаторы, хранящиеся во временной таблице (что может быть выполнено с помощью некоторой функции C # или простого SQL), тогда проблема становится простой и решаемой в SQL.
select "all exist"
where (select case when count(distinct t.id) = (select count(distinct id) from #products) then "true" else "false" end
from ProductTable t, #products p
where t.id = p.id) = "true"
Это вернет «все существует», если все продукты в #products
существуют в целевой таблице ( ProductTable
), и не вернет строку, если указанное выше неверно.
Если вы не желаете записывать во временную таблицу, вам нужно ввести какой-либо параметр для количества продуктов, которые вы пытаетесь найти, и заменить временную таблицу на «in»; предложение, чтобы подзапрос выглядел так:
SELECT "All Exist"
WHERE(
SELECT case when count(distinct t.id) = @ProductCount then "true" else "false"
FROM ProductTable t
WHERE t.id in (1,100,10,20) -- example IDs
) = "true"
DECLARE @values TABLE (ProductId int)
INSERT @values (1)
INSERT @values (10)
INSERT @values (100)
SELECT CASE WHEN (SELECT COUNT(*) FROM @values v) =
(SELECT COUNT(*) FROM Products p WHERE p.ProductId IN
(SELECT v.ProductId FROM @values v))
THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END [AreAllFound]
Предполагая, что вы используете SQL Server, логический тип не существует, но существует битовый тип, который может содержать только 0 или 1, где 0 представляет False, а 1 представляет True.
Я бы пошел следующим образом:
select 1
from Products
where ProductId IN (1, 10, 100)
Здесь будет возвращена пустая строка или ее отсутствие (если строки не существует).
Или даже:
select case when EXISTS (
select 1
from Products
where ProductId IN (1, 10, 100)
) then 1 else 0 end as [ProductExists]
Здесь всегда будет возвращаться скалярное значение 1 или 0 (если строка не существует).
@Mark Hurd, спасибо, что указали на ошибку.
это будет работать (если вы используете Postgresql, Sql Server 2008):
create table products
(
product_id int not null
);
insert into products values(1),(2),(10),(100);
SELECT
CASE
WHEN EXISTS(
SELECT 1
FROM (values(1),(10),(100)) as x(id)
WHERE x.id NOT IN (select product_id from products))
THEN 0 --'NOT ALL'
ELSE 1 -- 'ALL'
END
Если вы используете MySQL, создайте временную таблицу памяти (затем заполните ее 1,10,100):
create table product_memory(product_id int) engine=MEMORY;
insert into product_memory values(1),(10),(100);
SELECT
CASE
WHEN EXISTS(
SELECT 1
FROM product_memory
WHERE product_memory.id NOT IN (select product_id from products))
THEN 0 -- 'NOT ALL'
ELSE 1 -- 'ALL'
END
В вашем коде C #:
bool isAllExist = (int)(new SqlCommand(queryHere).ExecuteScalar()) == 1;
[РЕДАКТИРОВАТЬ]
Как написать запрос, который будет вернуть строку, если все идентификаторы продукта существует и нет строки, если один или несколько Идентификатор продукта не существует?
Что касается возврата строки (единственного числа), если все строки существуют, и ни одной строки , которая должна быть возвращена, если один или несколько идентификаторов продукта не существует:
MySql:
SELECT 1
WHERE
NOT EXISTS(
SELECT 1
FROM product_memory
WHERE product_memory.id NOT IN (select product_id from products) )
Posgresql, Sql Server 2008:
SELECT 1
WHERE
NOT EXISTS(
SELECT 1
FROM (values(1),(10),(100)) as x(id)
WHERE x.id NOT IN (select product_id from products) )
Затем в вашем коде C #:
var da = new SqlDataAdapter(queryhere, connectionhere);
var dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
return true;
else
return false;
Или просто сделайте условие короче:
return dt.Rows.Count > 0;