發(fā)現(xiàn)最終數(shù)據(jù)好像就是多個限制條件連在一起的凤跑，而且還是相同的限制條件柿冲，
所以先把問題拆分一下酬滤！

我在這個表里查詢了數(shù)據(jù)
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是一一對應(yīng)關(guān)系（但因為是不同BU的隧膏，所以這里看不出來贿堰，）

考試試卷信息

要用到的欄位都顯示出來了跑杭。

問題铆帽？：這個表里他有相同的workstation,例如印刷機有多個，我現(xiàn)在要做到是把相同的給加入限制條件（如果有相同的workstation,就取這個useID所在的bu 工站ID（workstationID））

select * from esoppost

esoppost表

select * from tb_emp

3.png

聯(lián)合查詢一下德谅，查找到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是一一對應(yīng)關(guān)系

已經(jīng)搞定了第一個小問題，雖然這語句寫的有點渣窄做，但

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

查詢結(jié)束，這個問題解決了Ｍ终怠组砚！現(xiàn)在是怎么把這個語句給優(yōu)化
雖然可以寫在存儲里面！但這語句看起來就很渣的樣子掏颊，完全不能裝逼嘛糟红！
所以要如何優(yōu)化呢！Ｎ谝丁Ｅ璩ァ！

• 我優(yōu)化了一下准浴，但只是把語句縮短了一點點而已事扭，完全說不上優(yōu)化啊Ｐ至选＞浜怠：

把 (
select distinct(userid),workstation,examid,score,workstationid from EsopUserExam  where userid='F1331881' 
 ) a
換成 
EsopUserExam  where userid='F1331881' 
這里 的group by 分組效果和distinct去重效果 一樣所以 這里去除了這個語句

改進了一下，越來越長了晰奖，這絕對是我寫過的單句最長的SQL
有用到的函數(shù)：

• isnull(str1,str2) 當str1為空時谈撒，用str2代替
• isnumeric(str1) 查找當前欄位為數(shù)字的；
• distinct() 去重
  -count()計數(shù)

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' ))