--分类表:
CREATE TABLE [GoodType] (
[gt_id] [int] IDENTITY (1, 1) NOT NULL ,
[pgt_id] [int] NULL ,
[gt_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--产品表
CREATE TABLE [Good] (
[g_id] [int] IDENTITY (1, 1) NOT NULL ,
[gt_id] [int] NULL ,
[g_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO求分类名称下产品的数量!
CREATE TABLE [GoodType] (
[gt_id] [int] IDENTITY (1, 1) NOT NULL ,
[pgt_id] [int] NULL ,
[gt_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--产品表
CREATE TABLE [Good] (
[g_id] [int] IDENTITY (1, 1) NOT NULL ,
[gt_id] [int] NULL ,
[g_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO求分类名称下产品的数量!
select 类别名称,sum(数量) as 数量 from GoodType a,
Good b where a.id=b.id group by 类别名称
select 类别名称,sum(数量) from GoodType a,
Good b where a.gid=b.gt_id group by 类别名称
select [gt_id],[gt_name],
[count]=(select count(*) from good where gt_id=t.gt_id )
from goodtype t
a.[gt_name],
sum(数量字段) as 数量
from
GoodType a, Good b
where
a.[gt_id]=b.[g_id]
group by
a.[gt_name]