Common SQL query statements

1. Obtain the first row of data in the table

select top 1 * from Dept

2. display a specific row in the first row

select DeptNO,DeptName,Phone,Manager,Deputy,Remark from dept where parentID='3' order by case when deptNO='3' then 0 else 1 end

3. SQL nested Query

select a,b,c from tableD where a=(select a from tableD)

select * from table1 where field1 in    (select field1 from table2 where field2 in      (select field2 from table3 where field4 in        ...................       )    )  

4. SQL sorting Query

Select * from Dept order by parentid desc -- select * from Dept order by parentid ASC in ascending order, deptno ASC

5. Time fuzzy query in SQL

Select * from t where convert (varchar, sendtime, 120) Like '2017-12-2007 '-- t is the database table, and sendtime is the field name of the time field in the database

6. query unique

select distinct Pname from planweek

7. Remove leading and trailing Spaces

use Performance select  ltrim(rtrim(dutyname)) as dutyname from [user] where uid=10

8. Joint Query

Select dept2.deptname as 'department ', Dept. deptname as 'group', planweek. pname as 'name', [user]. dutyname as 'post', left (convert (varchar, planweek. [years], 120), 7) as 'Planning month', -- convert to convert (varchar, planweek. werks, 120) as 'number of planned weeks', -- convert to the string planweekdetails. task as 'work key', planweekdetails. target as 'work target', planweekdetails. weights as 'weight', planweekdetails. datesp as 'scheduled start time', planweekdetails. datesa as 'actual start time', planweekdetails. dateep as 'planned completion time', planweekdetails. dateea as 'actual End Time', planweekdetails. suggestion as 'complete', planweekdetails. reason as 'unfinished reason ', planweekdetails. summary as 'work summary', planweekdetails. remark as 'note' from planweek left join planweekdetailson planweek. id = planweekdetails. idleft join [user] On planweek. uid = [user]. UID left join (select * from Dept where parentid <> 0) as dept on [user]. deptid = Dept. idleft join (select * from Dept where parentid = 0) as dept2 on [user]. deptid = dept2.id or Dept. parentid = dept2.idwhere planweek. pname like '%' and planweek. years like '%' and planweek. werks like '%' order by planweek. werks, dept2.deptname, Dept. deptname, planweek. pname, [user]. dutyname, planweek. [years]