MYSQL - 从多行,相同用户,不同值中选择

时间:2021-11-18 01:37:45

Below is my table called fittest. I need to find which students based on student id (studid) have taken the pre and post test as designated in the prepost column. So based on the simple table below I would need to return studid 123456. How do I write the SELECT query for this?

下面是我的名为fittest的表格。我需要找到哪些学生根据学生ID(studid)进行了前置栏中指定的前后测试。因此,基于下面的简单表格,我需要返回studid 123456.如何为此编写SELECT查询?

SQL query: SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ; 

    studid  prepost
    123456  pre
    123456  post
    1031460 pre

9 个解决方案

#1


3  

Try

尝试

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2

or if you want to ensure that only ids that have exactly one row with pre and one row with post then you can enforce it like this

或者如果你想确保只有一行只有一行有pre和一行有post,那么你可以像这样执行它

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING (MAX(prepost = 'pre' ) + 
        MAX(prepost = 'post')) = 2
   AND COUNT(DISTINCT prepost) = 2

Output:

输出:

| STUDID |
----------
| 123456 |

Here is SQLFiddle demo for both queries

这是两个查询的SQLFiddle演示

#2


4  

What about a JOIN:

加入怎么样:

select s1.studid, s1.prepost, s2.prepost
from fittest s1
inner join fittest s2
    on s1.studid = s2.studid
where s1.prepost = 'pre' 
  and s2.prepost = 'post'

#3


0  

Depending on your real Primary Key, you use the following:

根据您的真实主键,您可以使用以下内容:

SELECT studid
FROM fittest
WHERE prepost = 'pre' OR prepost = 'post'
GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2

#4


0  

SELECT t1.studid
FROM fittest t1
INNER JOIN t2 ON t1.studid=t2.studid
WHERE t1.prepost = 'pre'
AND t2.prepost='post'

#5


0  

If Only PRE and POST are possible in this column then try

如果此列中只有PRE和POST,请尝试

SELECT studid FROM fittest group by studid
HAVING Min(prepost)<>max(prepost)

#6


0  

SELECT 
studid 
FROM `fittest` 
where prepost = 'pre' 
or prepost = 'post'
group by studid
having count(distinct prepost)=2

SQL Fiddle with Extra records.

SQL小提琴额外记录。

#7


0  

try:

尝试:

    select f1.studid
       from fittest f1
       inner join fittest f2
         on f1.studid = f2.studid
    where f1.prepost = 'pre' and f2.prepost = 'post'

here's the SQL Fiddle

这是SQL小提琴

#8


0  

You could use JOIN

你可以使用JOIN

SELECT a.studid 
FROM fittest a 
JOIN fittest b 
    ON a.studid=b.studid 
        and a.prepost like 'pre' 
        and b.prepost like 'post'

#9


-1  

Select .... where studid in (123456,123457,...)

选择.... where studid in(123456,123457,...)

#1


3  

Try

尝试

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2

or if you want to ensure that only ids that have exactly one row with pre and one row with post then you can enforce it like this

或者如果你想确保只有一行只有一行有pre和一行有post,那么你可以像这样执行它

SELECT studid
  FROM fittest
 GROUP BY studid
HAVING (MAX(prepost = 'pre' ) + 
        MAX(prepost = 'post')) = 2
   AND COUNT(DISTINCT prepost) = 2

Output:

输出:

| STUDID |
----------
| 123456 |

Here is SQLFiddle demo for both queries

这是两个查询的SQLFiddle演示

#2


4  

What about a JOIN:

加入怎么样:

select s1.studid, s1.prepost, s2.prepost
from fittest s1
inner join fittest s2
    on s1.studid = s2.studid
where s1.prepost = 'pre' 
  and s2.prepost = 'post'

#3


0  

Depending on your real Primary Key, you use the following:

根据您的真实主键,您可以使用以下内容:

SELECT studid
FROM fittest
WHERE prepost = 'pre' OR prepost = 'post'
GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2

#4


0  

SELECT t1.studid
FROM fittest t1
INNER JOIN t2 ON t1.studid=t2.studid
WHERE t1.prepost = 'pre'
AND t2.prepost='post'

#5


0  

If Only PRE and POST are possible in this column then try

如果此列中只有PRE和POST,请尝试

SELECT studid FROM fittest group by studid
HAVING Min(prepost)<>max(prepost)

#6


0  

SELECT 
studid 
FROM `fittest` 
where prepost = 'pre' 
or prepost = 'post'
group by studid
having count(distinct prepost)=2

SQL Fiddle with Extra records.

SQL小提琴额外记录。

#7


0  

try:

尝试:

    select f1.studid
       from fittest f1
       inner join fittest f2
         on f1.studid = f2.studid
    where f1.prepost = 'pre' and f2.prepost = 'post'

here's the SQL Fiddle

这是SQL小提琴

#8


0  

You could use JOIN

你可以使用JOIN

SELECT a.studid 
FROM fittest a 
JOIN fittest b 
    ON a.studid=b.studid 
        and a.prepost like 'pre' 
        and b.prepost like 'post'

#9


-1  

Select .... where studid in (123456,123457,...)

选择.... where studid in(123456,123457,...)