列传行sql
来源:互联网 发布:js单例模式的应用场景 编辑:程序博客网 时间:2024/04/30 12:31
1 子查询方法
select u.userid ,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=1) as jbr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=2) as fhr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=3) as shr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=4) as yjsc,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=5) as yjfp
from
table(multiset(
select userid from view_user_juese group by userid
)) u
2 连接方法
select u.userid,v1.jueseid,v2.jueseid,v3.jueseid,v4.jueseid,v5.jueseid
from
table(multiset(
select userid,'' from view_user_juese group by userid
)) u
left join view_user_juese v1 on v1.userid=u.userid and v1.jueseid=1
left join view_user_juese v2 on v2.userid=u.userid and v2.jueseid=2
left join view_user_juese v3 on v3.userid=u.userid and v3.jueseid=3
left join view_user_juese v4 on v4.userid=u.userid and v4.jueseid=4
left join view_user_juese v5 on v5.userid=u.userid and v5.jueseid=5
3 case when
select userid,
max(case when jueseid=='1' then '1'
else '0' end ) jb ,
max(case
when jueseid=='2' then '1'
else '0' end) sh ,
max(case
when jueseid=='3' then '1'
else '0' end) fh ,
max(case
when jueseid=='4' then '1'
else '0' end) yjfs ,
max(case
when jueseid=='5' then '1' --where userid='c3'
else '0' end) yjsc 已创建
from view_user_juese group by userid
一个人可能有多个角色列转行:
查询出结果如:
userid jb fh sh fs sc
1 1 2 4
2 1 3
3 1 4
4 2
select u.userid ,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=1) as jbr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=2) as fhr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=3) as shr,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=4) as yjsc,
(select jueseid from view_user_juese v1 where v1.userid=u.userid and jueseid=5) as yjfp
from
table(multiset(
select userid from view_user_juese group by userid
)) u
2 连接方法
select u.userid,v1.jueseid,v2.jueseid,v3.jueseid,v4.jueseid,v5.jueseid
from
table(multiset(
select userid,'' from view_user_juese group by userid
)) u
left join view_user_juese v1 on v1.userid=u.userid and v1.jueseid=1
left join view_user_juese v2 on v2.userid=u.userid and v2.jueseid=2
left join view_user_juese v3 on v3.userid=u.userid and v3.jueseid=3
left join view_user_juese v4 on v4.userid=u.userid and v4.jueseid=4
left join view_user_juese v5 on v5.userid=u.userid and v5.jueseid=5
3 case when
select userid,
max(case when jueseid=='1' then '1'
else '0' end ) jb ,
max(case
when jueseid=='2' then '1'
else '0' end) sh ,
max(case
when jueseid=='3' then '1'
else '0' end) fh ,
max(case
when jueseid=='4' then '1'
else '0' end) yjfs ,
max(case
when jueseid=='5' then '1' --where userid='c3'
else '0' end) yjsc 已创建
from view_user_juese group by userid
一个人可能有多个角色列转行:
查询出结果如:
userid jb fh sh fs sc
1 1 2 4
2 1 3
3 1 4
4 2
0 0
- 列传行sql
- SQL列传行与行转列
- SqlServer列传行+动态sql
- oracle多列传行
- 数据表的列传行操作
- oracle 列传行 sys.stragg
- mysql行转列和列传行
- pig 次数统计和列传行
- mysql 列传行 行转列 个人总结
- 人物列传
- Oceanbase列传
- Oceanbase列传
- 牛逼哥列传
- 《爆音列传》节选
- 大学老师列传
- 《史记.饭岛爱列传》
- 史记·陈冠希列传
- 史记·凤姐列传
- QTP10-插件破解脚本
- Win7(64) + eclipse(64)+ tomcat7(64)+ jdk7(64)开发环境配置
- js跳转页面方法
- JSP内置九大对象
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- 列传行sql
- 测试PVAdder
- (Object-C)协议和代理
- 实模式和保护模式区别及寻址方式
- 2013年全球IT公司市值排行榜
- 失落的C语言结构体封装艺术
- java泛型的理解
- linux中踢除掉其他终端
- ListView如何加载网络图片,防止内存溢出