RDBMS - 基于另一列唯一性的列的 SQL 最大值

发布于 2024-10-10 01:14:00 字数 1567 浏览 0 评论 0原文

这确实是一个很难问的问题。所以就这样吧。以下 sql：

select * from Survey_Answers 
where QuestionID='50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
order by MaxRating

生成以下内容：

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anyplace       0     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

(6 row(s) affected)

在 MaxRating 列中有两个“2”。我想要这两列的最大值。因此，例如，如果有三个 1、两个 2 和五个 4，我希望“值”列的最大值基于不同的最大评级列。

我希望这是有意义的，所以结果应该是：

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

9 是基于数据为“2”的 MaxRating 列的“Value”列的最大值

这很难解释，所以我希望有人理解这一点。任何帮助将不胜感激。任何重新措辞的帮助都会很棒。谢谢。

原文

This is really difficult question to ask. So here goes. The following sql:

select * from Survey_Answers 
where QuestionID='50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
order by MaxRating

Generates the following:

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anyplace       0     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

(6 row(s) affected)

In the MaxRating Column there are two "2"s. I would like the max value of those two columns. So for instance if there was three 1s and two 2s and five 4s, I would like the max value of the "value" column based upon distinct maxrating columns.

I hope this make sense so instead of the above the result should be:

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

The 9 is the max of column "Value" based upon the Maxrating column with data of "2"

This is so difficult to explain, so I hope someone understands this. Any help is gladly appreciated. Any help in re-wording this would be great. Thanks.

分享到QQ

分享到微博

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

发布评论

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

和我恋爱吧 2024-10-17 01:14:00

像这样的事情：

select *
from Survey_Answers sa1
where sa1.QuestionID = '50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
and sa1.value = (select max(value) 
                 from Survey_Answers sa2
                where sa2.questionid = sa1.QuestionID
                  and sa2.maxrating = sa1.maxrating)
order by MaxRating

Something like this:

select *
from Survey_Answers sa1
where sa1.QuestionID = '50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
and sa1.value = (select max(value) 
                 from Survey_Answers sa2
                where sa2.questionid = sa1.QuestionID
                  and sa2.maxrating = sa1.maxrating)
order by MaxRating

回复收藏 0 原文

~没有更多了~