sql - C# SQL INSERT INTO 查询并显示结果

Question

我正在使用 VS2005 和 SQL Server 2005。

我正在尝试执行连接 3 个 sql 表的多个 INSERT INTO SQL 语句。

这 3 个表是:

• 表 1:用户 ID、用户名
• Table2:用户ID，状态
• 表 3:用户 ID、用户名、问题

以下是我需要执行的检查:

1. 存在于Table1中的用户应该存在于Table2

   中

2. 存在于 Table1 中的用户不应在 Table2

   中具有 STATUS=DELETE

3. Table2 中没有STATUS=DELETE 的用户应该存在于Table1

目前我只有一个 SELECT 语句可以满足上述 3 个查询，而无需在第 3 个表中添加:

SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid
WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL)
OR (t2.status = 'DELETE' AND t1.userid IS NOT NULL)
OR (t2.userid IS NOT NULL AND t2.status <> 'DELETE' AND t1.userid IS NULL)

对于上面的每项检查，我想使用唯一的 ISSUE 变量将结果INSERT 到 Table3 中(例如，检查号1, ISSUE=用户存在于 t1 而不是 t2)

目前我正在尝试形成一个将 3 个表连接在一起的查询，对于第一次检查发现的每个结果，t2.userid IS NULL AND t1.userid IS NOT NULL，它将在 Table3 中插入一个新行。但是我在使用 SQL 查询时遇到了一些问题。

INSERT INTO Table3 (userid, username, issue) 
VALUES (t1.userid, t1.username, 'user not found in t2')
FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid
WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL)

非常感谢您的帮助。

Answer 1

我没有这些表用于测试，所以语法将接近:

UserId 的 COALESCE 将确保您有一个值插入列中，因为您不确定该值是否存在于 Table1 或 Table2

我还使用了 CASE 语句的 where 子句来填充 Issue 列。您可以更新文本以适合您的应用。

INSERT INTO Table3(UserId, Username, Issue)
SELECT COALESCE(t1.UserId, t2.UserId), t1.UserName
   , CASE
        WHEN (t2.userid IS NULL AND t1.userid IS NOT NULL)
           THEN 'User exists in t1 but not in t2'
        WHEN (t2.status = 'DELETE' AND t1.userid IS NOT NULL)
           THEN 'User Exists in t1, but status in t2 is not DELETE'
        WHEN (t2.userid IS NOT NULL AND t2.status != 'DELETE' AND t1.userid IS NULL)
           THEN 'Non-Deleted user in t2 does not exist in t1'
     END
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid
WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL)
   OR (t2.status = 'DELETE' AND t1.userid IS NOT NULL)
   OR (t2.userid IS NOT NULL AND t2.status != 'DELETE' AND t1.userid IS NULL)