检查表中记录的组合

发布于 2024-08-03 05:59:38 字数 145 浏览 11 评论 0原文

我有两个值数组，例如 X、Y、Z 和 1,2 有一个包含两列的表 A。我想验证表 A 中包含所有组合的记录是否存在，无论是否重复。例如，

X 1

Y 1

Z 1

X 2

Y 2

Z 2

提前致谢！

原文

I have two arrays of values like X,Y,Z and 1,2
There is a table A with two columns.I want to validate that in table A records with all the combination exists irrespective of duplicates.
e.g.

X 1

Y 1

Z 1

X 2

Y 2

Z 2

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

山有枢 2024-08-10 05:59:38

无论值如何，以下操作都应该有效：

select col1, col2
from (select distinct col1 from combtest), (select distinct col2 from combtest)
minus
select col1, col2
from combtest

首先获取可能的组合，然后减去实际的组合。

The following should work no matter the values:

select col1, col2
from (select distinct col1 from combtest), (select distinct col2 from combtest)
minus
select col1, col2
from combtest

First it gets the possible combinations then subtracts the actual combinations.

回复收藏 0 原文

最初的梦 2024-08-10 05:59:38

这适用于任何数据集，并且不假设您知道表中的值。

该查询返回所有缺失的行。您可以轻松地将其转换为插入语句来填充您的表。

SELECT *
FROM
(select * from (SELECT DISTINCT col1 FROM table1) CROSS JOIN (SELECT DISTINCT col2 FROM table1)) AS t1
LEFT OUTER JOIN table1 ON t1.col1 = table1.col1 AND t1.col2 = table1.col2
WHERE
table1.col1 is null

This will work with any data set and doesn't assume you know the values that will be in the table.

The query returns all the rows that are missing. You can easily turn this into an insert statement to populate your table.

SELECT *
FROM
(select * from (SELECT DISTINCT col1 FROM table1) CROSS JOIN (SELECT DISTINCT col2 FROM table1)) AS t1
LEFT OUTER JOIN table1 ON t1.col1 = table1.col1 AND t1.col2 = table1.col2
WHERE
table1.col1 is null

回复收藏 0 原文

~没有更多了~