感觉商店表没用,直接SELECT STOREID,COUNT(STOREID) as counts from tablename group by storeid
用个LEFT JOin就好了
如果goods表一条数据是一件商品的话那么就下边 select store.store_id,count(store.store_id) from store left join goods on store.store_id=goods.store_id group by store.store_id如果goods表中有数量字段 select store.store_id,isnull(sum(数量),0) from store left join goods on store.store_id=goods.store_id group by store.store_id
CREATE TABLE Store ( StoreID VARCHAR(16) PRIMARY KEY, StoreName NVARCHAR(32) ) --店铺配置 INSERT INTO Store SELECT '001',N'周六福' UNION ALL SELECT '002',N'爱琳' UNION ALL SELECT '003',N'皓鑫'CREATE TABLE GoodsMaster ( GoodsID VARCHAR(16), StoreID VARCHAR(16), PRIMARY KEY(GoodsID,StoreID) ) ALTER TABLE GoodsMaster ADD CONSTRAINT FK_StoreID FOREIGN KEY(StoreID) REFERENCES Store(StoreID) --主表数据 INSERT INTO GoodsMaster SELECT 'Pro278','001' UNION ALL SELECT 'Pro098', '001' UNION ALL SELECT 'Pxe810', '003'CREATE TABLE GoodsDetail ( GoodsID VARCHAR(16), GoodsName NVARCHAR(32), GoodsQty INT ) --子表数据 INSERT INTO GoodsDetail SELECT 'Pro278',N'千手观音',236 UNION ALL SELECT 'Pro098',N'大展宏图',102 UNION ALL SELECT 'Pxe810',N'福如东海',56 --查询 SELECT s.StoreName N'店名',gd.GoodsName N'品名',ISNULL(gd.GoodsQty,0) N'数量' FROM Store s LEFT JOIN GoodsMaster gm ON s.StoreID = gm.StoreID LEFT JOIN GoodsDetail gd ON gm.GoodsID = gd.goodsID店名 品名 数量 -------------------------------- -------------------------------- ----------- 周六福 大展宏图 102 周六福 千手观音 236 爱琳 NULL 0 皓鑫 福如东海 56
SELECT STOREID,sum(qty) as counts from tablename group by storeid这个是求的商品总数量。
虚拟一下,比如store表的店铺名称字段为:店铺名称; goods表有"商品名称","和商品数量"字段 1.查询各店铺,各商品的数量 select strore.store_id ,store.店铺名称,goods.商品名称,sum(goods.商品数量) form stroe left join on goods group by store.store_id, stroe.店铺名称, goods.商品名称 2.查询各店铺,所有商品和合计数量 select strore.store_id ,store.店铺名称,sum(goods.商品数量) form stroe left join on goods group by store.store_id, stroe.店铺名称 3.查询各店铺的商品种类 select strore.store_id ,store.店铺名称,goods.商品名称,count(goods.商品名称) form stroe left join on goods group by store.store_id, stroe.店铺名称, goods.商品名称 正常sum应该会自动将null转换为0 如果没有转换试试改为下面 将 sum(goods.商品数量) 改为 isnull(sum(goods.商品数量),0)
left join on goods那句写错了, 改成left jion goods on store.stroe_id = goods.stroe_id
group by storeid
select store.store_id,count(store.store_id) from store
left join goods on store.store_id=goods.store_id
group by store.store_id如果goods表中有数量字段
select store.store_id,isnull(sum(数量),0) from store
left join goods on store.store_id=goods.store_id
group by store.store_id
(
StoreID VARCHAR(16) PRIMARY KEY,
StoreName NVARCHAR(32)
)
--店铺配置
INSERT INTO Store
SELECT '001',N'周六福' UNION ALL
SELECT '002',N'爱琳' UNION ALL
SELECT '003',N'皓鑫'CREATE TABLE GoodsMaster
(
GoodsID VARCHAR(16),
StoreID VARCHAR(16),
PRIMARY KEY(GoodsID,StoreID)
)
ALTER TABLE GoodsMaster ADD CONSTRAINT FK_StoreID FOREIGN KEY(StoreID) REFERENCES Store(StoreID)
--主表数据
INSERT INTO GoodsMaster
SELECT 'Pro278','001' UNION ALL
SELECT 'Pro098', '001' UNION ALL
SELECT 'Pxe810', '003'CREATE TABLE GoodsDetail
(
GoodsID VARCHAR(16),
GoodsName NVARCHAR(32),
GoodsQty INT
)
--子表数据
INSERT INTO GoodsDetail
SELECT 'Pro278',N'千手观音',236 UNION ALL
SELECT 'Pro098',N'大展宏图',102 UNION ALL
SELECT 'Pxe810',N'福如东海',56
--查询
SELECT s.StoreName N'店名',gd.GoodsName N'品名',ISNULL(gd.GoodsQty,0) N'数量'
FROM Store s LEFT JOIN GoodsMaster gm ON s.StoreID = gm.StoreID
LEFT JOIN GoodsDetail gd ON gm.GoodsID = gd.goodsID店名 品名 数量
-------------------------------- -------------------------------- -----------
周六福 大展宏图 102
周六福 千手观音 236
爱琳 NULL 0
皓鑫 福如东海 56
group by storeid这个是求的商品总数量。
goods表有"商品名称","和商品数量"字段
1.查询各店铺,各商品的数量
select strore.store_id ,store.店铺名称,goods.商品名称,sum(goods.商品数量)
form stroe
left join on goods
group by store.store_id, stroe.店铺名称, goods.商品名称
2.查询各店铺,所有商品和合计数量
select strore.store_id ,store.店铺名称,sum(goods.商品数量)
form stroe
left join on goods
group by store.store_id, stroe.店铺名称
3.查询各店铺的商品种类
select strore.store_id ,store.店铺名称,goods.商品名称,count(goods.商品名称)
form stroe
left join on goods
group by store.store_id, stroe.店铺名称, goods.商品名称
正常sum应该会自动将null转换为0
如果没有转换试试改为下面
将 sum(goods.商品数量) 改为 isnull(sum(goods.商品数量),0)
改成left jion goods
on store.stroe_id = goods.stroe_id