SQL Formatter 1 2
Exquisite SQL statement
The smart people in the eyes of Bill Gates can't see: 12 classic feelings in life
Exquisite SQL statement introduction
Oracle Union All Query Returns all parent records containing the ID, Item, Empid, Money1 001 1002 001 150 ... ...
A TOTAL table, Empid, Money, Money, is to insert each EMPID of the Detail table into the Total table, the written statement is the same, Write 1: Update Total a set Money = (SELECT SUM (Money (Money, 0)) from detail where a.empid = Empid Group by Empid
Writing 2: (Some Universal) Drop Table Total; Create Table Total As Select Empid, SUM (NVL (Money (Money, 0)) Money from Detail Group by Empid; Write 3: Update Total A Set Money = (SELECT SUM (NVL Money, 0)) from detail where a.empid = Empid) Write 4: Update Total a set Money = (Select Sum (NVL (B.Money, 0)) from Detail B Where a.empid = B.empid) Where exists WHERE EXISTS (Select 1 from detail b where a.empid = b.empid);
Q] How to use Hint Tips [A] in SELECT / Delete / Update Record / * Hint * /, Select / * Index (Table_Name Index_name) * / COL1 ... Note / * and Can't have space Use Hint to specify an index
Select / * index (cbotab) * / col1 from cbotab; SELECT / * INDEX (CBOTAB CBOTAB1) * / Col1 from cbotab; SELECT / * INDEX (a cbotab1) * / col1 from cbotab A; where Table_Name is necessary Written, and if you use the alias of the table in the query, you must use the alias of the table instead of the table name; index_name can do not need to write, Oracle will select an index according to the statistical value; if the index name or table name is wrong The hint will be ignored;
Create Procedure DBo.PostGetPostBypage (@Page Int, @forumID INT, @topornot int) as / * set nocount on * / decare @begin int, @ End, @ f int, @ L int, @ count int, @ Top INT SELECT @ TOP = count (*) from posts as p1 where p1.postid = (Select Min (postid) from PostId = p2.threadid) and p1.fordid=@forumid and posttype = 4 if @topornot = 1 Select p1.posttype, p1.title, p1.username, p1.totalviews, p1.postid, p1.threadid, p1.forumid, filename, reply = (select count (*) from posts as p2 where p1.threadID = P2.threadid) -1, lastdate = (select max (postddate) from postid = p2.threadid, lastwriter = (select username from posts as p2 where p2.postid = (SELECT MAX "from FROM PostidId = p2.threadid)) from posts as p1 where p1.postid = (select min) from posts as p2 where p1.threadid = p2.threadid) and p1.forumid=@forumid and posttype = 4 else if @ Topornot = 2 Begin select @ count = count (*) from posts as p1 where p1.postid = (select min (postid) from posts as p2 where p1.ThreadID = p2.ThreadID) and p1.ForumID=@forumid and PostType <> 4 declare my_cursor SCROLL CURSOR for select p1.PostID from Posts as p1 where p1.PostID = (select min (PostID) from Posts as p2 where p1 .ThreadID = p2.ThreadID) and p1.ForumID=@forumid and PostType <> 4 order by (select max (PostID) from Posts as p2 where p1.ThreadID = p2.ThreadID) desc open my_cursor if @ count @ top <25 And @ Page = 1 Begin Select @ f = 1 Select @ l = @ count end if @ count @ Top> = 25 and @ Page = 1 Begin Select @ f =
1 SELECT @ L = 25- @ Top end if (@ Page * 25- @ Top> @POUNT) and @Page> 1 Begin SELECT @f = (@ Page-1) * 25 1- @ Top SELECT @ L = @PANT END IF (@Page * 25- @ Top <= @ count) and @Page> 1 Begin Select @f = (@ Page-1) * 25 1- @ Top SELECT @ L = @ Page * 25- @ top end fetch absolute @f from my_cursor into @begin fetch absolute @l from my_cursor into @end set nocount off select p1.PostType, p1.Title, p1.UserName, p1.TotalViews, p1.PostID, p1.ThreadID, p1. ForumID, filename, reply = (select count (*) from posts as p2 where p1.threadid = p2.threadid) -1, lastdate = (select max (postddate) from posts as p2 where p1.threadid = p2.threadID), Lastwriter = (Select UserName from Posts as P2 WHERE P2.POSTID = (Select Max (Postid) from PostId = p2.threadid)) from posts as p1 where p1.postid = (Select Min (PostID) from POSTS AS P2 WHERE P1.THREADID = p2.threadid) and P1.forumid=@forumid and postid <= @ begin and postid> = @ End and posttype <> 4 Order by (SELECT MAX) AS P2 WHERE P1 .Threadid = p2.threadid) desc close my_cursor end return @@
