select * from (SELECT stuAnswer,subid FROM nfmsdb.studentpapermakeof spmo
inner join studentpaperinfo spi on spi.spiId =spmo.spiId
inner join nfmsdb.papermakeof pmo on pmo.ppid=spi.ppiId and pmo.subIndex=spmo.subIndex
where ppiid='402882aa3ac44686013ac44b90f70000'
and nfid='00110720001') as temp1
inner join (
SELECT id,
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 0,1)=1 then 1 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 1,1)=1 then 2 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 2,1)=1 then 4 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 3,1)=1 then 8 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 4,1)=1 then 16 else 0 end) as 'answer'
FROM nfmsdb.answer
where id='ff8080813abfd2c5013abfd3cadd03dc'
group by id
) as temp2 on temp2.id = temp1.subid;
需要把temp2 表里面的 id字符串 改为 temp1 里面的subid字段 。
求大神优化 ,或者说点思路
inner join studentpaperinfo spi on spi.spiId =spmo.spiId
inner join nfmsdb.papermakeof pmo on pmo.ppid=spi.ppiId and pmo.subIndex=spmo.subIndex
where ppiid='402882aa3ac44686013ac44b90f70000'
and nfid='00110720001') as temp1
inner join (
SELECT id,
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 0,1)=1 then 1 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 1,1)=1 then 2 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 2,1)=1 then 4 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 3,1)=1 then 8 else 0 end) +
(case when(select isRight from nfmsdb.answer where id='ff8080813abfd2c5013abfd3cadd03dc'
order by aid limit 4,1)=1 then 16 else 0 end) as 'answer'
FROM nfmsdb.answer
where id='ff8080813abfd2c5013abfd3cadd03dc'
group by id
) as temp2 on temp2.id = temp1.subid;
需要把temp2 表里面的 id字符串 改为 temp1 里面的subid字段 。
求大神优化 ,或者说点思路
具体需求为:
子查询出来的数据为
'ff8080813abfd2c5013abfd36d430009', '0'
'ff8080813abfd2c5013abfd36d430009', '1'
'ff8080813abfd2c5013abfd36d430009', '1'
'ff8080813abfd2c5013abfd36d430009', '0'
我需要将第二个字段进行数据转换
转换规则为---- 为0 则 为0
为1 则 为 2的N次幂
上面的数据转换之后应该为:
'ff8080813abfd2c5013abfd36d430009', '0'
'ff8080813abfd2c5013abfd36d430009', '2'
'ff8080813abfd2c5013abfd36d430009', '4'
'ff8080813abfd2c5013abfd36d430009', '0'
就是需要这样的需求
SELECT id as tid,
(case when(select isRight from nfmsdb.answer where id=tid order by aid limit 0,1)=1 then 1 else 0 end) +
(case when(select isRight from nfmsdb.answer where id=tid order by aid limit 1,1)=1 then 2 else 0 end) +
(case when(select isRight from nfmsdb.answer where id=tid order by aid limit 2,1)=1 then 4 else 0 end) +
(case when(select isRight from nfmsdb.answer where id=tid order by aid limit 3,1)=1 then 8 else 0 end) +
(case when(select isRight from nfmsdb.answer where id=tid order by aid limit 4,1)=1 then 16 else 0 end)
as 'answer'
FROM nfmsdb.answer
group by id;
中间的 case 子句不知道斑竹有没有改良方法
'ff8080813abfd2c5013abfd36d430009', '1' 2
'ff8080813abfd2c5013abfd36d430009', '1' 4
'ff8080813abfd2c5013abfd36d430009', '0' 0
'ff8080813abfd2c5013abfd36d430009', '1' 16
'ff8080813abfd2c5013abfd36d430009', '1'32是这样?
如果是,要加入1个行号