how to write subquery in sql

Tags: style blog http color ar sp Data div Arthttp://blog.csdn.net/c517984604/article/details/7052186 [ERR] 1242-subquery returns more than 1 row--Indicates that the subquery returned multiple rows of dataFor example:SELECT * FROM table1 where table1.colums= (select columns from table2)Workaround1,select * FROM table1 where column=any (select columns from table2)2,select * FROM table1 where column in (selec

subquery statementSome: and "any" mean the sameIn: Same meaning as "=any"Exists: Sub-query statement returns at least one record Example: Query for departments where all employees pay less than 2000 /* * Sub -query query all employees pay less than 2000 of the department */ @Test publicvoid childtest () { List). List (); for (Dept dept:list) { System.out.println (Dept.getdeptname ());

I collected online materials and added my own SQL. 1. Group by is a group query. Generally, group by isUsed with aggregate Functions The principle of group by is that all columns after select do not use aggregate functions and must appear after group. 2. Having The where clause is used inBefore grouping query resultsRemove rows that do not meet the where condition, that is, filter data before grouping,The condition cannot contain clustering fu

not exists (select * From t_to_order_insurance where between = t_to_order_tickets.order_id) and departure_time Summary: Observe step 1 of the original SQL Execution Plan| 10 | View | vw_nso_1 | 1120k | 13m | 26017 (7) | 00:05:13 |Vw_nso_1 indicates that Oracle has performed subquery decoding nesting (which is unlocked by default, This is where the problem is. If you don't want him to solve it, you can te

the comments below)--correct wordingSELECT * fromDbo. Table_a asaWHEREa.ID not inch(SELECTb.id fromDbo. Table_b asbWHEREb.ID is not NULL)--exclude null values to participate in operator comparisons --suggested modifications to the associated query method--correct wording 1SELECT * fromDbo. Table_a asaWHERE not EXISTS(SELECT * fromDbo. Table_b asbWHEREa.ID=b.id)--correct wording 2SELECT * fromDbo. Table_a asa Left OUTER JOINDbo. Table_b asB ona.ID

Label:/* Update--1 using an update with the associated subquery . Create a test table Create TABLE Table1 ( a varchar (ten), B varchar (ten), C varchar (ten), CONSTRAINT [pk_ Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) on [PRIMARY] create TABLE table2 ( a varchar (ten), C varchar (ten), CONSTRAINT [pk_table2] PRIMARY KEY Clustered ( a ASC ) on [ Primary] go insert into Table1 values (' Zhao ', ' ASDs ',

SQL subquery, multi-Table query, and joint Query Subquery, multi-Table query, and joint QueryThese three concepts are interpreted slightly differently in different SQL versions, roughly as follows: JoinYou can check related SQL documents or buy a

subqueries, multiple table queries, federated queriesThese three concepts are interpreted slightly differently in different versions of SQL, roughly as follows: Join connectionCan look at the relevant SQL information, or buy a book of SQL 1, such as: SELECT * from TAB1 where ID in (SELECT id form tab2 where ...)The query sentence in parentheses above belongs to

of the subquery results. Also note that the all and any operators cannot be used alone, and can only be used in conjunction with single-line comparators (=, >, Cases: 1). Multi-row subqueries use the in operation symbol Example: The query took the name of the student whose teacher was named Rona (assuming only) Sql> Select Stname From Student where Stid in (SelectDistinct Stid from score where teid= (sele

Here I see a post example. Requirement: use SQL statements to find out who will play basketball and badminton and find out the name-Hoby combination. Create table test (NAME varchar (20) not null, holobby varchar (20) not null );Insert into test values ('Adam ', 'bucketball ');Insert into test values ('bill ', 'bucketball ');Insert into test values ('bill ', 'football ');Insert into test values ('cyper', 'bucketball ');Insert into test values ('cype

(select Top 5 code from Renyuan)Select top 5*from Renyuan where code not in (select Top ten code from Renyuan) --a total of a few pagesSelect CEILING (COUNT (*)/5.0) from Renyuan --Find out all information about people older than 35 years in the sales department SELECT * from Renyuan where code in(select code from Renyuan where age>35 and ygbm=(select Bmcode from bumen where bmname= ' sales Department ')) --View all personnel information and replace the department number with the department nam

Tags: io ar sp on C R SQL BS as1. Check all subdirectories by root directoryWith Locs (Menu_id,menu_name_ch,parent_menu_id,loclevel)As(SELECT a.menu_id,a.menu_name_ch,a.parent_menu_id,0 as Loclevel from Sys_menu_info Awhere a.menu_id=75UNION AllSELECT a.menu_id,a.menu_name_ch,a.parent_menu_id,loclevel+1 from Sys_menu_info AINNER JOIN locs p on a.parent_menu_id=p.menu_id)SELECT * from Locs2. Start querying by sub-directoryWith Locs (Menu_id,menu_name_c

(T.taskid,'__')= IsNull(N.taskid,'__')Group byT.taskidThe execution plan is as follows: (this looks much simpler than the above, imagine that performance will improve)When the amount of data is large, the performance aspect of using the left join will be greatly improved, note that when on, the null value is converted using IsNull.The two methods can be very different when the data volume performance is not very large, but when the data is large and the sub-query nesting complex may need to car

shows that the syntax of the subquery statement is not checked, but filtering plays a role, which is indeed a strange thing. If I mistakenly typed select xid in select * from tb1 where id in (select xid from tb2) into select id when I typed an SQL statement, then there will be a problem with the Retrieved Data (full extraction), but there is no prompt, because the syntax check is skipped. Therefore, this i

Label:Execute SQL statement: SELECT * FROM ( SELECT * from tab where id>20 order by UserID Desc As a order by date desc Logically looking pretty right but error: The ORDER by clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions unless you specify also TOP or for XML. As long as we add in the nested subqueries view: Top percent SELECT * FROM ( Select Top Percent * from Tab where id>20 order by UserI