还是ACCESS SQL查询遇到问题了，再次请求帮助！~

samichang

表1
     Class       name        id
    一（1）     张三      10001
   二（6）      李四      10004
   三（4）      王二      10005

。。。。


表二
    id       type
10001    表扬
10001    表扬
10004    金奖
10001    金奖
10005    银奖
10004    表扬
10001    银奖

最终查询结果如下表
Class     total(>=2)     表扬    金奖     银奖
一（1）          1            2          1         1
二（6）          0            2          1           1
三（4）        0                                   1

说明：是按照班级进行统计查询，其中TOTAL 是显示该班级同时获得2个以上的人数

select a.class,
count(  d.id) as total,
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from (表1 a inner join 表2 b on a.id = b.id)
left join ( select distinct id from 表2  group by id having count(*) >= 2) as d on a.id = d.id
group by a.class


这个查询语句在TOTAL处计算有问题，
但结果TOTAL 数值不对

Class     total(>=2)     表扬    金奖     银奖
一（1）          4           2          1         1
二（6）          2           1          1           0
三（4）        0             0         0           1


麻烦高手帮忙修改一下，谢谢！

amazeyeli

[code=sql]USE tempdb
IF OBJECT_ID('tb2') IS NOT NULL
  DROP TABLE tb2;      --> 如果表TEST不为空,删除表
GO  
CREATE TABLE tb2       --> 创建表,字段及类型如下
(
  id    int,
  type  nvarchar(10)
);
GO
INSERT INTO tb2 VALUES --> 向表中插入数据
(1001,'a'),
(1001,'a'),
(1004,'b'),
(1001,'b'),
(1005,'c'),
(1004,'a'),
(1001,'c');
GO
CREATE TABLE tb1       --> 创建表,字段及类型如下
(
  class nvarchar(10),
  name  nvarchar(10),
  id    int
);
GO
INSERT INTO tb1 VALUES --> 向表中插入数据
('11','x','1001'),
('26','y','1004'),
('34','z','1005');
GO
--------------------开始查询--------------------

;with a as
(
  select id,a,b,c
  from tb2
  pivot(COUNT(type) for [type] in (a,b,c))as a
),  
b as
(
  select id,COUNT(1) as total
  from tb2
  group by id
  having COUNT(1) >= 2
)
select *
from tb1 as c
left join b
on c.id=b.id
join a
on c.id=a.id
   
--------------------运行结果--------------------
/*
class      name       id          id          total       id          a           b           c
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11         x          1001        1001        4           1001        2           1           1
26         y          1004        1004        2           1004        1           1           0
34         z          1005        NULL        NULL        1005        0           0           1

(3 行受影响)

*/[/code]

amazeyeli

[code=sql]--------------------开始查询--------------------

select id,a,b,c
from tb2
pivot(COUNT(type) for [type] in (a,b,c))as a

--相当于

select id,
count(case when [type]='a' then [type] end) as a,
count(case when [type]='b' then [type] end) as b,
count(case when [type]='c' then [type] end) as c
from tb2
group by id

--------------------运行结果--------------------
/*
id          a           b           c
----------- ----------- ----------- -----------
1001        2           1           1
1004        1           1           0
1005        0           0           1

(3 行受影响)

id          a           b           c
----------- ----------- ----------- -----------
1001        2           1           1
1004        1           1           0
1005        0           0           1
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)
*/[/code]

amazeyeli

[code=sql]--------------------开始查询--------------------

select id,
sum(case when [type]='a' then 1 end) as a,
sum(case when [type]='b' then 1 end) as b,
sum(case when [type]='c' then 1 end) as c
from tb2
group by id

--结果却有点不一样,看你的需要吧

--------------------运行结果--------------------
/*
id          a           b           c
----------- ----------- ----------- -----------
1001        2           1           1
1004        1           1           NULL
1005        NULL        NULL        1
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)
*/
[/code]

samichang

感谢各位，但答案不是我要的

我要的是：
Class     total(>=2)     表扬    金奖     银奖
一（1）          1           2          1         1
二（6）          1           1          1           0
三（4）          0             0         0           1

主要是TOTAL部分计算，是该班级有荣誉大于等于2个以上的人头数， 两个班级为1，说明这两个班级内各有1个人有2个荣誉，应该是cOUNT 计算的是人头数，
麻烦各位再帮我研究一下算法，非常感谢！！

amazeyeli

[code=sql]--------------------开始查询--------------------

select id,
sum(case when [type]='a' then 1 else 0 end) as a,
sum(case when [type]='b' then 1 else 0 end) as b,
sum(case when [type]='c' then 1 else 0 end) as c
from tb2
group by id

--再改下就一样了,else都忘了写,
--case when 相当于 iif 函数, 当嵌套很多层时, 书写很方便

--------------------运行结果--------------------
/*
id          a           b           c
----------- ----------- ----------- -----------
1001        2           1           1
1004        1           1           0
1005        0           0           1

(3 行受影响)
*/[/code]

amazeyeli

早说嘛,我还以为最后给的是你要的结果呢
[code=sql]--------------------开始查询--------------------

;with a as
(
  select id,a,b,c
  from tb2
  pivot(COUNT(type) for [type] in (a,b,c))as a
),  
b as
(
  select id
  from tb2
  group by id
  having COUNT(1) >= 2
),
c as
(
  select id,COUNT(1) as counts
  from b
  group by id
)  
select *
from tb1 as d
left join c
on d.id=c.id
join a
on d.id=a.id
   
--------------------运行结果--------------------
/*
class      name       id          id          counts      id          a           b           c
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11         x          1001        1001        1           1001        2           1           1
26         y          1004        1004        1           1004        1           1           0
34         z          1005        NULL        NULL        1005        0           0           1

(3 行受影响)
*/
[/code]

amazeyeli

修改下,上面的错了
[code=sql]--------------------开始查询--------------------

;with a as
(
  select id,a,b,c
  from tb2
  pivot(COUNT(type) for [type] in (a,b,c))as a
),  
b as
(
  select id
  from tb2
  group by id
  having COUNT(1) >= 2
),
c as
(
  select a.class,COUNT(1) as count
  from tb1 as a
  join b
  on a.id = b.id
  group by a.class
)  
select *
from tb1 as d
left join c
on d.class = c.class
join a
on d.id = a.id
   
--------------------运行结果--------------------
/*
class      name       id          id          counts      id          a           b           c
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11         x          1001        1001        1           1001        2           1           1
26         y          1004        1004        1           1004        1           1           0
34         z          1005        NULL        NULL        1005        0           0           1

(3 行受影响)
*/
[/code]

samichang

感谢你提供的帮助与支持，你用的语句格式不适合我，我用以下语句终于实现了我要的结果：供参考：

select m.class,sum(iif(n.class is null,0,1)) as total,m.表扬,m.金奖,m.银奖
from
(select a.class,
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from table1 a inner join table2 b on a.id = b.id
group by a.class) as m
left join
(select  table1.class,table1.id
from table1 inner join table2 on  table1.id = table2.id  group by table1.class,table1.id
having count(*) >= 2) as n
on m.class = n.class
group by m.class,m.表扬,m.金奖,m.银奖