tricky sql queries

1. Use of computed columnsWhen querying computed columns: select ten from Deptno; The query results will be a column of numbers 10,deptno the table has a few lines of content, where the number of rows will appear 10.The use of 2.distinct.The main function is to go heavy;It is important to note that when you go to two or more fields, the relationship between the fields is "or" and only the exact same content is removed.3.between4.in5.topThere are two forms, top2 refers to the first two, top perce

The has been used since the start of the project. The original of this stored procedure (SORRY, forget the name), write this section of the SQL code is very good, I on this basis, according to my habits and thinking mode, adjusted the code, only to do paging query. /*----------------------------------------------*procedure name:p_pageresult * Author:fay * Create date:2014-07-18 * * CREATE PROCEDURE Prcpageresult--Get data for a page--@currPage

Tags: SELECT * from Information_schema.columns WHERE table_name= ' account ' SELECT [case] A.colorder=1 then d.name Else ' ' End ' as table name,--if the table name is the same, return the empty a.colorder as field ordinal, a.name as field name, (case when ColumnProperty (A.id,a.name, ' Isidentity ') =1 then ' √ ' else ' end as identifier (case when (SELECT count (*) from sysobjects--query primary key Where (the name in (the SELECT name from sysindexes WHERE (id = a).

Tags: font infinite structure sha where from logs pre sharpID,PID infinite structure, querying all subordinates or all ancestors of an ID, using with ASLook for subordinates with ID 1/* Find all subordinates with Id 1 * /with T as ( SELECT id,pid,name,0 L from User WHERE id=1 UNION all select U.id,u. Pid,u.name,l+1 from the User U INNER JOIN t on u.pid=t.id ) SELECT * from T Find all superiors with ID 100/* Find all ancestors with Id 100 * /with T as ( SELE

Tags: sel div tab Query 2017 UART try select any1. The following table, for the 2017 Q1 quarter from A_country to other cities of the highest volume of the company, and output volume sum ID from_country to_country company_name company_count year quarter month Select * FROM (select sum (company_count) as Top_count,company_namefrom testdat

Label:How to query data within half an hour select * from 1 表名 . wheredatediff(minute,createtime,getdate()) Ways to query the data of the day select * from 1 表名 . wheredatediff(day,createtime,getdate())=0 2, select * from 表名 where CONVERT (varchar (8), createtime,112) =convert (varchar (8), GETDATE (), 112) Recommend this type, high efficiency 3. Select * FROM table name where CAST (createdate as Date) =cast (GETDATE () as date) 3, String. Format (" select * from 表名 where createdate>= ' {1} 0:00

fromVW_PFJRDMX,VW_PFXHMX, KehuwhereKehu. Khdm=VW_PFJRDMX. DM1 andVW_PFJRDMX. SPDM=Vw_pfxhmx. SPDMGroup byKehu. KHDM, Kehu. KHMC;/*Wholesale Comparison Statistics current period limit: 2010-05-01 to 2010-05-31 Previous period time limit: 2010-04-01 to 2010-04-30 Commodity code pre-shipment amount The amount of goods shipped in the current issue volume increase: ( Current issue amount-pre-shipment amount) growth rate: (current issue amount-pre-shipment amount) *100/Pre- shipment amount*/SelectSh

The function I want to implement is to count the first three records of each order in the Order Log table, with the following table structure:An order in the Fixed bar log table has more than one record, according to the time to query out the first three records of each order, SQL as follows: SelectB.ordernumber,b.creationtime,b.remark from ( SELECTA.ordernumber,a.creationtime,a.remark from [Fortunelabford].[dbo].[So_log]awhereA.sysidinch (

Query all data with user number 1165:Select * from where inch (7,1,2, Yonghuid = '1165'orderbydescQuery for data with user number 1165 and monitoring parameter (1,2,7,15,19,20): Select * fromT_JIANKANGJC_JIANCESJ Awhere exists( Select * from ( SelectJiancecsid,Max(JIANCESJ) asFtime fromT_jiankangjc_jiancesjwhereYonghuid='1165' andJiancecsidinch( +,7,1,2, -, the)Group byjiancecsid) x whereX.jiancecsid=A.jiancecsid andA.jiancesj=X.ftime)SQL Server

Tags: des style blog http color io ar data spSQL to create the table usedCREATE TABLE [dbo]. [Emp_pay] ( [EmployeeID] [int] NOT NULL, [Base_pay] [money] is not NULL, [commission] [Decimal] (2, 2) is not null) on [PRIMARY ]The resulting table, and the data in the table:--Method OneSelect Top 1 * from( SELECT TOP 2 * from [dbo].[Emp_pay] WHEREBase_pay= - Order byEmployeeIDASC) asA--Note: Be sure to specify an alias after the derived table. Order byA.employeeidDESC--Me

Dry goods are as follows:exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' crs.***.com,6598 ' exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' Password '--exec sp_droplinkedsrvlogin ' ITSV ', null --Delete mappings (mapping to remote logins on linked servers) --exec sp_ Dropserver ' ITSV ' --delete remote server link Select * from ITSV.biz_xjp_rpt.dbo.rpt_b_territory where repid= ' dsr0009 'How SQL

/ * First implementation method, low efficiency and error * /DECLARE @DD DATETIME SET @DD = GETDATE () SELECT a.goodsid, A.account, a.lastupdate from Dbo.tb_app_goods a left JOIN dbo.tb_app_goods b on a.account = B.account and A.lastupdate > B.lastupdate GROUP by A.goodsid, A.account, A.lastupdate have COUNT (B.GOODSID) / * The second method of implementation, high efficiency and accurate * /SET @DD = GETDATE () SELECT a.goodsid, A.account,

1. Query which stored procedures (hereinafter referred to as SP) are used by a table to:SELECT DISTINCT object_name (ID) from syscomments where ID in(select id from sysobjects where type = ' P ') and the text like '%tablename% '2. Find those procedures to update the table:SELECT DISTINCT object_name (ID) from syscomments where ID in(select id from sysobjects where type = ' P ') and the text like '%update tablename% 'SQL Server

1 ----Paged Query @pageindex page @pageSize page There are several records2 Create procgetpagelist3 @pageIndex int,4 @pageSize int,5 @rowsCount intOutput6 as7 begin8 Select @rowsCount= COUNT(*) fromStudentinfo9 Select * fromTen(Select *, Row_number () Over(Order bystuid) One asRowIndex fromStudentinfo) asSTU1 A whereRowIndexbetween(@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize - End - --Execute the query process the Declare @temp int - execGetpagelist2,2,@tempOutput - Print @

. Isnullorwhitespace (Request. UserName) {userInfo = Userinfo.where (p = p.username.contains (Request). UserName)); }//number, state type if (request. Amount!=null) {result = result. Where (p = p.amount = = Request. Amount); }//Time range if (request. Stockinstart = null) {result = result. Where (p = p.created >= request. Stockinstart); }//Time range if (request. Stockinend = null) {result = result. Where (p = p.created . OrderByDescending (p = p.created) . Skip (Request. PAGEINDEX-1

Label:--Get the stored procedure creation statementSelect O.XTYPE,O.NAME,CM.TextFromsyscomments cmInnerJoin sysobjects oOn O.id=Cm.idwhere xtype=‘P‘Orderby O.XTYPE,O.NAME,CM.Text--Get View Path Creation statementSelect O.XTYPE,O.NAME,CM.TextFromsyscomments cmInnerJoin sysobjects oOn O.id=Cm.idwhere xtype=‘V‘Orderby O.XTYPE,O.NAME,CM.Text--Query all table names, field names, types, lengthsSelect O.name, C.name,t.name,c.lengthFromsyscolumns CInnerJoin Systypes TOn C.xtype=T.xtypeInnerJoin sysobjec

Tags: Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced. MyBatis-like fuzzy queries in Oracle, MySQL, DB2, SQL Server

displayed at a timeExample: Find out the displacement of the car between 400,000 to 500,000ORDER BY clause: ORDER BY column nameNOTE: ASC arranges desc in descending order by ascendingExample 1: Sort by car price in ascending orderExample 2: According to the car's fuel consumption in descending order, the same as fuel consumption, by car price descending orderNote: If a table has the price of the fruit and the inventory of the two columns, but there is no cost this column, to calculate the cost

SQL query This thing, to say that it is simple, can be very simple, usually only need to use additions and deletions to the programming language with the logical expression ability, you can achieve all functions. But additions and deletions do not represent all of the SQL statements, and the full SQL functionality will be daunting to others. Take more than ordina

SQL query This thing, to say that it is simple, can be very simple, usually only need to use additions and deletions to the programming language with the logical expression ability, you can achieve all functions. But additions and deletions do not represent all of the SQL statements, and the full SQL functionality will be daunting to others. Take more than ordina