| Product Key | Product Name | Product ID | Model Name (the attribute’s key) |
| 210 | HL Road Frame – Black, 58 | FR-R92B-58 | HL Road Frame |
| 607 | HL Road Frame – Black, 58 | FR-R92B-58 | HL Frame |
| Product Key | Product Name | Model Name (the attribute’s key) | Subcategory Key | Subcategory |
| 210 | HL Road Frame – Black, 58 | HL Road Frame | 14 | Road Frames |
| 608 | HL Road Frame – Black, 58 | HL Road Frame | 17 | Touring Frames |
| Model Name | Subcategory Key | Subcategory |
| HL Road Frame | 14 | Road Frames |
| Model Name | Subcategory Key | Subcategory |
| HL Road Frame | 14 | Road Bike Frames |
| Model Name | Subcategory Key | Subcategory | Category Key | Category |
| ML Bike Frames | 14 | Road Frames | 3 | Accessories |
| HL Road Frame | 14 | Road Frames | 3 | Accessories |
| HL Touring Frame | 17 | Touring Frames | 2 | Components |
| Model Name | Subcategory Key | Subcategory | Category Key | Category |
| ML Bike Frames | 14 | Road Frames | 3 | Accessories |
| HL Road Frame | 17 | Touring Frames | 2 | Components |
| HL Touring Frame | 17 | Touring Frames | 2 | Components |
select distinct OrderId
from Northwind.dbo.[Order Details] OD
where
Quantity <l; (select avg(Quantity) * .1
from Northwind.dbo.[Order Details]
where OD.ProductID = ProductID)The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to "OD.ProductID". This reference compares the outer query's "ProductID" with the inner query's "ProductID". When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each "[Order Details]" record. This inner query will calculate the average "Quantity" for the particular "ProductID" for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement. select CompanyName, ContactName, Address,
City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc
)
order by RegionHere you can see the inner query is a correlated subquery because it references "OuterC", which is the table alias for the "Northwind.DBO.Customer" table in the outer query. This inner query uses the "Region" value to calculate the top two customers for the region associated with the row being processed from the outer query. If the "CustomerID" of the outer query is one of the top two customers, then the record is placed in the record set to be returned.select C.CustomerID, Count(*)*75 Rebate
from Northwind.DBO.Customers C
join
Northwind.DBO.Orders O
on c.CustomerID = O.CustomerID
where Datepart(yy,OrderDate) = '1998'
group by C.CustomerId
having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount))
from Northwind.DBO.Orders O
join
Northwind.DBO.[Order Details] OD
on O.OrderID = OD.OrderID
where CustomerID = C.CustomerId
and Datepart(yy,OrderDate) = '1998'
group by O.OrderId
) By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the "CustomerID" from the outer query and the year of the order "Datepart(yy,OrderDate)", to help identify the Order records associated with each customer, that were placed the year '1998'. For these associated records I am calculating the total order amount, for each order, by summing up all the "[Order Details]" records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula "Count(*)*75 ". create table A(A int, S int)
create table B(A int, B int)
set nocount on
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)
update A
set S = (select sum(B)
from B
where A.A = A group by A)
select * from A
drop table A,BHere is the result set I get when I run this query on my machine:A S
----------- -----------
1 1
2 2
3 3In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated. 1.select top m * from tablename where id not in (select top n id from tablename) 2.select top m * into #temp from tablename order by columnname -- 将top m笔插入 set rowcount nselect * from #temp order by columnname desc 3.select top n * from (select top m * from tablename order by columnname) aorder by columnname desc 4.如果tablename里没有其他identity列，那么：select identity(int) id0,* into #temp from tablename 取n到m条的语句为：select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：exec sp_dboption 你的DB名字,'select into/bulkcopy',true 5.如果表里有identity属性，那么简单：select * from tablename where identitycol between n and m
HKEY_CLASSES_ROOT\AppID\{61738644-F196-11D0-9953-00C04FD919C1}
| Parameter | CAT-5 (ISO Class D)100 MHz | CAT-5 100 MHz | CAT-5e 100 MHz |
| Attenuation | 24.0dB | 24.0dB | 24.0dB |
| NEXT | 27.1dB | 27.1dB | 30.1dB |
| PSNEXT | 24.0dB | N/A | 27.1dB |
| ELFEXT | 17.0dB | 17.0dB | 17.4dB |
| PSELFEXT | 14.4dB | 14.4dB | 14.4dB |
| ACR (derived) | 3.1dB | 3.1dB | 6.1dB |
| PSACR (derived) | N/A | N/A | 3.1dB |
| Return Loss | 10.0dB | 8.0dB | 10.0dB |
