类表:classTable CID int主键 CName 类名 产品表:ProTableproduct PID 主键 CID 类IDselect classTable.CName,product.* from product join classTable on product.cid=classTable.cid order by classTable.CName
是所有产品类别和该类别下的产品数量 如CID,CName,Count(PID) as pNum是所有的产品类别啊
-- 类表:classTable -- CID int主键 -- CName 类名 -- 产品表:ProTable -- -- PID 主键 -- CID 类ID -- 想列出所有的产品分类和该类别下的产品数 -- 如何来写这个sql语句 create table #classTable ( CID int , --主键 CName varchar(50) --类名 )insert into #classTable select 1,'办公类' union all select 12,'备件' union all select 13,'半成品' union all select 14,'材料类' union all select 16,'服装类' union all select 17,'食品类' create table #ProTable ( PID char(2), --主键 CID int --类ID )insert into #ProTable select '01',1 union all select '02',1 union all select '03',12 union all select '04',13 union all select '05',14 union all select '06',16 union all select '07',17 union all select '08',12 union all select '09',13 union all select '10',14 union all select '11',16 union all select '12',17--结果 select a.*,b.* from #ProTable a left join #classTable b on (a.cid=b.cid)drop table #ProTable drop table #classTable -------------------(所影响的行数为 6 行) (所影响的行数为 12 行)PID,CID,CID,CName 01,1,1,办公类 02,1,1,办公类 03,12,12,备件 04,13,13,半成品 05,14,14,材料类 06,16,16,服装类 07,17,17,食品类 08,12,12,备件 09,13,13,半成品 10,14,14,材料类 11,16,16,服装类 12,17,17,食品类(所影响的行数为 12 行)
--tryselect *, 产品数=(select count(*) from ProTable where CID=tmp.CID) from classTable as tmp
select classTable.CName,记录数=count(*) from product join classTable on product.cid=classTable.cid group by classTable.CName
create table #classTable ( CID int , --主键 CName varchar(50) --类名 )insert into #classTable select 1,'办公类' union all select 12,'备件' union all select 13,'半成品' union all select 14,'材料类' union all select 16,'服装类' union all select 17,'食品类' create table #ProTable ( PID char(2), --主键 CID int --类ID ) insert into #ProTable select '01',1 union all select '02',1 union all select '03',12 union all select '04',13 union all select '05',14 union all select '06',16 union all select '07',17 union all select '08',12 union all select '09',13 union all select '10',14 union all select '11',16 union all select '12',17select *,产品数=(select count(*) from #ProTable where CID=tmp.CID) from #classTable as tmp--result CID CName 产品数 ----------- -------------------------------------------------- ----------- 1 办公类 2 12 备件 2 13 半成品 2 14 材料类 2 16 服装类 2 17 食品类 2(6 row(s) affected)
-- 类表:classTable -- CID int主键 -- CName 类名 -- 产品表:ProTable -- -- PID 主键 -- CID 类ID -- 想列出所有的产品分类和该类别下的产品数 -- 如何来写这个sql语句 create table #classTable ( CID int , --主键 CName varchar(50) --类名 )insert into #classTable select 1,'办公类' union all select 12,'备件' union all select 13,'半成品' union all select 14,'材料类' union all select 16,'服装类' union all select 17,'食品类' create table #ProTable ( PID char(2), --主键 CID int --类ID )insert into #ProTable select '01',1 union all select '02',1 union all select '03',12 union all select '04',13 union all select '05',14 union all select '06',16 union all select '07',17 union all select '08',12 union all select '09',13 union all select '10',14 union all select '11',16 union all select '12',17--结果 select max(a.cid) as cid,max(b.cname) as cname,count(1) as 记录数 from #ProTable a left join #classTable b on (a.cid=b.cid) group by a.cid drop table #ProTable drop table #classTable -----------------------(所影响的行数为 6 行) (所影响的行数为 12 行)cid,cname,记录数 1,办公类,2 12,备件,2 13,半成品,2 14,材料类,2 16,服装类,2 17,食品类,2(所影响的行数为 6 行)
CID int主键
CName 类名
产品表:ProTableproduct
PID 主键
CID 类IDselect classTable.CName,product.*
from product join classTable on product.cid=classTable.cid
order by classTable.CName
如CID,CName,Count(PID) as pNum是所有的产品类别啊
-- CID int主键
-- CName 类名
-- 产品表:ProTable
--
-- PID 主键
-- CID 类ID
-- 想列出所有的产品分类和该类别下的产品数
-- 如何来写这个sql语句
create table #classTable
(
CID int , --主键
CName varchar(50) --类名
)insert into #classTable
select 1,'办公类' union all
select 12,'备件' union all
select 13,'半成品' union all
select 14,'材料类' union all
select 16,'服装类' union all
select 17,'食品类'
create table #ProTable
(
PID char(2), --主键
CID int --类ID
)insert into #ProTable
select '01',1 union all
select '02',1 union all
select '03',12 union all
select '04',13 union all
select '05',14 union all
select '06',16 union all
select '07',17 union all
select '08',12 union all
select '09',13 union all
select '10',14 union all
select '11',16 union all
select '12',17--结果
select a.*,b.*
from #ProTable a left join #classTable b on (a.cid=b.cid)drop table #ProTable
drop table #classTable
-------------------(所影响的行数为 6 行)
(所影响的行数为 12 行)PID,CID,CID,CName
01,1,1,办公类
02,1,1,办公类
03,12,12,备件
04,13,13,半成品
05,14,14,材料类
06,16,16,服装类
07,17,17,食品类
08,12,12,备件
09,13,13,半成品
10,14,14,材料类
11,16,16,服装类
12,17,17,食品类(所影响的行数为 12 行)
from classTable as tmp
from product join classTable on product.cid=classTable.cid
group by classTable.CName
(
CID int , --主键
CName varchar(50) --类名
)insert into #classTable
select 1,'办公类' union all
select 12,'备件' union all
select 13,'半成品' union all
select 14,'材料类' union all
select 16,'服装类' union all
select 17,'食品类'
create table #ProTable
(
PID char(2), --主键
CID int --类ID
)
insert into #ProTable
select '01',1 union all
select '02',1 union all
select '03',12 union all
select '04',13 union all
select '05',14 union all
select '06',16 union all
select '07',17 union all
select '08',12 union all
select '09',13 union all
select '10',14 union all
select '11',16 union all
select '12',17select *,产品数=(select count(*) from #ProTable where CID=tmp.CID)
from #classTable as tmp--result
CID CName 产品数
----------- -------------------------------------------------- -----------
1 办公类 2
12 备件 2
13 半成品 2
14 材料类 2
16 服装类 2
17 食品类 2(6 row(s) affected)
-- CID int主键
-- CName 类名
-- 产品表:ProTable
--
-- PID 主键
-- CID 类ID
-- 想列出所有的产品分类和该类别下的产品数
-- 如何来写这个sql语句
create table #classTable
(
CID int , --主键
CName varchar(50) --类名
)insert into #classTable
select 1,'办公类' union all
select 12,'备件' union all
select 13,'半成品' union all
select 14,'材料类' union all
select 16,'服装类' union all
select 17,'食品类'
create table #ProTable
(
PID char(2), --主键
CID int --类ID
)insert into #ProTable
select '01',1 union all
select '02',1 union all
select '03',12 union all
select '04',13 union all
select '05',14 union all
select '06',16 union all
select '07',17 union all
select '08',12 union all
select '09',13 union all
select '10',14 union all
select '11',16 union all
select '12',17--结果
select max(a.cid) as cid,max(b.cname) as cname,count(1) as 记录数
from #ProTable a left join #classTable b on (a.cid=b.cid)
group by a.cid
drop table #ProTable
drop table #classTable
-----------------------(所影响的行数为 6 行)
(所影响的行数为 12 行)cid,cname,记录数
1,办公类,2
12,备件,2
13,半成品,2
14,材料类,2
16,服装类,2
17,食品类,2(所影响的行数为 6 行)
marco08解决了我的问题
这就结分