全完是我个人理解.可能语言上有有错误....我也是才学SQL...
1
SELECT
ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
2 ( SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName
3 FROM Products
查询Produces表原始数据...但是需要得出品种名称..表中CategoryID字段所记录的数据是品种的编号..
2 ( SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName
3 FROM Products
那么要通过品种编号ID得出品种名称.所以在主查询设置所需要查询字段的部分`加入子查询
SELECT
CategoryName
FROM
Categories
WHERE
Categories.CategoryID
=
Products.CategoryID
程序在执行查询过程中.就会执行这个子查询`找到符合条件的数据..然后得到一个返回数据.使用AS把数据放到子定义表CategoryName中.
最后得出结果就会多出一个CategoryName表.`里面装的数据就是当前本条记录的产品品种名称.
完整的例子:
1
SELECT
ProductID, ProductName, SupplierID, CategoryID,
2 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
3 ( SELECT CategoryName FROM Categories
4 WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,
5 ( SELECT CompanyName FROM Suppliers
6 WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
7 FROM Products
8
9
2 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
3 ( SELECT CategoryName FROM Categories
4 WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,
5 ( SELECT CompanyName FROM Suppliers
6 WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
7 FROM Products
8
9