In SQL connection queries, the location of the WHERE keyword is explained _mssql

Because the author is clumsy, and thinking is not rigorous, also really not good at writing SQL statements, Master Please do not laugh, please skip this article directly.

The background is not much introduced, first build the table, insert test data. Fields that all have comments

Copy Code code as follows:

--Doctor's Table
CREATE TABLE Doctor
(
ID INT IDENTITY (1, 1),--id self-growth
Docnumber NVARCHAR not NULL,--Doctor code
Name NVARCHAR not NULL-doctor name
)
Go

--Inserting test data
INSERT into Doctor
VALUES (' 007 ', ' Tom ')
INSERT into Doctor
VALUES (' 008 ', ' John ')
INSERT into Doctor
VALUES (' 009 ', ' Jim ')

--Source list (registration form)
CREATE TABLE Nosource
(
ID INT IDENTITY (1, 1),
Docnumber NVARCHAR not NULL--corresponds to the doctor's code in the Doctor's table
Worktime DATETIME not NULL
)

Go
--Inserting test data
INSERT into Nosource
VALUES (' 007 ', ' 20120819 ')
INSERT into Nosource
VALUES (' 007 ', ' 20120820 ')
INSERT into Nosource
VALUES (' 007 ', ' 20120821 ')
INSERT into Nosource
VALUES (' 008 ', ' 20120821 ')

After the table is built, the test data is OK. Here's what you need to say.

1. Identify the relevant information for each physician and the number of source numbers that the doctor has.

This is simply too simple, may even just learn HelloWorld and a little database based friends will be seriously BS. But the code is still written.

Copy Code code as follows:

--Simple group query can be done
SELECT COUNT (nos.id) as Personnumsouncecount--Total
Dct.id as DocID,
Dct.name,
Dct.docnumber,
Nos.worktime
From Doctor as DCT
Left JOIN Nosource as nos on dct.docnumber = nos.docnumber
GROUP by Dct.id,
Dct.name,
Dct.docnumber,
Nos.worktime

It's really simple. A small grouping can be done. What else do you sell?

That needs to change now, need to match according to the condition: the request number source table's worktime is more than the current date to be effective, otherwise does not match.
If a doctor with a worktime condition does not match, the value of the corresponding Personnumsouncecount field should be 0; for example, Dr. Jim does not have a matching and qualifying number source, and the Personnumsouncecount field value should be 0. Look up to the sky 40 degrees, think can use where keyword filter, and then a one-time query out? Give it a try.

Copy Code code as follows:

SELECT COUNT (nos.id) as Personnumsouncecount--Total
Dct.id,
Dct.name,
Dct.docnumber,
Nos.worktime
From Doctor as DCT
Left JOIN Nosource as nos on dct.docnumber = nos.docnumber
WHERE DATEDIFF (Day, GETDATE (), nos.worktime) > 0
GROUP by Dct.id,
Dct.name,
Dct.docnumber,
Nos.worktime

I believe someone will write the above code. However, after the implementation of the query, the discovery completely does not meet the requirements AH. Even Dr. Jim's basic information and watch records were filtered out and gone. What the hell?

The reason is very simple. The "where" keyword is used after the connection query to filter the data in the result set of the connection query. Because the condition of the right table (the source table) does not match, the data in the left table (Doctor table) is filtered out.

So, the above phenomenon (Dr. Jim's information and records are gone). Is it possible to find out at once? How to achieve it?

In fact, the correct way of writing should be this:

Copy Code code as follows:

SELECT COUNT (nos.id) as Personnumsouncecount--Total
Dct.id,
Dct.name,
Dct.docnumber,
Nos.worktime
From Doctor as DCT
Left JOIN (SELECT *
From Nosource
WHERE DATEDIFF (Day, GETDATE (), worktime) > 0
) as nos on dct.docnumber = nos.docnumber
GROUP by Dct.id,
Dct.name,
Dct.docnumber,
Nos.worktime

To execute again, sure enough, is to meet the requirements of the result. The idea is: just filter the right table, will (using subqueries) filtered result set as the right table of the connection query, and then to connect, group ...

In fact, writing concise and high-performance SQL statements, it is necessary to have a strong logical thinking ability (and mathematics inseparable) and experience. There is a simpler formulation:

Copy Code code as follows:

SELECT sum (case when nos.worktime>getdate then 1 else 0 end) as Personnumsouncecount--Total
Dct.id as DocID,
Dct.name,
Dct.docnumber
From Doctor as DCT
Left JOIN Nosource as nos on dct.docnumber = nos.docnumber
GROUP by Dct.id,
Dct.name,
Dct.docnumber

To explain this, do not know whether people can understand, anyway, the general meaning is this. The author's ability and level of expression is really limited, unavoidably biased, hope that the reader understanding!