从mysql中的sigle表中获取列作为行 [英] get the columns as rows from a sigle table in mysql

问题描述

hai all



i有一张桌子像

 sid名称科目出勤
 1 jhon sub1 1 
 2 toni sub1 0 
 3 danial sub1 1 
 4 jafer sub1 1 
 5 jhon sub2 1 
 6 toni sub2 1 
 7 danial sub2 1 
 8 jafer sub2 0 

这里主题名称是动态的我们在写入查询之前不知道主题名称我需要输出像

<前lang =HTML>名称sub1 sub2
jhon 1 1
toni 0 1
danial 1 1
jafer 1 0

我无法写询问请帮帮我

提前感谢..

解决方案

尝试使用group by和case语句

 选择名称，SUM（ CASE   WHEN  subject = '  sub1 ' 和 attendance =  1  那么  1   ELSE   0   END ） as  sub1，
 SUM（ CASE   WHEN  subject = '  sub2' 和 attendance =  1  那么  1   ELSE   0   END ） as  sub2 
 来自表1 
  group   by  name 

hai all

i have one table like

sid	name	subject	attendance
1	jhon	sub1	1
2	toni	sub1	0
3	danial	sub1	1
4	jafer	sub1	1
5	jhon	sub2	1
6	toni	sub2	1
7	danial	sub2	1
8	jafer	sub2	0

here subject names are dynamic we dont know about subject name before writing query i need output like

name	sub1	sub2
jhon	1	    1
toni	0	    1
danial	1	    1
jafer	1	    0

am unable to write query please help me
thanks in advance..

解决方案

try with group by and case statement

select name, SUM(CASE WHEN subject = 'sub1' and  attendance = 1 THEN 1 ELSE 0 END) as sub1,
 SUM(CASE WHEN subject = 'sub2' and  attendance = 1 THEN 1 ELSE 0 END) as sub2
from Table1
group by name

这篇关于从mysql中的sigle表中获取列作为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！