select a.devicesn,a.devicetemp,a.devicesd,a.devicezd,b.devicec02
from 表名 a left join 表名 b on substr(a.devicesn,2,3)=substr(b.devicesn,2,3)
表名为你存储数据的表
通过case when和pivot函数均可实现行转列
--模拟数据
CREATE TABLE Test(name VARCHAR(5),Tsource VARCHAR(5),grade INT)
insert into Test VALUES ('A','语文',56)
insert into Test VALUES ('A','数学',78)
insert into Test VALUES ('A','外语',60)
insert into Test VALUES ('B','语文',74)
insert into Test VALUES ('B','数学',84)
insert into Test VALUES ('C','数学',94)
insert into Test VALUES ('C','外语',56)
go
1:case when
SELECT name,max(CASE WHEN Tsource='语文' THEN grade ELSE 0 END) as 语文,
max(CASE WHEN Tsource='数学' THEN grade ELSE 0 END )as 数学,
max(CASE WHEN Tsource='外语' THEN grade ELSE 0 END )as 外语
FROM Test GROUP BY name
2:pivot(PIVOT 的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P)
select * from Test pivot
(MAX(grade) FOR Tsource in([语文],[数学],[外语]))t
select new1.id,wm_concat(role.name) from (----新加的select new1.id, role.name from (select admin.id,admin_role.roleSet_id from admin left join admin_role on admin.id=admin_role.adminSet_id) as new1 left join role on new1.roleSet_id=role.id)
group by new1.id----新加的
wm_concat针对oracle,其他类型数据库自己切换下函数即可
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)