这样? select (select count(1) from 表名)'总数量',sort,count(1)'数量' from 表名 group by sort
select 类别, count(*) as 类别记录数, (select count(1) from T) as 总记录数 from T a
select 类别, count(*) as 类别记录数, (select count(1) from T) as 总记录数 from T a group by 类别--少了组
create proc proc_name as begin select 类别,count(1) as [count] from table group by 类别 end 最好返回一个表。----------------那只能用函数
返回整个表记录select ID, 类别, (select count(*) from T where 类别=a.类别)as 类别记录数, (select count(1) from T) as 总记录数 from T a
count(id) 返回表中数据总数 group by,having子句来统计类别数据
create proc pt_slasbeginselect 商品编号,count(1) '数量' from 进货表 group by 商品编号end exec pt_sl用这个执行出来的,会出现好几表啊
应用 happyflystone 朋友的用法。select * from 进货表 drop proc pt_slcreate proc pt_slasbeginselect 商品编号,count(商品编号) '数量' from 进货表 group by 商品编号end exec pt_sl--结果SP001 3 SP002 2 SP003 2
create table ta(id int,sort int,content varchar(10)) insert ta select 1,1,'a' insert ta select 1,2,'a' insert ta select 1,3,'a' insert ta select 1,4,'a' insert ta select 1,5,'a' insert ta select 1,6,'a' insert ta select 1,7,'a' insert ta select 1,8,'a' insert ta select 1,9,'a' insert ta select 1,2,'b'go declare @i int declare @s varchar(8000) set @I = (select count(1) from ta )set @s = 'select '+ltrim(@i)+ ' as [总数量]' select @s = @s +',[sort'+ltrim(sort)+']= sum(case when sort = '+ltrim(sort) +' then 1 else 0 end)' from (select distinct sort from ta) aexec( @s + ' from ta ')drop table ta/* 总数量 sort1 sort2 sort3 sort4 sort5 sort6 sort7 sort8 sort9 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 10 1 2 1 1 1 1 1 1 1*/
这样?
select (select count(1) from 表名)'总数量',sort,count(1)'数量' from 表名 group by sort
类别,
count(*) as 类别记录数,
(select count(1) from T) as 总记录数
from
T a
类别,
count(*) as 类别记录数,
(select count(1) from T) as 总记录数
from
T a
group by 类别--少了组
as
begin
select 类别,count(1) as [count] from table group by 类别
end
最好返回一个表。----------------那只能用函数
ID,
类别,
(select count(*) from T where 类别=a.类别)as 类别记录数,
(select count(1) from T) as 总记录数
from
T a
group by,having子句来统计类别数据
drop proc pt_slcreate proc pt_slasbeginselect 商品编号,count(商品编号) '数量' from 进货表 group by 商品编号end exec pt_sl--结果SP001 3
SP002 2
SP003 2
insert ta select 1,1,'a'
insert ta select 1,2,'a'
insert ta select 1,3,'a'
insert ta select 1,4,'a'
insert ta select 1,5,'a'
insert ta select 1,6,'a'
insert ta select 1,7,'a'
insert ta select 1,8,'a'
insert ta select 1,9,'a'
insert ta select 1,2,'b'go
declare @i int
declare @s varchar(8000)
set @I = (select count(1) from ta )set @s = 'select '+ltrim(@i)+ ' as [总数量]'
select @s = @s +',[sort'+ltrim(sort)+']= sum(case when sort = '+ltrim(sort) +' then 1 else 0 end)'
from (select distinct sort from ta) aexec( @s + ' from ta ')drop table ta/*
总数量 sort1 sort2 sort3 sort4 sort5 sort6 sort7 sort8 sort9
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
10 1 2 1 1 1 1 1 1 1*/