A=inventory b=productsselect b.id,count(b.id) from b where b.id in (select id from a) group by b.id
也许是我的问题不够详细,二位的回复没能统计出来。 我将两个表的内容罗列一下: Products表 ID Name Model Supplier Price 1 Paper 1-01 Sup-A 0.02 2 Pen s-1 Sup-B 2.00 3 Folder aa-1 Sup-A 15.00 4 Stapler st-99 Sup-A 10.00Inventory表 ProductId InventoryDate Status 1 2003.02.02 n 1 2003.02.02 n 1 2003.03.02 n 2 2003.02.10 n 2 2003.03.01 n 3 2003.03.10 n 3 2003.02.22 n 3 2003.02.22 n 4 2003.02.22 n 4 2003.03.01 n ---------------------------------- 要求查询后的结果: Id Name Model Quantity Price 1 Paper 1-01 3 0.02 2 Pen s-1 2 2.00 3 Folder aa-1 3 15.00 4 Stapler st-99 2 10.00请各位帮帮忙...
Products表 字段: Id Name Model Supplier Price Inventory表 字段:ProductId InventoryDate Status现根据Inventory表中的数据统计Products中的每个产品的仓存量,用以下语句出错,不知何原因。不知各位高手如何写这语句(Select)SELECT ID,NAME,SUM(PRODUCTS.PRICE)-SUM(INVENTORY.STATUS) AS 库存 ; FROM PRODUCTS,INVENTORY ; GROUP BY NAME,INVENTORYDATE ; WHERE PRODUCTS.ID=INVENTORY.ID 我对英文不熟,看不懂你表中的意思,试着给你写了一个,你试一下吧,我没调试过
select inv.ProductId, pro.[Name], pro.Model, pro.Supplier, count(inv.ProductId) as Quantity, pro.Price from Products pro inner join Inventory inv on inv.productid=pro.[id]group by inv.ProductId,pro.Name,pro.Model,pro.Supplier,pro.Price
谢谢 cdshelf(cdshelf),你的可以达到要求, 我根据你的语句修改了一下我原来的句子,无须用到join语句 原来的: select Products.Id,Products.Name,Products.Model,Products.Supplier,count(Inventory.ProductId) as Quantity,Products.Price from Inventory,Products group by Inventory.ProductId修改后的: select Products.Id,Products.Name,Products.Model,Products.Supplier,count(Inventory.ProductId) as Quantity,Products.Price from Inventory,Products where Products.Id=Inventory.ProductId group by Products.Id,Products.Name,Products.Model,Products.Supplier,Products.Price原来在进行分组查询时需要注意需要将查询的列也在分组时指定一下。
b=productsselect b.id,count(b.id) from b where b.id in (select id from a)
group by b.id
我将两个表的内容罗列一下:
Products表
ID Name Model Supplier Price
1 Paper 1-01 Sup-A 0.02
2 Pen s-1 Sup-B 2.00
3 Folder aa-1 Sup-A 15.00
4 Stapler st-99 Sup-A 10.00Inventory表
ProductId InventoryDate Status
1 2003.02.02 n
1 2003.02.02 n
1 2003.03.02 n
2 2003.02.10 n
2 2003.03.01 n
3 2003.03.10 n
3 2003.02.22 n
3 2003.02.22 n
4 2003.02.22 n
4 2003.03.01 n
----------------------------------
要求查询后的结果:
Id Name Model Quantity Price
1 Paper 1-01 3 0.02
2 Pen s-1 2 2.00
3 Folder aa-1 3 15.00
4 Stapler st-99 2 10.00请各位帮帮忙...
字段: Id Name Model Supplier Price
Inventory表
字段:ProductId InventoryDate Status现根据Inventory表中的数据统计Products中的每个产品的仓存量,用以下语句出错,不知何原因。不知各位高手如何写这语句(Select)SELECT ID,NAME,SUM(PRODUCTS.PRICE)-SUM(INVENTORY.STATUS) AS 库存 ;
FROM PRODUCTS,INVENTORY ;
GROUP BY NAME,INVENTORYDATE ;
WHERE PRODUCTS.ID=INVENTORY.ID
我对英文不熟,看不懂你表中的意思,试着给你写了一个,你试一下吧,我没调试过
pro.[Name],
pro.Model,
pro.Supplier,
count(inv.ProductId) as Quantity,
pro.Price from Products pro
inner join Inventory inv on inv.productid=pro.[id]group by inv.ProductId,pro.Name,pro.Model,pro.Supplier,pro.Price
我根据你的语句修改了一下我原来的句子,无须用到join语句
原来的:
select Products.Id,Products.Name,Products.Model,Products.Supplier,count(Inventory.ProductId) as Quantity,Products.Price from Inventory,Products group by Inventory.ProductId修改后的:
select Products.Id,Products.Name,Products.Model,Products.Supplier,count(Inventory.ProductId) as Quantity,Products.Price from Inventory,Products where Products.Id=Inventory.ProductId group by Products.Id,Products.Name,Products.Model,Products.Supplier,Products.Price原来在进行分组查询时需要注意需要将查询的列也在分组时指定一下。