Ms access kết hợp select top 1 in join năm 2024
Discovering subqueries is one of those "Eureka!" moments. A new landscape opens in front of you, and you can do really useful things such as: Show
What is a subquery?The SELECT query statementThis example shows basic SQL syntax. It returns 3 fields from 1 table, applies criteria, and sorts the results: SELECT CompanyID, Company, City FROM Table1 WHERE (City = "Springfield") ORDER BY Company; The clauses must be in the right order. Line endings and brackets are optional. A subquery is a SELECT query statement inside another query. As you drag fields and type expressions in query design, Access writes a sentence describing what you asked for. The statement is in SQL (see'quell) - Structured Query Language - the most common relational database language, also used by MySQL, SQL Server, Oracle, DB2, FoxPro, dBase, and others. If SQL is a foreign language, you can mock up a query like the subquery you need, switch it to SQL View, copy, and paste into SQL View in your main query. There will be some tidying up to do, but that's the simplest way to create a subquery. Subquery examplesThe best way to grasp subqueries is to look at examples of how to use them. Identifying what is NOT thereA sales rep. wants to hound customers who have not placed any orders in the last 90 days: SELECT Customers.ID, Customers.Company FROM Customers WHERE NOT EXISTS (SELECT Orders.OrderID FROM Orders WHERE Orders.CustomerID = Customers.CustomerID AND Orders.OrderDate > Date() - 90) ; The main query selects two fields (ID and Company) from the Customers table. It is limited by the WHERE clause, which contains the subquery. The subquery (everything inside the brackets) selects Order ID from the Orders table, limited by two criteria: it has to be the same customer as the one being considered in the main query, and the Order Date has to be in the last 90 days. When the main query runs, Access examines each record in the Customers table. To decide whether to include the customer, it runs the subquery. The subquery finds any orders for that customer in the period. If it finds any, the customer is excluded by the NOT EXISTS. Points to note:
Get the value in another recordPeriodically, they read the meter at your house, and send a bill for the number of units used since the previous reading. The previous reading is a different record in the same table. How can they query that? A subquery can read another record in the same table, like this: SELECT MeterReading.ID, MeterReading.ReadDate, MeterReading.MeterValue, (SELECT TOP 1 Dupe.MeterValue FROM MeterReading AS Dupe WHERE Dupe.AddressID = MeterReading.AddressID AND Dupe.ReadDate < MeterReading.ReadDate ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue FROM MeterReading; The main query here contains 4 fields: the primary key, the reading date, the meter value at that date, and a fourth field that is the value returned from the subquery. The subquery returns just one meter reading (TOP 1.) The WHERE clause limits it to the same address, and a previous date. The ORDER BY clause sorts by descending date, so the most recent record will be the first one. Points to note:
TOP n records per groupYou want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query: SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID FROM Orders WHERE Orders.OrderID IN (SELECT TOP 3 OrderID FROM Orders AS Dupe WHERE Dupe.CustomerID = Orders.CustomerID ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID; Points to note:
Year to dateA Totals query easily gives you a total for the current month, but to get a year-to-date total or a total from the same month last year means another calculation from the same table but for a different period. A subquery is ideal for this purpose. SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS MonthAmount, (SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID = OD.OrderID WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1) AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Points to note:
Delete unmatched recordsThe Unmatched Query Wizard (first dialog when you create a new query) can help you identify records in one table that have no records in another. But if you try to delete the unmatched records, Access may respond with, Could not delete from specified tables. An alternative approach is to use a subquery to identify the records in the related table that have no match in the main table. This example deletes any records in tblInvoice that have no matching record in the tblInvoiceDetail table: DELETE FROM tblInvoice WHERE NOT EXISTS (SELECT InvoiceID FROM tblInvoiceDetail WHERE tblInvoiceDetail.InvoiceID = tblInvoice.InvoiceID); Delete duplicate recordsThis example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.) DELETE FROM Table1 WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe WHERE (Dupe.Surname = Table1.Surname) AND (Dupe.FirstName = Table1.FirstName)); Nulls don't match each other, so if you want to treat pairs of Nulls as duplicates, use this approach: DELETE FROM Table1 WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe WHERE ((Dupe.Surname = Table1.Surname) OR (Dupe.Surname Is Null AND Table1.Surname Is Null)) AND ((Dupe.FirstName = Table1.FirstName) OR (Dupe.FirstName Is Null AND Table1.FirstName Is Null))); Aggregation: Counts and totalsInstead of creating a query into another query, you can summarize data with a subquery. This example works with Northwind, to show how many distinct clients bought each product: SELECT Products.ProductID, Products.ProductName, Count(Q.CustomerID) AS HowManyCustomers FROM (SELECT DISTINCT ProductID, CustomerID FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) AS Q INNER JOIN Products ON Q.ProductID = Products.ProductID GROUP BY Products.ProductID, Products.ProductName; Points to note:
Filters and searchesSince subqueries can look up tables that are not in the main query, they are very useful for filtering forms and reports. A Filter or WhereCondition is just a WHERE clause. A WHERE clause can contain a subquery. So, you can use a subquery to filter a form or report. You now have a way to filter a form or report on fields in tables that are not even in the RecordSource! In our first example , the main query used only the Customers table, and the subquery filtered it to those who had no orders in the last 90 days. You could filter the Customers form in exactly the same way: strWhere = "NOT EXISTS (SELECT Orders.OrderID FROM Orders " & _ > "WHERE (Orders.CustomerID = Customers.CustomerID) AND (Orders.OrderDate > Date() - 90))" Forms!Customers.Filter = strWhere > Forms!Cusomters.FilterOn = True DoCmd.OpenReport "Customers", acViewPreview, , strWhere This technique opens the door for writing incredibly powerful searches. Add subqueries to the basic techniques explained in the Search form article, and you can offer a search where the user can select criteria based on any related table in the whole database. The screenshot below is to whet your appetite for how you can use subqueries. The form is unbound, with each tab collecting criteria that will be applied against related tables. The final RESULTS tab offers to launch several reports which don't even have those tables. It does this by dynamically generating a huge WhereCondition string that consists of several subqueries. The reports are filtered by the subqueries in the string. Other examplesThese articles also illustrate the use of subqueries:
ConclusionNow you know how powerful subqueries are, you will also want to know their limitations. Surviving subqueries gives insight to help you trouble-shoot them. |