create table godinfo
(
gid int identity(1,1),
godname varchar(30),
store varchar(30),
inTime datetime,
state bit --商品状态,成品1,次品0
)
insert into godinfo
select '笔记本','华夏科技','2008-9-9',0 union
select 'ebook','US','2008-9-9',0 union
select 'IBM','US','2008-9-9',1 union
select 'NOTEPAD','US','2008-9-9',1 union
select 'DESK','US','2008-9-9',1 union
select '移动硬盘','华夏科技','2008-9-9',1
select store,rtrim(sum(case state when 1 then 1 else 0 end)*100/count(1))+'%' as 成品率 from godinfo group by store
drop table godinfo
(
gid int identity(1,1),
godname varchar(30),
store varchar(30),
inTime datetime,
state bit --商品状态,成品1,次品0
)
insert into godinfo
select '笔记本','华夏科技','2008-9-9',0 union
select 'ebook','US','2008-9-9',0 union
select 'IBM','US','2008-9-9',1 union
select 'NOTEPAD','US','2008-9-9',1 union
select 'DESK','US','2008-9-9',1 union
select '移动硬盘','华夏科技','2008-9-9',1
select store,rtrim(sum(case state when 1 then 1 else 0 end)*100/count(1))+'%' as 成品率 from godinfo group by store
drop table godinfo
--有如下表:用来记录产品信息-------------------------------------------
create table godinfo
(
gid int identity(1,1),
godname varchar(30),
store varchar(30),
inTime datetime,
state bit --商品状态,成品1,次品0
)
insert into godinfo
select '笔记本','华夏科技','2008-9-9',0 union
select 'ebook','US','2008-9-9',0 union
select 'IBM','US','2008-9-9',1 union
select 'NOTEPAD','US','2008-9-9',1 union
select 'DESK','US','2008-9-9',1 union
select '移动硬盘','华夏科技','2008-9-9',1
select store,rtrim(sum(case state when 1 then 1 else 0 end)*100/count(1))+'%' as 成品率 from godinfo group by store/*
store 成品率
------------------------------ -------------
US 75%
华夏科技 50%
*/drop table godinfo
select store,count(*) as '产品数量' from godinfo group by store
也能够得到各个厂商的不合格产品的数量,
select store,count(*) as '产品数量' from godinfo where state=0 group by store
但是这两个怎么可以组合到一起呢。
?
AS
SELECT
godname,
SUM(CASE WHEN state=1 THEN 1 ELSE 0 END)*100
/(SELECT COUNT(*)
FROM godinfo
WHERE inTime BETWEEN @start_date AND @end_date)
FROM godinfo
WHERE inTime BETWEEN @start_date AND @end_date
GROUP BY godname
GO
AS
SELECT
godname,
SUM(CASE WHEN state=1 THEN 1 ELSE 0 END)*100/COUNT(*)
FROM godinfo
WHERE inTime BETWEEN @start_date AND @end_date
GROUP BY godname
GO
from godinfo
group by store
from godinfo
group by store