精华内容
下载资源
问答
  • where有关的优化:使用where代替having

    千次阅读 2019-02-21 11:42:21
    注意:时间差不明显是因为数据比较少,数据多的话更好验证  ...3、行被分组需要时间,降低分组的行数可以提高效率,所以使用where 字句代替havingz字句 验证 select JOB,max(sal) from emp group by JOB  havi...

    注意:时间差不明显是因为数据比较少,数据多的话更好验证 


    一、使用where字句代替having字句 
    1、select语句汇总,where字句过滤行 
    2、having字句是分组后才过滤 
    3、行被分组需要时间,降低分组的行数可以提高效率,所以使用where 字句代替havingz字句

    验证

    select JOB,max(sal)  from emp
    group by JOB 
    having JOB<>'CLERK';
    
    
    select JOB,max(sal)  from emp
    where JOB<>'CLERK'
    group by JOB ;


    二、使用<=代替< 
    empno<7901与empno<=7900的区别 
    1、使用empno<7901会定位到7901,然后再去找比7901小的数据 
    2、使用empno<=7900d自己定位到7900 
    3、在查询数据量大,并且在循环语句中使用这2个比较操作符时,区别就很明显

    验证

    select * from emp where empno<7901;
    select * from emp where empno<=7900;

    --------------------- 
    作者:单身贵族男 
    原文:https://blog.csdn.net/zhou920786312/article/details/72857428

    展开全文
  • where字句有关的优化 注意:时间差不明显是因为数据比较少,数据多的话更好...3行被分组需要时间,降低分组的行数可以提高效率,所以使用where 字句代替havingz字句验证select JOB,max(sal) from emp group by JOB ha

    where字句有关的优化
    注意:时间差不明显是因为数据比较少,数据多的话更好验证
    使用where字句代替having字句
    1select语句汇总,where字句过滤行
    2having字句是分组后才过滤
    3行被分组需要时间,降低分组的行数可以提高效率,所以使用where 字句代替havingz字句

    验证

    select JOB,max(sal)  from emp
    group by JOB 
    having JOB<>'CLERK';
    
    
    select JOB,max(sal)  from emp
    where JOB<>'CLERK'
    group by JOB ;

    这里写图片描述

    使用<=代替<
    empno<7901与empno<=7900的区别
    1使用empno<7901会定位到7901,然后再去找比7901小的数据
    2使用empno<=7900d自己定位到7900
    3在查询数据量大,并且在循环语句中使用这2个比较操作符时,区别就很明显

    验证

    select * from emp where empno<7901;
    select * from emp where empno<=7900;

    这里写图片描述

    展开全文
  • SELECT Mate_PBusinessDocNo 采购单号,Mate_PTotalQuantity 总计数量,Mate_PTotalTon 总计吨数,Mate_PTotalAmount, SUM(Mate_PDWholeQuantity) 细表数量, SUM(Mate_PDWholeTon) 细表吨数, SUM(Mate_PDAmount) 细表...
    SELECT Mate_PBusinessDocNo 采购单号,Mate_PTotalQuantity	总计数量,Mate_PTotalTon	总计吨数,Mate_PTotalAmount,
    	SUM(Mate_PDWholeQuantity) 细表数量, SUM(Mate_PDWholeTon) 细表吨数, SUM(Mate_PDAmount) 细表金额
    FROM dbo.Mate_Purchasing p -- 采购单
    LEFT JOIN dbo.Mate_PurchasingDetail pd ON p.Mate_PCode = pd.Mate_PCode-- 采购明细
    GROUP BY Mate_PBusinessDocNo,Mate_PTotalQuantity,Mate_PTotalTon,Mate_PTotalAmount
    HAVING Mate_PTotalQuantity <> SUM(Mate_PDWholeQuantity) OR Mate_PTotalTon <> SUM(Mate_PDWholeTon) OR Mate_PTotalAmount <> SUM(Mate_PDAmount)
    		OR COUNT(Mate_PDWholeQuantity) = 0 --不存在细表数据的单据

    展开全文
  • Not sure where is a bottleneck in your procedure, but IN (with,constants, here) may not be a culprit. Maybe indexes? I just run a query with some 2100 constants (yes, 2 zeros – all integers)...

    http://bbs.csdn.net/topics/390137775/

    EXPLAIN EXTENDED

    How to create fast database queries

    Passing parameters in MySQL: IN list vs. temporary table

    with 4 comments

    When you need to pass a list of parameters into a MySQL, there are at least two method to do this:

    • Issue a dynamical query which would fill an IN list with constant values
    • Fill a temporary table with these values then use it in a JOIN

    The second method is considered better for many reasons (you can easily reuse the values, it's more injection-proof etc.)

    Leaving all these issues aside, let's compare these methods performance-wise.

    We will create a table of 50,000 records, pass a list of 500 parameters (using both methods) and see which is faster:


    Table creation details

    We have created two stored procedures.

    The first procedure, prc_temporary, fills the temporary value using a dynamic query then issues the query using a JOIN in a loop.

    The second procedure, prc_range, just uses the IN list in a dynamic query, also in a loop.

    Let's run both procedures:

    1. CALL prc_list(1000)
    _cnt
    1001
    1 row fetched in 0.0001s (4.1218s)
    1. CALL prc_temporary(1000)
    _cnt
    1001
    1 row fetched in 0.0001s (1.9406s)

    However, if we change the procedures and leave but 20 parameters:

    Changing procedures here

    , both procedures have almost the same performance:

    1. CALL prc_list(1000)
    _cnt
    1001
    1 row fetched in 0.0001s (0.6315s)
    1. CALL prc_temporary(1000)
    _cnt
    1001
    1 row fetched in 0.0001s (0.6408s)

    We see that for a large list of parameters, passing them in a temporary table is much faster that as a constant list, while for small lists performance is almost the same.

    Using a temporary table is the best way to pass large arrays of parameters in MySQL.

    Written by Quassnoi

    August 18th, 2009 at 11:00 pm

    Posted in MySQL

    4 Responses to 'Passing parameters in MySQL: IN list vs. temporary table'

    Subscribe to comments with RSS

    1. You could provide an SQL example

      Alon

      24 Apr 13 at 15:51

    2. @Alon: click on the links (“Table creation details”) and (“Changing procedures here”)

      Quassnoi

      24 Apr 13 at 16:03

    3. Isn’t mysql internally creating a temp table even with IN (LIST) syntax? If you perform an EXPLAIN on any query with the IN list statement, you will see the “using temporary” message. Based on that I would think there has to be more to this performance difference. Perhaps the temp table that Mysql is using also has a unique index on the values, which would avoid redundant / duplicate joins and save on query time but lose on time to create the unique temp table values. It would be interesting to see the results of your test if you also added unique indexing on your temp table. I would think the methods would be on par at that point.

      Mike

      30 Jun 13 at 12:03

    4. Not sure where is a bottleneck in your procedure,
      but IN (with,constants, here) may not be a culprit.
      Maybe indexes?

      I just run a query with some 2100 constants (yes, 2 zeros – all integers) on a temp table 11.000 rows and it took some 0.4 sec to complete.

      I must add, that query had 7 tables involved 2 of which were LEFT JOINed and it was quite heavy with filters: multiple BETWEENs,”=”, even some SELECT GROUP_CONCAT() subqueries.
      On top of above, query run BOOLEAN FULLTEXT search on 2 tables, which is not speediest of them all.

      0.4 sec is not that bad.

    展开全文
  • where 条件中 使用 instr 替代 in

    千次阅读 2017-04-08 15:43:00
    where ee.pro_ype = processType  and ee.cost_type in ( DECODE(processType,'1','1,2','2','2','') )  processType 为传进来的参数 相当于根据传进来的值分别得到不同的查询语句: 1. select * from
  • postgresql where in改写为 where any

    千次阅读 2019-06-11 17:23:51
    通常 where in sql where column in (field-1,field-2,...,field-n); 稍高级 where in sql where column in (select column from table where condition) 更高级 where in sql where column = any(array(select ...
  • 前几天在QQ群里看到有位同学请人帮忙根据社区里的文章(给 Eloquent 的 whereHas 加个 where in 的优化)给Eloquent的whereHas方法进行性能优化,因为这篇文章里面提供的代码还是有些细节问题,并不能支持所有的关联...
  • 但它与where条件相比,孰优孰劣,还真没搞个所以然呢! 比如想查自带表emp里,mgr是王的7839编号的人总数,并按部门分组统计,可以这样:SELECT e.deptno 部门编号, count(CASE WHEN e.mgr=7839 THEN 1 ELSE NULL END) 王...
  • link中如何使用groupby代替where获取过滤后的数据?
  • HAVING子句会在分组之后对分组形成的结果进行过滤。 ...这个过程需要聚合、排序,因此如果通过WHERE子句限制记录而省略HAVING子句,是可以提升性能的。 点击(此处)折叠或打开 ...
  • 转自:http://www.cnblogs.com/dwfbenben/archive/2013/09/07/3307941.html当我们使用某个表达式作为输出的一列时,我们无法在Where...select id, (c1 + c2) as s from t1 where s > 100 运行会报错: “列名 s 无效”
  • SQL语句,当WHERE条件需要搜索工号为16或工号为2的雇员姓名时,需用到OR关键词如: SELECT [EmployeeName] FROM [Employee] WHERE [WorkNumber] = 16 OR [WorkNumber] = 2 但是,上面SQL语句会涉及...
  • --在存储过程中where条件in()的替代做法 DECLARE @test NVARCHAR(50) SET @test='48,49,50,51'; SELECT * FROM dbo.TbExamRecord WHERE CHARINDEX(','+CAST(reid AS NVARCHAR(10))+',',','+@test+',')>0 ...
  • 什么不能再where语句中使用聚合函数

    万次阅读 多人点赞 2016-08-15 15:07:42
    1.问题描述 select deptno ,avg(sal) from emp where count(*)>3 group by deptno; 在where 句中使用聚合函数count(*),报出错误:...那是为什么呢? 2.问题解决: 大致解释如下,sql语句的执行过程是:from-->where
  • IDL where函数

    2020-04-03 21:07:10
    where函数介绍 函数WHERE()能返回数组中满足指定条件的元素下标 调用格式: Result=Where(数组表达式[,count][,Complement=变量1][,/L64][,NCOMPLEMENT=变量2]) 其中,关键字count返回符合指定条件的元素个数;变量1...
  • np.where和torch.where的使用区别

    万次阅读 2019-01-22 10:41:21
    两者都是三个输入参数,第一个是判断条件,第二个是符合条件的设置值,第三个是不...mask_true = torch.where(torch.isnan(y_true), torch.full_like(y_true, 0), torch.full_like(y_true, 1)) mask_true = np.w...
  • 1.问题描述 select deptno ,avg(sal) from emp where count(*)&gt;3 group by deptno; 在where 句中使用聚合函数count...那是为什么呢? 2.问题解决: 大致解释如下,sql语句的执行过程是:from--&...
  • 数据准备:查询指定字段select 列1,列2,... from 表名;例:select name,gender from students;使用 as 给字段起别名select ...可以通过 as 给表起别名-- 如果是单表查询 可以省略表明select id, name, gender from st...
  • 情景分析 工作当中,动态sql经常会用到,当遇到多个查询条件时,我们可能会写出类似下面这样的sql <... select * from user where <if test="username != null"> username=#{usern
  • 很多时候,使用where 1=1 可以很方便的解决我们的问题,但是这样很可能会造成非常大的性能损失,因为添加使用了 “where 1=1 ”的过滤条件以后数据库系统就无法上使用索引等查询优化策略,数据库系统将会被迫 对每行...
  • SWIFT逻辑控制之where与guard

    千次阅读 2017-05-09 18:07:31
    SWIFT逻辑控制之where 这里对where的应用举个例子: for i in 0...100{ if i%3 == 0{ print(i) } } //上面的代码通过where实现: ...}在swift3中,使用if case这样的模式匹配,where关键字可以省略,转而使用
  • noe4j之cypher之where

    千次阅读 2017-09-29 16:51:19
    WHERE  where 关键字的用法在官方文档中描述为 与match 、...where可以加一些布尔运算符 入 and ,or, not ,xor。 大多数情况下where用于节点或关系的属性判断 关于属性值的相关运算种类比较多 比如 n.age
  • --注意as别名不能where条件语句中(会报错)原值代替就行例如。。此处where后条件名不能使用age SELECT s.*,c.grade,DATE_FORMAT(NOW(),'%Y') - s.birth AS age FROM stu s JOIN score c ON s.id=c.stu_id ...
  • 网上有不少人提出过类似的问题:“看到有人写了WHERE 1=1这样的SQL,到底是什么意 思?”。 其实使用这种用法的开发人员一般都是在使用动态组装的SQL。 让我们想像如下的场景:用户要求提供一个灵活的查询界面...
  • having 与 where 的区别

    2018-07-05 10:59:23
    having与where都是用来过滤查询结果集的,不同的是,where是用来对查询结果进行过滤,having是对分组进行过滤,但是where的执行级别要比having高,也就是说,会先执行where的过滤条件,再执行having过滤条件。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 226,211
精华内容 90,484
关键字:

where可以用什么代替