SQL常用查詢語句

1.擷取表中第一行資料

select top 1 * from Dept

2.讓特定的某一行顯示在第一行

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 巢狀查詢

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 排序查詢

select * from Dept Order by ParentID desc  -- 降序    select * from Dept Order by ParentID asc   -- 升序    select * from Dept Order by ParentID asc , DeptNO asc  

5.sql中 時間模糊查詢

SELECT * FROM T WHERE Convert(varchar,sendTime,120) LIKE '2007-12-30%'--T為資料庫的表，sendTime是資料庫中的時間欄位的欄位名

6.查詢唯一

select distinct Pname from planweek

7.去除前後空格

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

8.聯集查詢

select dept2.deptName as '部門' , dept.deptName as '小組' ,planweek.Pname as  '姓名',[User].DutyName as '職位',left((Convert(varchar,planweek.[Years],120)),7) as '計劃月', --轉換成年月Convert(varchar,planweek.Werks,120)  as '計劃周數',--轉換成字串PlanWeekDetails.Task as '工作重點',PlanWeekDetails.Target as '工作目標',PlanWeekDetails.Weights as '權重',PlanWeekDetails.DateSP as '計劃開始時間',PlanWeekDetails.DateSA as '實際開始時間',PlanWeekDetails.DateEP as '計劃完成時間',PlanWeekDetails.DateEA as '實際結束時間',PlanWeekDetails.Suggestion as '完成情況',PlanWeekDetails.Reason as '未完成原因',PlanWeekDetails.Summary as '工作總結',PlanWeekDetails.Remark as '備忘'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]