在查询中组合记录 [英] Combining Records in a query

问题描述

我必须编写一个在两行之间搜索的查询，但只会匹配两行中的一个值.我要搜索的数据是一个包含名字和姓氏的表是单独的行.不要告诉我应该将它们存储在同一行中.我不能，这就是所有需要说的.我需要做的是有一个组合记录的查询，以便我可以在不同的列中搜索它们.

I have to write a query that searches between 2 rows, but will only match a value in both rows. The data that I am search is a table that holds the first name and last name is separate rows. Don't tell me that I should store them in the same row. I can't and that's all that needs to be said. What I need to do is have a query that combines the records so that I can search for them in separate columns.

数据格式如下:

StudentValues
    ID
    StudentID
    FieldID
    Response

fieldid 为 1 给我响应列中的名字，fieldid 为 2 给我响应列中的姓氏，StudentID 将用于对记录进行分组.

A fieldid of 1 gives me the first name in the response column and fieldid of 2 gives me the last name in the response column and the StudentID would be used to group the records.

我已经能够构建以下 SQL，但 where 子句现在失败

I have been able to build the following SQL but the where clauses now fail

    SELECT StudentId, MIN(CASE WHEN FieldId = 1 THEN Response ELSE NULL END) AS fname, MIN(CASE WHEN FieldId = 2 THEN Response ELSE NULL END) AS lname
    FROM sms_studentvalues
    WHERE fname = 'Some Value'
      AND lname = 'Some Value'
    GROUP BY studentid

推荐答案

更新以下评论

SELECT *
FROM (
    SELECT  StudentId,
            MIN(CASE WHEN FieldId = 1 THEN Response ELSE NULL END) AS Name, 
            MIN(CASE WHEN FieldId = 2 THEN Response ELSE NULL END) AS LastName
    FROM YourTable
    GROUP BY StudentId) AS StudentsNames
WHERE Name = 'Some Value' AND LastName = 'Some Value'

这篇关于在查询中组合记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！