1.

Existing advertising contract form Orders, In which advertising space and on which days the advertisement needs to be broadcasted

OrderID

Positioncode

Startdate

Enddate

1

A

2015-11-01

2015-11-03

2

C

2015-11-02

2015-11-03

3

B

2015-11-01

2015-11-04

4

A

2015-11-03

2015-11-04

5

C

2015-11-01

2015-11-02

6

B

2015-11-02

2015-11-05

7

A

2015-11-02

2015-11-03

8

A

2015-11-04

2015-11-05

9

C

2015-11-03

2015-11-04

10

C

2015-11-02

2015-11-04

Product list Product, It means that each advertising space can broadcast at most several advertisements every day

Positioncode

Showcount

A

2

B

1

C

3

It is required to find out , Contracts that exceed the number of ad space rotations

Declare @Dup table (TmpDate datetime)
Declare @minDate datetime,@maxDate datetime
SELECT @minDate=MIN(StartDate),@maxDate=Max(EndDate) FROM Orders
WHILE @minDate<=@MaxDate
BEGIN
INSERT INTO @Dup VALUES (@minDate)
SET @minDate=@minDate+1
END SELECT distinct aa.* FROM
Orders aa INNER JOIN (
SELECT Positioncode,tmpdate,count(*) as cnt
FROM Orders a,@Dup b
where tmpdate between a.startdate and a.enddate
group by Positioncode,tmpdate )bb
ON aa.PositionCode=bb.PositionCode AND bb.tmpDate Between aa.StartDate AND aa.ENdDate
INNER JOIN Product cc ON bb.PositionCode=cc.PositionCode
WHERE bb.cnt>cc.showcount
Order by PositionCode
OrderId PositionCode StartDate EndDate
1 A 2015-11-01 2015-11-03
4 A 2015-11-03 2015-11-04
7 A 2015-11-02 2015-11-03
3 B 2015-11-01 2015-11-04
6 B 2015-11-02 2015-11-05

2.

Please use SQL Statements for : from T_GetLargerDebitOccur It is found in the data table that the amount of all months is more than 101 Record of high amount of account in corresponding month .

Please note that : There are many subjects in the table , There are 1-12 The amount of the month .

AccID: Account code ,Occmonth: The month of occurrence ,  DebitOccur: What happened .

AccId Occmonth DebitOccur
101 1 100
102 1 200
103 1 300
101 2 400
102 2 300
103 2 500
101 3 300
104 3 400
NULL NULL NULL
select a.* from T_GetLargerDebitOccur a
,(select Occmonth,max(DebitOccur) Debit101ccur from T_GetLargerDebitOccur where AccID='' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

result :

AccId Occmonth DebitOccur
102 1 200
103 1 300
104 2 500
105 3 400

To be continue...

Arrange interview questions :SQL( Two ) More articles about

  1. Touch event or Answer the interview questions of mobile guard ( Two )

    Touch event or Answer the interview questions of mobile guard ( Two ) Custom control 1. Touch The transmission mechanism of events top View-> Father View-> Son View, Do not handle reverse returns OnInterceptTouchEve ...

  2. 【IT Organize the written interview questions 】 Binary search tree into two-way list

    [ Test description ] The binary search tree into a two-way list For binary search trees , It can be converted into a two-way linked list , among , The left subtree pointer of a node points to the previous node in the linked list , The right subtree pointer points to the next node in the list . Train of thought : Using the idea of recursion , For binary search ...

  3. python Arrange interview questions ( Two )

    1. process , Threads , The definition of coprocessing , What's the difference? A process is the smallest unit of resources allocated by the operating system , One process corresponds to one block CPU A thread is a control unit in a process , yes CPU Minimum unit of scheduling , Threads are independent of each other , The process ends and the thread ends , A process to ...

  4. iOS Develop interview questions ( Two )

    8 The role of categories ? What's the difference between inheritance and class in implementation ? answer :category Can be found in , Add new methods without changing the original code , Only add , Modification cannot be deleted . And if there is a name conflict between the class and the method in the original class , Then the category ...

  5. php Arrange interview questions ( Two )

    Indexes ,desc and explain unset Just delete the variable name

  6. myBatis+Spring+SpringMVC Frame interview questions

    myBatis+Spring+SpringMVC Frame interview questions ( One ) 2018 year 09 month 06 Japan 13:36:01  New wish tree   Read the number 14034 more Category column : SSM   Copyright notice : This article is an original blog article ...

  7. SQL + Python Interview questions : The second ( difficulty : secondary )

    SQL + Python Interview questions : The second ( difficulty : secondary )

  8. iOS Arrange interview questions ( With answers ) Two

    The first interview questions : http://www.cocoachina.com/bbs/read.php?tid-459620.html This interview question is the same : If there is a problem with the answer , Welcome to correct ! 1. answer person Of ret ...

  9. Shangxuetang Java Arrange interview questions

    Blog classification : Classic sharing   1. super() And this() The difference between ? - 6 -  2. Scope public,protected,private, And the difference between not writing ? - 6 -  3. Program the output, such as ...

  10. MyBatis Arrange interview questions

    MyBatis Arrange interview questions 1. What is? MyBatis? answer :MyBatis It's a customizable SQL. Persistence layer framework for stored procedures and advanced mapping . 2. Talk a MyBatis The cache of answer :MyBatis The cache is divided into level 1 cache and level 2 cache ...

Random recommendation

  1. Android Horizontal and vertical screen switching

    ps: Although the present app It's usually a fixed screen orientation , But it's still necessary to understand the method and attention of screen switching . One   Fixed horizontal and vertical screens androidmainfest.xml Set in activoty attribute :android:scree ...

  2. php Composer newspaper ssl Certificate error

    CA Certificate download address :http://curl.haxx.se/docs/caextract.html modify php.ini file openssl.cafile= D:/wamp/php/verify/cace ...

  3. eval() Function USES

    Conditions : With data set data[indx], The dataset contains objects data[index].obj1.pama1. explain : The incoming parameter is var str = 'obj1.pama1', Ask for data[index].obj ...

  4. nsstring Replace

    -(NSString*)searchaAndPlacing:(NSString*)string { NSString *text=[string stringByReplacingOccurrence ...

  5. PhotoSwipe Source code interpretation series ( Two )

    author : Anchor date : 2013 year 12 month 19 Japan explain : This series of articles is a draft , Waiting for later perfection . The source code is jQuery Version of ,code.photoswipe-3.0.5.js 1. The beginning of the code , Just some copyright notices , Nothing ...

  6. laravel Pagination and total number of Paged links with parameters

    <div class="pagers "> <span class="fs pager"> common {{$trades->total() ...

  7. WEB Front end development record PS Common operations

    1. adjacent 2 Shortcut key method for layer merging : First select the upper layer , Press again ctrl+e. 2. Merge multiple layers or groups within a group : Right click in the group , choice “ Convert to intelligent objects ”, Then you can do something else , such as : Capture this group as a picture :ctr ...

  8. auth Module user authentication

    One .auth Module what auth When the module django The user authentication module comes with When we are developing a website , There is no need to design and implement the user system of the website . At this time, we need to implement including user registration . The user login . User authentication . Cancellation . Change the password, etc ...

  9. git Collaborative development of introductory course

    We have introduced the concept of remote warehouse , But there was no further discussion , Just explained how to create a remote warehouse and push the latest work results to the remote warehouse , In fact, remote warehouse is very important for team collaborative development , It's not just the foundation of team collaborative development , It's also code backup ...

  10. 【C++ Primer | 07】 Commonly used algorithm

    The first part Common generic algorithms : find(beg, end, val); equal(beg1, end1, beg2); fill(beg, end, val); fill_n(beg, cnt, val ...