如何在 DB2 中选择不同列和一个非不同列？

发布于 2024-11-27 13:55:57 字数 342 浏览 2 评论 0原文

我需要对几列执行不同的选择，其中一列是非不同的。我可以在 SQL 语句中指定哪些列构成不同的组吗？

目前我正在做这个。

Select distinct a,b,c,d from TABLE_1 inner join TABLE_2 on TABLE_1.a = TABLE_2.a where TABLE_2.d IS NOT NULL;

我遇到的问题是上面的 SQL 得到了 2 行，因为 D 列包含不同的值。如何形成一组不同的列（a、b 和 c），忽略 d 列，但在我的 select 子句中也有 d 列？

仅供参考：我正在使用 DB2

谢谢桑迪普

原文

I need to perform distinct select on few columns out of which, one column is non-distinct. Can I specify which columns make up the distinct group in my SQL statement.

Currently I am doing this.

Select distinct a,b,c,d from TABLE_1 inner join TABLE_2 on TABLE_1.a = TABLE_2.a where TABLE_2.d IS NOT NULL;

The problem I have is I am getting 2 rows for the above SQL because column D holds different values. How can I form a distinct group of columns (a,b&c) ignoring column d, but have column d in my select clause as well?

FYI: I am using DB2

Thanks
Sandeep

分享到QQ

分享到微博

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

发布评论

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

假装爱人 2024-12-04 13:55:57

SELECT a,b,c,MAX(d) 
FROM table_1 
INNER JOIN table_2 ON table_1.a = table_2.a 
GROUP BY a,b,c

SELECT a,b,c,MAX(d) 
FROM table_1 
INNER JOIN table_2 ON table_1.a = table_2.a 
GROUP BY a,b,c

回复收藏 0 原文

背叛残局 2024-12-04 13:55:57

嗯，你的问题，即使经过改进，仍然很笼统。所以，你会得到一个一般性的答案。

如果不了解更多有关表结构或所需结果的信息，可能无法给出有意义的答案，但这里是这样的：

SELECT a, b, c, d
FROM table_1 as t1
JOIN table_2 as t2
ON t2.a = t1.a
AND t2.[some_timestamp_column] = (SELECT MAX(t3.[some_timestamp_column])
                                  FROM table_2 as t3
                                  WHERE t3.a = t2.a)

这假设 table_1 填充了要检索的单行，并且该行 - table_1 和 table_2 之间的一对多关系是由于 d 的值不同而创建的，并填充在唯一的 [some_timestamp_column] 次。如果是这种情况，它将获取与 table_1 匹配的最新 table_2 记录。

Well, your question, even with refinements, is still pretty general. So, you get a general answer.

Without knowing more about your table structure or your desired results, it may be impossible to give a meaningful answer, but here goes:

SELECT a, b, c, d
FROM table_1 as t1
JOIN table_2 as t2
ON t2.a = t1.a
AND t2.[some_timestamp_column] = (SELECT MAX(t3.[some_timestamp_column])
                                  FROM table_2 as t3
                                  WHERE t3.a = t2.a)

This assumes that table_1 is populated with single rows to retrieve, and that the one-to-many relationship between table_1 and table_2 is created because of different values of d, populated at unique [some_timestamp_column] times. If this is the case, it will get the most-recent table_2 record that matches to table_1.

回复收藏 0 原文

~没有更多了~