SQL问题

作者: Klart | 来源:发表于2017-10-09 20:47 被阅读55次

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

我在这个表里查询了数据
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' ))

相关文章

  • sql审核-避免离线sql导致的db集群故障

    关键词: sql审核、sql审批、sql检查、sql检测、sql执行 离线sql可能会导致的问题 首先,什么是离线...

  • SQL问题

    发现最终数据好像就是多个限制条件连在一起的,而且还是相同的限制条件,所以先把问题拆分一下! 我在这个表里查询了数据...

  • SQL查询优化

    如何获取有性能问题的SQL 通过用户反馈获取存在性能问题的SQL 通过慢查日志获取存在性能问题的SQL 实时获取存...

  • MySQL之SQL查询优化

    SQL查询优化 获取有性能问题的SQL 通过用户 通过慢查日志获取存在性能问题的SQL 实时获取存在性能问题的SQ...

  • MySQL的SQL语句如何优化

    1.如何获取有性能问题的SQL 通过用户反馈获取存在性能问题的SQL 通过慢查日志获取存在性能问题的SQL 实时获...

  • YII2安全之SQL注入和XSS攻击

    SQL注入 疑问:SQL语句拼接变量 //直接把获取的$id代入(有问题)$sql...

  • LC-mysql-184(DeparmentHighestSal

    本SQL问题是 #184. Department Highest Salary 问题 编写SQL,找出每个部门的最...

  • MySQL性能管理及架构设计(三):SQL查询优化、分库分表 -

    一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢...

  • SQL查询优化,分库分表

    一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢...

  • 高性能MySQL学习笔记(六)

    如何获取由性能问题的SQL1、通过用户反馈获取存在性能问题的SQL2、通过慢查日志获取存在性能问题的SQL3、实时...

网友评论

      本文标题:SQL问题

      本文链接:https://www.haomeiwen.com/subject/ujzpyxtx.html