Topic: Consumers from Madrid with fewer than 3 orders
Build table:
Copy Code code as follows:
SET NOCOUNT ON--When SET NOCOUNT is on, the count is not returned (representing the number of rows affected by Transact-SQL statements). Returns the count when SET NOCOUNT is off
Use SY
Go
If OBJECT_ID (' dbo. Orders ') is not null
DROP TABLE dbo. Orders
Go
If OBJECT_ID (' dbo. Customers ') is not null
DROP TABLE dbo. Customers
Go
CREATE TABLE dbo. Customers
(
CustomerID CHAR (5) NOT null primary key,
City varchar (TEN) NOT NULL
);
INSERT INTO dbo. Customers values (' Fissa ', ' Madrid ');
INSERT INTO dbo. Customers values (' Frndo ', ' Madrid ');
INSERT INTO dbo. Customers values (' Krlos ', ' Madrid ');
INSERT INTO dbo. Customers values (' mrphs ', ' Zion ');
CREATE TABLE dbo. Orders
(
OrderID int NOT NULL primary key,
CustomerID CHAR (5) NULL references customers (CustomerID)
)
INSERT INTO dbo. Orders values (1, ' Frndo ');
INSERT INTO dbo. Orders values (2, ' Frndo ');
INSERT INTO dbo. Orders values (3, ' Krlos ');
INSERT INTO dbo. Orders VALUES (4, ' Krlos ');
INSERT INTO dbo. Orders values (5, ' Krlos ');
INSERT INTO dbo. Orders VALUES (6, ' mrphs ');
INSERT INTO dbo. Orders values (7,null);
--------------------------------------------------------------------------------------------------------------- ---------------
Do a problem analysis:
Copy Code code as follows:
SELECT CustomerID as Consumer, COUNT (CustomerID) as Order
FROM dbo. Orders
Where CustomerID in (
SELECT CustomerID
FROM dbo. Customers
WHERE city = ' Madrid ')
GROUP BY CustomerID
Having COUNT (CustomerID) < 3
The result is as shown in the figure:
--the first thought of the answer, suddenly found less than a Madrid from the Fissa order, Fissa order number is 0, so in the Orders table does not appear, so the above writing will be one less.
--overturned the above answer, and thought of the connection with the table, and the use of the internal connection will be the same as the above, so I chose the left connection, as follows:
Copy Code code as follows:
Select C.customerid as Consumer, Count (O.customerid) as Order
FROM dbo. Customers as C left JOIN dbo. Orders as O on c.customerid = O.customerid
where c.city= ' Madrid '
GROUP BY C.customerid
Having count (C.customerid) < 3
The result is as shown in the figure:
--The query discovery is correct.
--Analyze and view left connections without conditions
Copy Code code as follows:
SELECT * FROM dbo. Customers as C left JOIN dbo. Orders as O on c.customerid = O.customerid
Copy Code code as follows:
SELECT * FROM dbo. Customers as C left JOIN dbo. Orders as O on c.customerid = O.customerid
The result is as shown in the figure:
The standard answer given in the book is:
Copy Code code as follows:
Select C.customerid, COUNT (O.orderid) as Numorders
FROM dbo. Customers as C left JOIN dbo. Orders as O on c.customerid = O.customerid
where c.city= ' Madrid '
GROUP BY C.customerid
Having count (O.orderid) < 3
ORDER BY Numorders
The result is as shown in the figure:
-The book gives you just a little more. An order by defines the sort method (sorted in ascending order of the Numorders column)
