Sql：我在从表中获取正确的数据时遇到问题 [英] Sql: I have problems with getting the right data out my table

问题描述

这是我的问题：

我想从桌面上找到，有多少参与者赞成1-8的努力。

他们将不同的努力排在1之间到3.

梦想场景是我可以得出结论a和c喜欢努力2但不同的等级。

SQL应该给出类似的东西：

A 2 1 C 2 2





排名A B C D

1 2 8 5 1

2 3 7 2 3

3 7 3 8 7



我尝试过：



我怎么试试我得到相同的结果。总是排名第一的排名超过其他排名。因此，如果A在第1位表示努力2，则B表示努力2也在第1位。

This is my problem:
I would like to find out from at table, how many participants favoring effort 1-8.
They rank the different efforts between 1 to 3.
Dream scenario is that i can conclude that a and c like the effort 2 but different rank.
SQL should give something like:
A 2 1 C 2 2


Rank A B C D
1 2 8 5 1
2 3 7 2 3
3 7 3 8 7

What I have tried:

How ever I try I get the same result. Always the first rank that overrules the other rank. So if A says effort 2 on place 1, B says effort 2 also on place 1.

推荐答案

我认为问题源于您的数据结构。存储它的标准化方式可能在这样的表中：

Hi,

I think the problem stems from the structure of your data. A normalised way of storing it might be in a table such as this:

CREATE TABLE #Efforts
(
	Participant nvarchar(15),
	Rank int,
	Effort int
)

您提供的数据如下所示：

The data you present would look like this:

Participant	Rank	Effort
A	1	2
A	2	3
A	3	7
B	1	8
B	2	7
B	3	3
C	1	5
C	2	2
C	3	8
D	1	1
D	2	3
D	3	7

然后你的查询变得微不足道了：

And then your query becomes trivial:

SELECT
	*
FROM
	#Efforts 
WHERE
	Effort = 2

给出

which gives

Participant	Rank	Effort
   A	      1	      2
   C	      2	      2

希望我我理解你的问题，这个解决方案有帮助。



Jon

Hope I've understood your problem and that this solution helps.

Jon

这篇关于Sql：我在从表中获取正确的数据时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！