当表具有键，值对时，如何在SQL查询中使用IN [英] How to use IN in sql query when table has key , values pairs

问题描述

我正在处理具有键值对的查询

学生

  StdId StuName phnNum 
 1约翰87678 
  

student_meta_data

  S.NO field_name field_value StdId 
 1大学St.Anns 1 
 2地址Arizona 1 
 3 IdNum 321 1 
 4学科数学1 
 5分数90 1 
 6学科物理1 
 7分数80 1 
  

我想从student_meta_data表中获取数据，为此，我编写了如下查询，

 当student_meta_data.field_name ='Subject'时选择
情况，当student_meta_data.field_name ='Marks'时field_value结束为主体.field_na me ='IdNum'，然后field_value结束，为student_meta_data 
 
中的idNum 
，其中student_meta_data.StdId = 1 
& （'Subject'）
  

中的student_meta_data.field_name对于以上查询，我正在获取以下记录，

 主题标记IdNum 
 null null null null 
  

我希望获取如下记录，

 主题标记IdNum 
数学90321 
物理学80321 
  

您能在其中提出建议吗？

解决方案

I am working on a query which is having key value pairs

student

StdId StuName   phnNum 
1     John       87678

student_meta_data

S.NO field_name   field_value     StdId  
1    college       St.Anns         1       
2    Address      Arizona          1       
3    IdNum         321             1       
4    Subject         Maths         1        
5    Marks          90             1
6   Subject        Physics         1
7   Marks          80              1

I would like to fetch data from student_meta_data table, for this I had written query like the below,

select 
   case when student_meta_data.field_name = 'Subject' Then field_value end as subject
   case when student_meta_data.field_name ='Marks' Then field_value end as marks
   case when student_meta_data.field_name = 'IdNum' Then field_value end as IdNum
 from student_meta_data

 where student_meta_data.StdId=1
       && student_meta_data.field_name in ('Subject')

for the above query I am fetching records like the below,

subject   marks     IdNum
null      null      null

I am expecting to fetch records like below,

subject   marks     IdNum    
Maths      90        321       
Physics    80        321       

can you one suggest in this. Thanks in advance.

解决方案

SQL DEMO

SELECT rn, 
       MAX(subject) as subject, 
       MAX(case when field_name = 'Marks' Then field_value end) as marks,
       MAX(idNum) as idNum

FROM ( SELECT m.*,
              @idNum := if(`field_name` = 'IdNum', `field_value`, @idNum) as idNum,
              @subject := if(`field_name` = 'Subject', `field_value`, @subject) as subject,
              @rn := if (@s = @subject, 
                         @rn,
                         if(@s := @subject, @rn+1, @rn+1)
                        ) as rn
       FROM student_meta_data m
       CROSS JOIN (SELECT @idNum := 0, @rn := 0, @subject := '', @s := '' ) as var
       ORDER BY `SNO` ) as T
WHERE rn > 0       
GROUP BY rn;  

OUTPUT

Using variable to track idNum and creating the groups for each subject. First query is just the inner subquery for debug propose, the final is your desire result

这篇关于当表具有键，值对时，如何在SQL查询中使用IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！