Common query routines for SQL

1. Write a sql: Query the number of users per day of the mall and the daily cumulative number of users date User_count Total_count
2016-12-01 1 1
2016-12-02 2 3
2016-12-03 2 5
2016-12-04 6 (User_count of visits per day Total_count total number of visits) method one: (Is wrong) select
A.date,a.user_conut, (A.user_conut+b.user_conut) Total_count
From
(select Date,count (1) User_count from
User GROUP By date
) A
Join (select Date,count (1) User_count from
User GROUP By date
) B
On A.date-1=b.date method Two: Select Date, COUNT (test) user_count,total_count= (
Select COUNT (Test)
From
Test1 I
where
I.date<=o.date
) from Test1 o Group by date

Method Three:

With CTE as (
SELECT A.date,count (1) as User_count from dbo.test007 as a GROUP by a.date
)
Select A.date,a.user_count, (select SUM (User_count) from the CTE as B where b.date<=a.date) as Total_count from CTE as a

---------------------------------------------------------------2. A SQL takes out the first two records of each category

One SQL takes out the first two records of each category

Wrote

--Generate test data: #T
IF object_id (' tempdb.dbo. #T ') is a not NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR (3), GID int,author varchar, Title varchar (), Date DATETIME)
INSERT into #T
SELECT ' 001 ', 1, ' Jiangjian ', ' SQLServer2005 development Management and application example ', ' 2008-05-10 ' UNION all
SELECT ' 002 ', 1, ' Hu Baijing ', ' SQLServer2005 performance tuning ', ' 2008-03-22 ' UNION all
SELECT ' 003 ', 1, ' Grove GROFF.J.R ', ' SQL Full manual ', ' 2009-07-01 ' UNION all
SELECT ' 004 ', 1, ' Kalendelaney ', ' SQLServer2005 Technology insider Storage engine ', ' 2008-08-01 ' UNION all
SELECT ' 005 ', 2, ' Alex.kriegel.boris.m.trukhnov ', ' SQL Bible ', ' 2007-10-05 ' UNION all
SELECT ' 006 ', 2, ' Freescale Technology product Development Center ', ' SQLServer2000 advanced management and Development ', ' 2007-09-10 ' UNION all
SELECT ' 007 ', 2, ' Hu Baijing ', ' SQLServer2005 database development ', ' 2008-06-15 ' UNION all
SELECT ' 008 ', 3, ' Chen Haoqui ', ' SQLServer2000 stored procedure with XML programming ', ' 2005-09-01 ' UNION all
SELECT ' 009 ', 3, ' Zhao Song ', ' SQLServer2005 system Management Record ', ' 2008-10-01 ' UNION all
SELECT ' 010 ', 3, ' Huang Jintao ', ' SQL Technical Handbook ', ' 2006-01-01 '

--sql query as follows:

--Group by GID, check the last 2 records of date in each group


--1. When the field ID is unique:
SELECT * from #T as-T where ID in (the Select TOP 2 ID from #T WHERE gid=t.gid ORDER by Date DESC)

--2. If the ID is not a momentary:
SELECT * from #T as T where 2> (select COUNT (*) from #T where Gid=t.gid and Date>t.date)

--sql Server 2005 uses the new method

--3. Using Row_number () for grouping of rows
SELECT id,gid,author,title,date
From
(
SELECT Rid=row_number () over (PARTITION by GID ORDER by Date DESC), *
From #T
) as T
WHERE rid<=2

--4. Using apply
SELECT DISTINCT b.*
From #T as a
Cross APPLY
(
SELECT TOP (2) * from #T WHERE a.gid=gid ORDER by Date DESC
) as B


-Results
/*

ID GID Author Title Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003 1 Grove GROFF.J.R. SQL Full Manual 2009-07-01 00:00:00.000
004 1 Kalendelaney SQLServer2005 Technology Insider storage engine 2008-08-01 00:00:00.000
005 2 Alex.kriegel.boris.m.trukhnov SQL Bible 2007-10-05 00:00:00.000
007 2 Hu Baijing SQLServer2005 database development 2008-06-15 00:00:00.000
009 3 Zhao Song SQLServer2005 system Management record 2008-10-01 00:00:00.000
010 3 Huang Jintao SQL Technical manual 2006-01-01 00:00:00.000

(6 rows affected)

The above content is quoted from the Internet

Below is a brief description of the circumstances under which this scenario will be encountered,

For example, there is a need:

A parent class has a lot of subclasses, each sub-class corresponds to more than one content, at this time we need to get the contents of all the subclasses under the parent class, and the rule that each subclass limit two, you can use the above method, the specific usage can be treated differently according to the above methods.

-------------------

SELECT *
From
(select *,row_number () over (partition by ClassID Order by ClassID) as RowNum
--The above using Row_number () first by ClassID classification, and then to each category within the number 1, 2, 3 ...
From TB) as TB1
where rownum<4

--------------------------------

For the first three of the ID 188 and the top three of id189, there are the first three codes for Oracle under each of the following lines.

Best Answer:

selecta.* from

( select row_number()over(partition  by id  order by 排序字段)  as num,* from [ table ] )a  where num<=3

[Sql]select a.* from
(select Row_number () over (partition by Menu_parentid ORDER by Menu_parentid) as num,*
From Dl_menu) a where num<=3
[ERR] Ora-00936:missing expression

Chase Answer

Select A.* from
(select Row_number () over (partition by Menu_parentid ORDER by Menu_parentid) as num,t.*
From Dl_menu T) a where num<=3

For example, there is a classification table category. The table has a,b,c,d four categories

One person in the product CategoryID is associated with category.

Excuse me: How do I get the first piece of data for each category in the People product table?

CREATE table Cate (Cateid int,catename Nvarchar (20)); CREATE table Product (ProductID int,cateid int,productname Nvarchar), INSERT Cate values (1, ' Demoa '), (2, ' demob '), (3, ' Democ '), (4, ' Demod '), INSERT PRODUCT values (the "("), ' PA '), (2,3, ' PB '), (3,2, ' PC '), (4,2, ' PD '), (5,4, ' PE '), (6,3, ' PF '), (7,1, ' PG '), (8,4, ' PH ') SELECT * from Product;/*productid    cateid    ProductName1    1    PA2    3    PB3    2    PC4    2    PD5    4    PE6    3    PF7    1    PG8    4    ph*/

-Method One: Select T.cateid,t.productid,t.productnamefrom (select Cateid,productid,productname, Row_number () over ( Partition by Cateid Order by ProductID ASC) Rowidfrom PRODUCT) T WHERE t.rowid=1--Method II: SELECT cateid,productid,productname< C0/>from product Twhere ProductID in (    SELECT top 1 ProductID from  PRODUCT     WHERE cateid=t.cateid ORDER by Cate ID ASC)/*cateid    ProductID    ProductName1    1    PA2    3    PC3    2    PB4    5    pe*/

Common query routines for SQL