SQL问题

发现最终数据好像就是多个限制条件连在一起的，而且还是相同的限制条件，
所以先把问题拆分一下！

我在这个表里查询了数据
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam where userid='F1331881'
) a
group by examid,userid,workstation,workstationid

workstation和workstationid是一一对应关系（但因为是不同BU的，所以这里看不出来，）

考试试卷信息

要用到的栏位都显示出来了。

问题？：这个表里他有相同的workstation,例如印刷机有多个，我现在要做到是把相同的给加入限制条件（如果有相同的workstation,就取这个useID所在的bu 工站ID（workstationID））

select * from esoppost

esoppost表

select * from tb_emp

3.png

联合查询一下，查找到userid 所在的BU的工站ID了，

select a.* from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881'

4.png

第一张图的workstationID和esoppost表中的ID是一一对应关系

已经搞定了第一个小问题，虽然这语句写的有点渣，但

select * from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881')
union
select * from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid not  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881') and workstation not in(
select workstation from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881'))

问题2？：这个语句如何改进

最终语句

select  examid,userid,workstation,workstationid, cast(score as varchar) score from (
select * from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881')
union
select * from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid not  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881') and workstation not in(
select workstation from (
select examid,userid,workstation, max(cast(score as int)) score,workstationid from (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881'  ) a
group by examid,userid,workstation,workstationid )a where workstationid  in(
select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881'))
)a
union 
select distinct examid,userid,station,workstationid,score from (
select userid,examid,station,classname,workstationid,score from Practical  
union all
select distinct userid,examid,station,classname,workstationid,score from PractrcalTemp
) b

最终效果

success.png

查询结束，这个问题解决了！！现在是怎么把这个语句给优化
虽然可以写在存储里面！但这语句看起来就很渣的样子，完全不能装逼嘛！
所以要如何优化呢！！！！

• 我优化了一下，但只是把语句缩短了一点点而已，完全说不上优化啊！！：

把 (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881' 
 ) a
换成 
EsopUserExam  where userid='F1331881' 
这里 的group by 分组效果和distinct去重效果 一样所以 这里去除了这个语句

改进了一下，越来越长了，这绝对是我写过的单句最长的SQL
有用到的函数：

• isnull(str1,str2) 当str1为空时，用str2代替
• isnumeric(str1) 查找当前栏位为数字的；
• distinct() 去重
  -count()计数

select @gg=workstationid from #a where workstationid in (
            select workstationid from #a where workstation in (
            select workstation from #a group by workstation having  count(workstation)>1) group by workstationid having count(workstationid)<2) 
            and isnumeric(score)>0

            select * from #a where workstation not in (
            select workstation from #a where workstationid in (
            select workstationid from #a where workstation in (
            select workstation from #a group by workstation having  count(workstation)>1) group by workstationid having count(workstationid)<2)
            and workstationid in (  select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881' ))
            union
            select* from #a where workstationid in (
            select isnull(workstation,()) from #a where workstationid in (
            select workstationid from #a where workstation in (
            select workstation from #a group by workstation having  count(workstation)>1) group by workstationid having count(workstationid)<2)
            and workstationid in (  select a.id from esoppost a,tb_emp b where a.buname=b.plant and b.emp_no='F1331881' ))