In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Multi-column subquery
Where (manager_id, department_id) in
Subquery
100 90
102 60
124 50
Each row of the main query is compared to a subquery with multiple rows and columns
Comparison of columns
A comparison of multiple columns, including subqueries, which can be:
Non-paired comparison
Pairwise comparison
Compare subquery with subquery
1. Display other employee information in the same department and with the same manager as the employee named "John"
Select employee_id, manager_id, department_id from empl_demo
Where (manager_id, department_id) IN
(select manager_id, department_id from empl_demo
Where first_name = 'John')
AND first_name 'John'
Non-paired comparison
1. Display the employee number, manager number and department number of the manager ID and department ID whose name is not "John"
Select employee_id, manager_id, department_id
From empl_demo
Where manager_id in
(select manager_id
From empl_demo
Where first_name = 'john')
And department_id in
(select department_id
From empl_demo
Where first_name = 'john')
And first_name 'john'
Scalar quantum query expression
A scalar quantum query is a subquery that returns a column from a row.
Scalar quantum queries can be used in the following situations:
-part of DECODE and CASE conditions and expressions
-in all clauses except the GROUP BY clause in SELECT
SET clause and WHERE clause of-UPDATE statement
Scalar quantum queries in CASE expressions:
Select employee_id, last_name, department_id
(case
When department_id =
(select department_id
From departments
Where location_id = 1800)
Then 'canada' else' usa' end) location
From employees
Scalar quantum query in the ORDER BY clause:
Select employee_id, last_name,department_id
From employees e
Order by (select department_name
From departments d
Where e.department_id = d.department_id)
Related subquery
Related subqueries are executed row after row, and each row of the main query executes a subquery
Use columns from the main query in a subquery
Select column1, column2,...
From table1 Outer_table
Where column1 operator
(selecT column1, column2
From table2
Where expr1 = Outer_table.expr2)
2. Find all employee information about who earns more than the average salary of their department.
Select last_name, salary, department_id
From employees outer_table
Where salary >
(selecT AVG (salary)
From employees inner_table
Where inner_table.department_id =
Outer_table.department_id)
3. Show which employees have changed their jobs at least twice
Select e.employee_id, last_name,e.job_id from employees e
Where 2
(select dept_avg
From avg_cost)
Order by department_name
Recursive WITH clause
Recursive WITH clause:
Enables formulation of recursive queries.
Creates query with a name, called the Recursive WITH element name
Contains two types of query blocks member: anchor and a recursive
Is ANSI-compatible
With reachable_from (source, destin, totalflighttime) as
(
Select source, destin, flight_time
From flights
Union all
Select incoming.source, outgoing.destin
Incoming.totalflighttime+outgoing.flight_time
From reachable_from incoming, flights outgoing
Where incoming.destin = outgoing.source
)
Select source, destin, totalflighttime
From reachable_from
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 250
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.