', 'bucketball ');Insert into test values ('David', 'badminton ');Insert into test values ('David', 'badminton ');
The syntax of Groupby requires the evolution of distinct.
Select name, group_concat (distinct (holobby), count (distinct (holobby) from test where holobby in ('bucketball ', 'badminton ') group by name having count (distinct (holobby)> 1
The Writing Method of the exists statement does not need to be modified, but the execution plan is dependent
ArticleDirectory
Non-equivalent join:
Select empno, ename, Sal, grade from EMP, salgrade where Sal between salgrade. losal and salgrade. hisal;
Non-equivalent connection, external connection, self-connection, subquery
Non-equivalent join: Select empno, ename, Sal, grade from EMP, salgrade where Sal between salgrade. losal and salgrade. hisal; outer join: Select * from EMP, DEPT where Dept. deptno = EMP. deptno (+); in addition to the same depa
For example, there is a student table:
Id student_name
1 S1
2 S2
03S3
04S4
05S5
06S6
07S7
08S8
09S9
These IDS are inconsistent. For example, I want to change IDs to 1, 2, 3 (instead of 03), 4, 5, 6, 7, 8, 9.
You can perform the following operations:
Update student a set a. ID = (select substr (B. ID,-1) from student B where B. ID = A. ID)Where a. ID> 2;
Commit;
The result is changed:
Id student_name
1 S1
2 S2
3S3
4S4
5S5
6S6
7S7
8S8
9S9
Then, you can use the
In mysql5.6.19, The subquery cannot use the index. mysql5.6.19 Index
The table structure is simple.
Create table 'oplogs '('Id' int (10) unsigned not null AUTO_INCREMENT,'Kind' varchar (45) not null default '','Op' varchar (100) not null,'User' varchar (25) not null default '','IP' varchar (16) not null default '','Updatetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,Primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT =
The WITH clause is the content of the SQL-99 standard and is introduced in Oracle9.2. The WITH clause can be used to name subqueries. When a subquery is used in multiple places, you can directly use the query name. Subqueries named by this clause are treated as inline views or temporary tables by the optimizer. In the latter case, query efficiency can be improved.Using the SCOTT mode, you can use the inline view for each employee to obtain the number
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 "subquery".
2, select tab1.*, tab2.* from Tab1, tab2 where TAB1. [Field]=tab2. FieldThis
Org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile (querytranslatorimpl.java:136)At Org.hibernate.engine.query.spi.HQLQueryPlan. (hqlqueryplan.java:105) at Org.hibernate.engine.query.spi.HQLQueryPlan.
(hqlqueryplan.java:80) at Org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan (queryplancache.java:168) At Org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan (abstractsessionimpl.java:221) at Org.hibernate.int Ernal. Abstractsessionimpl.createquery (abstractse
mapply.mid = Mconfig.mid
WHERE Mapply.aid
In (
SELECT Aid
From ' mapply ' where state = $state
ORDER BY ' atime ', ' uid ' DESC
)
So no error, MO away thought OK, but after running found that the data have problems
and simply read out the content of the application form is not the same, only to find limit position misplaced, and then put limit in, the results of the following error
This version of the MySQL doesn ' t yet support ' LIMIT in/all/any/some
(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
1 GROUP by indicates a sort, followed by the first data in each group, where the condition is indicated, if you need conditional filtering on the order, you should follow the property name with a + condition. and cannot use where2 aggregate function sum () count () Avg () max () min () group by is a group of queries that divide the same class of criteria into a set of general group by that is used in conjunction with an aggregate function. For exampleA person who has a score of more than 80 poin
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_ch,parent_menu_id,loclevel)As(SELECT a.menu_id
Label:--Paging data----row_number () is called window-opening function, can be paged operation Select Row_number () over (order by ID) as Num,*from gb_data----adds a sequential num value to each column to facilitate the use of the first few data. Select row_number () over (order by ID) as num,* from Gb_data where Num>5and num 10--This line of code is wrong, because the system does not recognize how much num is, why? -Because of the order of execution, the SQL statement executes the from, then
Renyuan--second pageSelect Top 5 * from Renyuan where code not in (select Top 5 code from Renyuan)--Page ThreeSelect Top 5 * from Renyuan where code not in (select top of code from Renyuan)--a total of several pages????Select CEILING (COUNT (*)/5.0) from Renyuan--Find out all information about people older than 35 years in the sales departmentSELECT * from Renyuan where code in(select code from Renyuan where age>35 and bc=(select Bcode from bumen where bname= ' sales Department '))--View all pe
A subquery is nested within another query statement by nesting a query statement
Query results for inner query statements that provide query conditions for outer query statements
Because in a particular case, the condition of one query statement requires another query statement to obtain
Reference table: Employee
Reference table: Department
subquery wit
Example: @ {HTML. telerik (). splitter (). name ("mainsplitter "). orientation (splitterorientation. vertical ). panes (vpanes ==>{ vpanes. add (). size ("50px "). content (@ Nested @ The MVC engine cannot escape @ Solution, with the help method, the help method (helper function (is defined in the view method, use the following {HTML. telerik (). splitter (). name ("mainsplitter "). orientation (splitterorientation. vertical ). panes (vpanes =
of values. Therefore, you can sum each record (UseSumFunction), but the current record and previous recordPriceAnd, as shown in the following figure.SQLStatement: Select A. XH, A. Price,( Select Sum (Price) From T_product B Where B. XH A. XH) As Totalprice From T_product from the preceding SQL statement, A subquery is used to calculate the value of totalprice field, the basic principle is to XH value (. XH ) to calculate the
Tag: Use Ar data 2014 sp c time new ef
I. Application of subquery
1. Paging Query1) 5 data entries are displayed on each page; 2) Top 5 pages (3-1) -- 3rd
Select top 5 * from car where code not in -- Skip ...... Obtain the first few data records.(Select top 10 code from car -- number of intermediate values skipped)
-- Query data on a specified page2. Retrieve the total number of pagesSelect ceiling (count (*)/5.0) from car
PS: Count (*) is an integer
The subquery is not supported in hiveThis does not imply that it does not support in or notIn or not in the back is fixed value words are supportedBut it can be customized.For exampleSelect ID from table not in (all-in-a-But this is not supported.Select ID from table1 not in (Select ID from table2 where col1 = ' a ')We need to use the left join to implement(1) Connect the Table1 and table2 to the eligible dataSelect T1.id as id1,t2.id as Id2 from tabl
For example, update table1 t1 set t1.name= (Select t2.name from table2 t2 where t2.age = 20)
Cause of error: the subquery contains more than one record.
If you want to update data in batches, You can nest the data, for example, the following SQL statement. Of course, you can continue nesting.
Update table1 t1 set t1.name=(Select t3.name, t3.id from(Select t2.name from table2 t2 where t2.age = 20)T3 where t3.id = t1.id)
Next to"=The returned result
I went to an interview with two cool Internet companies a few days ago and encountered problems in SQL. Well, unfortunately, let's take a look at it.
Score
1. Use group
Group by Dimension
For example:
Calculate the total score of each student.
SELECT student, SUM (score) FROM score group by student
Average score for each student
SELECT student, AVG (score) FROM score group by student
You can also follow the class and Course
2. Differences between having and where
Having is similar to where. It
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.