多表关联问题

[hqs19821108]

表A
docentry
1
2
3
4
5
表B
docentry itemcode
1 aa
1 ab
2 aaa
3 aa
3 aaa
3 adfds
3 adse
4 asfsdf
4 ab
5 asdffdf

表C
docentry itemcode 合格否
1 aa Y
1 ab N
2 aaa N
3 aa Y
3 aaa N
3 adfds Y
3 adse Y
4 asfsdf N
4 ab N
5 asdffd N

我想查询成：
docentry个数 itemcode总个数 itemcode合格数 itemcode不合格数 不合格率
5 10 4 6 60%

[百年树人]

---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([docentry] int)
insert [A]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
if object_id('[B]') is not null drop table [B]
go
create table [B]([docentry] int,[itemcode] varchar(10))
insert [B]
select 1,'aa' union all
select 1,'ab' union all
select 2,'aaa' union all
select 3,'aa' union all
select 3,'aaa' union all
select 3,'adfds' union all
select 3,'adse' union all
select 4,'asfsdf' union all
select 4,'ab' union all
select 5,'asdffdf'
if object_id('[C]') is not null drop table [C]
go
create table [C]([docentry] int,[itemcode] varchar(10),[合格否] varchar(1))
insert [C]
select 1,'aa','Y' union all
select 1,'ab','N' union all
select 2,'aaa','N' union all
select 3,'aa','Y' union all
select 3,'aaa','N' union all
select 3,'adfds','Y' union all
select 3,'adse','Y' union all
select 4,'asfsdf','N' union all
select 4,'ab','N' union all
select 5,'asdffdf','N'
 
---查询---
SELECT 
  [docentry个数]=COUNT(DISTINCT A.docentry),
  [itemcode总个数]=COUNT(*),
  [itemcode合格数]=SUM(CASE WHEN C.合格否='Y' THEN 1 ELSE 0 END), 
  [itemcode不合格数]=SUM(CASE WHEN C.合格否='N' THEN 1 ELSE 0 END),  
  [不合格率]=LTRIM(SUM(CASE WHEN C.合格否='N' THEN 1 ELSE 0 END)*100/COUNT(1))+'%'
FROM A,B,C
WHERE A.docentry=B.docentry 
AND B.docentry=C.docentry AND B.itemcode=C.itemcode

---结果---
docentry个数  itemcode总个数 itemcode合格数 itemcode不合格数 不合格率
----------- ----------- ----------- ------------ -------------
5           10          4           6            60%

(1 行受影响)

[华夏小卒]

--> 测试数据: #tc
if object_id('tempdb.dbo.#tc') is not null drop table #tc
go
create table #tc (docentry int,itemcode varchar(6),合格否 varchar(1))
insert into #tc
select 1,'aa','Y' union all
select 1,'ab','N' union all
select 2,'aaa','N' union all
select 3,'aa','Y' union all
select 3,'aaa','N' union all
select 3,'adfds','Y' union all
select 3,'adse','Y' union all
select 4,'asfsdf','N' union all
select 4,'ab','N' union all
select 5,'asdffd','N'

select 
docentry个数= count(distinct docentry),
itemcode总个数=count(*),
itemcode合格数=sum(case when 合格否='Y' THEN 1 ELSE 0 END),
itemcode不合格数=sum(case when 合格否='N' THEN 1 ELSE 0 END), 
不合格率=sum(case when 合格否='N' THEN 1 ELSE 0 END)*1.0/COUNT(*)
 from #tc


docentry个数  itemcode总个数 itemcode合格数 itemcode不合格数 不合格率
----------- ----------- ----------- ------------ ---------------------------------------
5           10          4           6            0.600000000000

(1 行受影响)

[天-笑]

select a.docentry ,b.items ,B.[itemcode合格数],B.[itemcode不合格数],
B.[itemcode合格数]/NULLIF(b.items ,0) AS [不合格率]
from A 
left join (
    select b.docentry,count(b.itemcode) AS items , 
    sum(case when C.合格否 ='Y' then 1 else 0 end) as [itemcode合格数],
    sum(case when C.合格否 ='N' then 1 else 0 end) as [不合格数]
    from B left join C on b.docentry = c.docentry and b.itemcode = c.itemcode
    group by 
)B ON a.docentry B.docentry

[jaydom]

if OBJECT_ID('ta') is not null
drop table ta
go
create table ta (docentry int)
insert into ta
select 1 union all select 2 union all select 3 union all select 4 union all select 5 

if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (docentry int,itemcode varchar(10))
insert into tb
select  1, 'aa' union all
select  1, 'ab' union all
select  2, 'aaa' union all
select  3, 'aa' union all
select  3, 'aaa' union all
select  3, 'adfds' union all
select  3, 'adse' union all
select  4, 'asfsdf' union all
select  4, 'ab' union all
select  5, 'asdffdf'
if OBJECT_ID('tc') is not null
drop table tc
go
create table tc (docentry int,itemcode varchar(10),合格否 varchar(2))
insert into tc
select   1, 'aa', 'Y' union all
select   1, 'ab', 'N' union all
select   2, 'aaa', 'N' union all
select   3, 'aa', 'Y' union all
select   3, 'aaa', 'N' union all
select   3, 'adfds', 'Y' union all
select   3, 'adse', 'Y' union all
select   4, 'asfsdf', 'N' union all
select   4, 'ab', 'N' union all
select   5, 'asdffd', 'N' 

select COUNT(distinct docentry) as docentry个数, COUNT(itemcode) as itemcode总个数,
       sum(case 合格否 when 'Y' then 1 else 0 end) as  itemcode合格数 ,
       sum(case 合格否 when 'N' then 1 else 0 end) as  itemcode不合格数 ,
       ltrim(ceiling(sum(case 合格否 when 'N' then 1 else 0 end)*100.0/COUNT(itemcode)))+'%' as 不合格率
from tc