SQL 如何获取sql中的缺失值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4076968/
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
How to get missing values in sql?
提问by eman
I need help.
我需要帮助。
I have a sql table t2 related to two other tables t1 and t3.
我有一个与另外两个表 t1 和 t3 相关的 sql 表 t2。
t2 has fields:
t2 有字段：
idFromt3 idFromt1 Value
1 14 text1
2 14 text2
1 44 text1
2 44 text2
3 44 text3
I'm searching for values, where ifFromt3 is missing. I want to fint in this example, the value ifFromt3 = 3, because of it's not present.
我正在寻找缺少 ifFromt3 的值。我想在这个例子中找到值 ifFromt3 = 3，因为它不存在。
I'm doing it like this example, but it doesn't work correctly.
我正在像这个例子那样做，但它不能正常工作。
SELECT t3.idFromt3, t3.idFromt1
FROM t3
INNER JOIN t2
ON t3.LanguageMessageCodeID <> t2.idFromt2
This are the 3 tables.
这是3张桌子。
CREATE TABLE [dbo].[t3](
[t3ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
)
CREATE TABLE [dbo].[t2](
[t2ID] [int] IDENTITY(1,1) NOT NULL,
[t3ID] [int] NOT NULL,
[t1ID] [int] NOT NULL,
)
CREATE TABLE [dbo].[t1](
[t1ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
)
UPDATE:
更新：
Tables with data: http://www.2shared.com/photo/40yY6FC-/Untitled.html
数据表：http: //www.2shared.com/photo/40yY6FC-/Untitled.html
And I need a query, that returns all missing combinations in table LangugageMessageCodes.
我需要一个查询，它返回表LangugageMessageCodes 中所有缺失的组合。
In this case:
在这种情况下：
LanguageMessageCodeID LanguageID
3 14
1 47
2 47
3 47
please. help.
请。帮助。
regards.
问候。
回答by Oded
You need to use a LEFT OUTER JOINs if you want to find rows that do notexist in the join table.
LEFT OUTER JOIN如果要查找连接表中不存在的行，则需要使用s 。
An outer join will result in all rows of the left table, whether there is a match on the right one or not.
外连接将导致左表的所有行，无论右表是否存在匹配项。
In order to filter by those that do not exist, you can add a WHEREclause checking for NULLvalues for the right table.
为了过滤不存在的那些，您可以添加一个WHERE子句检查NULL正确表的值。
SELECT t3.idFromt3, t3.idFromt1
FROM t3
LEFT OUTER JOIN t2
ON t3.LanguageMessageCodeID <> t2.idFromt2
WHERE t2.idFromts IS NULL
回答by Quassnoi
SELECT *
FROM t2
WHERE t2.idFromt3 NOT IN
(
SELECT LanguageMessageCodeID
FROM t3
)
or
或者
SELECT *
FROM t2
WHERE NOT EXISTS
(
SELECT NULL
FROM t3
WHERE t3.LanguageMessageCodeID = t2.id
)
or
或者
SELECT t2.*
FROM t2
LEFT JOIN
t3
ON t3.LanguageMessageCodeID = t2.id
WHERE t3.LanguageMessageCodeID IS NULL
Update:
更新：
Try this:
尝试这个：
SET NOCOUNT ON
DECLARE @t1 TABLE (id INT NOT NULL PRIMARY KEY)
DECLARE @t2 TABLE (t3id INT NOT NULL, t1id INT NOT NULL, PRIMARY KEY (t1id, t3id))
DECLARE @t3 TABLE (id INT NOT NULL)
INSERT
INTO @t1
VALUES (14)
INSERT
INTO @t1
VALUES (44)
INSERT
INTO @t2
VALUES (1, 14)
INSERT
INTO @t2
VALUES (2, 14)
INSERT
INTO @t2
VALUES (1, 44)
INSERT
INTO @t2
VALUES (2, 44)
INSERT
INTO @t2
VALUES (3, 44)
INSERT
INTO @t3
VALUES (1)
INSERT
INTO @t3
VALUES (2)
INSERT
INTO @t3
VALUES (3)
SELECT t1.id, t3.id
FROM @t1 t1
CROSS JOIN
@t3 t3
WHERE NOT EXISTS
(
SELECT NULL
FROM @t2 t2
WHERE t2.t1id = t1.id
AND t2.t3id = t3.id
)
回答by Gozzy
Solved this issue by going this approach:
通过采用这种方法解决了这个问题：
WITH Nums
AS
(
SELECT
1 AS Number
UNION ALL
SELECT
Number + 1
FROM
Nums
WHERE
Number < 99 --- Max number for Transactions it will count up to
), AccountLog (Account, TransNumber, NextTransNumber)
AS
(
SELECT --- Let's Generate some Sample Data
Account, --Pretend an Account with Transactions
TransNumber, --- Transaction Numbers which should be in sequence
LEAD(TransNumber,1,0) OVER (PARTITION BY Account ORDER BY TransNumber) NextCheckNumber
FROM (VALUES(1,2),
(100,1),---Pretend first column is the Account and then the Transaction Number (Which was Meant to be in Sequence)
(100,3),---As you can see we're missing numbers in sequence (1,3,7 and not 1,2,3,4,5,6,7)
(100,7),
(100,10),
(200,1),
(200,3),
(200,11),
(200,15)) a(Account,TransNumber)
),
MissingTrans (Account,TransNumber,TransDifference)
AS(
SELECT
Account,
TransNumber,
NextTransNumber-TransNumber as TransDifference
FROM AccountLog
WHERE
NextTransNumber<>0
AND
NextTransNumber-TransNumber<>1
)
SELECT
Account,
TransNumber+al.Number AS MissingTransNumber
FROM MissingTrans mt
CROSS JOIN Nums al
WHERE
al.Number<mt.TransDifference
AND
mt.TransNumber+al.Number>1
