MYSQL-从多行，同一用户，不同值中进行选择 [英] MYSQL - SELECT from multiple rows, same user, different values

问题描述

下面是我的表格，称为fittest.我需要找到基于学生ID(研究)的哪些学生接受了prepost列中指定的pre和post测试.因此，根据下面的简单表，我需要返回学习的123456.如何为此编写SELECT查询?

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

    studid  prepost
    123456  pre
    123456  post
    1031460 pre

解决方案

尝试

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

或者如果您要确保仅具有pre一行且具有post一行的ID，则可以这样执行

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

输出:

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

这是两个查询的 SQLFiddle 演示

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?

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

    studid  prepost
    123456  pre
    123456  post
    1031460 pre

解决方案

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

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

这篇关于MYSQL-从多行，同一用户，不同值中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！