另一种方法:--建立测试环境 Create Table tb1 (xinm Nvarchar(10), xiangm Nvarchar(10), xiangmValue Int) --插入数据 Insert tb1 Values(N'名1', N'岗位工资', 200) Insert tb1 Values(N'名1', N'基本工资', 1000) Insert tb1 Values(N'名2', N'岗位工资', 300) Insert tb1 Values(N'名2', N'基本工资', 2000) --测试 Select DIstinct xinm, (Select xiangmValue from tb1 Where xinm=A.xinm And xiangm=N'岗位工资') As 岗位工资, (Select xiangmValue from tb1 Where xinm=A.xinm And xiangm=N'基本工资') As 基本工资 from tb1 A --删除测试环境 Drop table tb1 --结果 /* xinm 岗位工资 基本工资 名1 200 1000 名2 300 2000 */
select xinm,sum(case xiangm when '岗位工资' then xiangmValue else 0 end ) as "岗位工资", sum(case xiangm when '基本工资' then xiangmValue else 0 end ) as "岗位工资" from tb1 group by xinm
select a.xinm as xm, a.xiangmValue as '岗位工资', b.xiangmValue, as '基本工资' from (select xinm, xiangmValue where xiangm = '岗位工资') a (select xinm, xiangmValue where xiangm = '基本工资') b where a.xinm = b.xinm
抱歉,刚才的语句写错了。select a.xinm as xm, a.xiangmValue as '岗位工资', b.xiangmValue, as '基本工资' from (select xinm, xiangmValue from tbl where xiangm = '岗位工资') a (select xinm, xiangmValue from tbl where xiangm = '基本工资') b where a.xinm = b.xinm
select * from tab1 where tab1.xianggm = '岗位工资' or tab1.xiangm = ‘基本工资‘
1.为什么会有如此的表设计出现?为什么不设计成要select的结构 2. select a.xinm ,a.xiangmValue as 基本工资,b.xiangmValue as 岗位工资 from salary a inner join salary b on a.xinm=b.xinm where a.xiangmValue>b.xiangmValue
select distinct(a.xinm), 基本工资=(select xiangmValue from tb1 where xinm=a.xinm and xiangm='基本工资'), 岗位工资=(select xiangmValue from tb1 where xinm=a.xm and xiangm='岗位工资') from tb1 a
Create Table tb1
(xinm Nvarchar(10),
xiangm Nvarchar(10),
xiangmValue Int)
--插入数据
Insert tb1 Values(N'名1', N'岗位工资', 200)
Insert tb1 Values(N'名1', N'基本工资', 1000)
Insert tb1 Values(N'名2', N'岗位工资', 300)
Insert tb1 Values(N'名2', N'基本工资', 2000)
--测试
Select DIstinct
xinm,
(Select xiangmValue from tb1 Where xinm=A.xinm And xiangm=N'岗位工资') As 岗位工资,
(Select xiangmValue from tb1 Where xinm=A.xinm And xiangm=N'基本工资') As 基本工资
from tb1 A
--删除测试环境
Drop table tb1
--结果
/*
xinm 岗位工资 基本工资
名1 200 1000
名2 300 2000
*/
sum(case xiangm when '基本工资' then xiangmValue else 0 end ) as "岗位工资"
from tb1
group by xinm
from (select xinm, xiangmValue where xiangm = '岗位工资') a
(select xinm, xiangmValue where xiangm = '基本工资') b
where a.xinm = b.xinm
from (select xinm, xiangmValue from tbl where xiangm = '岗位工资') a
(select xinm, xiangmValue from tbl where xiangm = '基本工资') b
where a.xinm = b.xinm
from tab1
where tab1.xianggm = '岗位工资' or tab1.xiangm = ‘基本工资‘
2.
select a.xinm ,a.xiangmValue as 基本工资,b.xiangmValue as 岗位工资 from salary a inner join salary b on a.xinm=b.xinm
where a.xiangmValue>b.xiangmValue
基本工资=(select xiangmValue from tb1 where xinm=a.xinm and xiangm='基本工资'),
岗位工资=(select xiangmValue from tb1 where xinm=a.xm and xiangm='岗位工资') from tb1 a