SQL学习-复杂查询

1.子查询与连接

WHERE client_id NOT IN (

               SELECT  DISTINCT client_id

               FROM table)

等于

LEFT JOIN invoices USING (client_id)

WHERE invoice_id IS NULL

2.ALL用法

WHERE invoice_total > ALL(

             SELECT invoice_total

             FROM invoices

             WHERE  client_id = 3

)

等于

WHERE invoice_total >(

             SELECT MAX(invoice_total)

             FROM invoices

             WHERE  client_id = 3

)

3.ANY用法

WHERE client_id IN (                 IN 等于 =ANY

        SELECT client_id

        FROM invoices

        GROUP BY client_id

        HAVING COUNT(*) >=2

)

4.相关子查询

SELECT *

FROM employees e

WHERE salary >(

           SELECT AVG(salary)

           FROM employees

           WHERE office_id = e.office_id

)

每遍一次 e.office_id,重新查询一次 AVG(salary)

5.EXISTS用法

验证条件是否可行

SELECT *

FROM clients c

WHERE EXISTS (

        SELECT client_id

        FROM invoices

        WHERE client_id = c.client_id

)

子查询中为true时,结果返回

NOT EXISTS 结果为 false 时通过

6.SELECT中子查询

SELECT 

(SELECT AVG(invoice_total) FROM invoices) AS average,

invoice_total - (SELECT average) AS difference

FROM invoices

7.FROM中子查询

SELECT *

FROM

        SELECT 

        (SELECT AVG(invoice_total) FROM invoices) AS average,

        invoice_total - (SELECT average) AS difference

        FROM invoices

)AS table

WHERE ..

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值