SQL 使用基于Count的IF ELSE语句执行不同的Insert语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14072140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using IF ELSE statement based on Count to execute different Insert statements
提问by user1934821
While I am searching through my database, I run an INSERT statement if I find that a particular item does not exist, and I run a different INSERT statement if I find one or more of this item.
当我在我的数据库中搜索时，如果我发现某个特定项目不存在，我会运行一个 INSERT 语句，如果我找到一个或多个这个项目，我会运行一个不同的 INSERT 语句。
I am not entirely sure how to use the IF ELSE expressions.
我不完全确定如何使用 IF ELSE 表达式。
What I have so far is a statement that will count the number of times the target data appears; it will print TRUE if it is greater than 0, if not, it will print FALSE. I can't find any examples to help me understand how I can use this to run two different INSERT statements.
到目前为止，我有一个语句，它会计算目标数据出现的次数；如果大于 0 则打印 TRUE，否则打印 FALSE。我找不到任何示例来帮助我理解如何使用它来运行两个不同的 INSERT 语句。
Here is what I have so far:
这是我到目前为止所拥有的：
SELECT CASE WHEN COUNT(*)>0 THEN 'TRUE' ELSE 'FALSE' END
(
SELECT [Some Column], COUNT(*) TotalCount
FROM INCIDENTS
WHERE [Some Column] = 'Target Data'
GROUP BY [Some Column]
)
回答by sgeddes
Depending on your needs, here are a couple of ways:
根据您的需求，这里有几种方法：
IF EXISTS (SELECT * FROM TABLE WHERE COLUMN = 'SOME VALUE')
--INSERT SOMETHING
ELSE
--INSERT SOMETHING ELSE
Or a bit longer
或者长一点
DECLARE @retVal int
SELECT @retVal = COUNT(*)
FROM TABLE
WHERE COLUMN = 'Some Value'
IF (@retVal > 0)
BEGIN
--INSERT SOMETHING
END
ELSE
BEGIN
--INSERT SOMETHING ELSE
END
回答by Atheer Mostafa
As long as you need to find it based on Count just more than 0, it is better to use EXISTS like this:
只要你需要根据 Count 刚好大于 0 找到它，最好像这样使用 EXISTS：
IF EXISTS (SELECT 1 FROM INCIDENTS WHERE [Some Column] = 'Target Data')
BEGIN
-- TRUE Procedure
END
ELSE BEGIN
-- FALSE Procedure
END
回答by paparazzo
IF exists
如果存在
IF exists (select * from table_1 where col1 = 'value')
BEGIN
-- one or more
insert into table_1 (col1) values ('valueB')
END
ELSE
-- zero
insert into table_1 (col1) values ('value')
回答by Katie
Simply use the following:
只需使用以下内容：
IF((SELECT count(*) FROM table)=0)
BEGIN
....
END
回答by Kaf
Not very clear what you mean by
不是很清楚你的意思
"I cant find any examples to help me understand how I can use this to run 2 different statements:"
"I cant find any examples to help me understand how I can use this to run 2 different statements:"
. Is it using CASElike a SWITCHyou are after?
. 它CASE像SWITCH你所追求的那样使用吗？
select case when totalCount >= 0 and totalCount < 11 then '0-10'
when tatalCount > 10 and totalCount < 101 then '10-100'
else '>100' end as newColumn
from (
SELECT [Some Column], COUNT(*) TotalCount
FROM INCIDENTS
WHERE [Some Column] = 'Target Data'
GROUP BY [Some Column]
) A
回答by Zdravko Danev
one obvious solution is to run 2 separate queries, first select all items that have count=1 and run your insert, then select the items with count>1 and run the second insert.
一个明显的解决方案是运行 2 个单独的查询，首先选择所有 count=1 的项目并运行插入，然后选择 count>1 的项目并运行第二次插入。
as a second step if the two inserts are similar you can probably combine them into one query.
作为第二步，如果两个插入内容相似，您可以将它们合并为一个查询。
another possibility is to use a cursor to loop thru your recordset and do whatever logic you need for each line.
另一种可能性是使用游标循环遍历您的记录集并为每一行执行您需要的任何逻辑。
回答by Ray K
There are many, many ways to code this, but here is one possible way. I'm assuming MS SQL
有很多很多方法可以对此进行编码，但这里是一种可能的方法。我假设 MS SQL
We'll start by getting row count (Another Quick Example) and then do if/else
我们将从获取行数（另一个快速示例）开始，然后执行 if/else
-- Let's get our row count and assign it to a var that will be used
-- in our if stmt
DECLARE @HasExistingRows int -- I'm assuming it can fit into an int
SELECT @HasExistingRows = Count(*)
ELSE 0 -- false
FROM
INCIDENTS
WHERE {Your Criteria}
GROUP BY {Required Grouping}
Now we can do the If / Else Logic MSDN Docs
现在我们可以做 If / Else Logic MSDN Docs
-- IF / Else / Begin / END Syntax
IF @HasExistingRows = 0 -- No Existing Rows
BEGIN
{Insert Logic for No Existing Rows}
END
ELSE -- existing rows are found
BEGIN
{Insert logic for existing rows}
END
Another faster way (inspired by Mahmoud Gamal's comment):
另一种更快的方式（灵感来自 Mahmoud Gamal 的评论）：
Forget the whole variable creation / assignment - look up "EXISTS" - MSDN Docs 2.
忘记整个变量的创建/分配 - 查找“EXISTS” - MSDN Docs 2。
IF EXISTS ({SELECT Query})
BEGIN
{INSERT Version 1}
END
ELSE
BEGIN
{INSERT version 2}
END
回答by Jeff
If this is in SQL Server, your syntax is correct; however, you need to reference the COUNT(*) as the Total Count from your nested query. This should give you what you need:
如果这是在 SQL Server 中，则您的语法是正确的；但是，您需要将 COUNT(*) 引用为嵌套查询中的 Total Count。这应该给你你所需要的：
SELECT CASE WHEN TotalCount >0 THEN 'TRUE' ELSE 'FALSE' END FROM
(
SELECT [Some Column], COUNT(*) TotalCount
FROM INCIDENTS
WHERE [Some Column] = 'Target Data'
GROUP BY [Some Column]
) DerivedTable
Using this, you could assign TotalCount to a variable and then use an IF ELSE statement to execute your INSERT statements:
使用它，您可以将 TotalCount 分配给一个变量，然后使用 IF ELSE 语句来执行您的 INSERT 语句：
DECLARE @TotalCount int
SELECT @TotalCount = TotalCount FROM
(
SELECT [Some Column], COUNT(*) TotalCount
FROM INCIDENTS
WHERE [Some Column] = 'Target Data'
GROUP BY [Some Column]
) DerivedTable
IF @TotalCount > 0
-- INSERT STATEMENT 1 GOES HERE
ELSE
-- INSERT STATEMENT 2 GOES HERE
