很复杂的跨表 SQL 查询，请高手帮忙看一下，感谢！！！！

samichang

table1
Class    name   id
一（1） 张三 10001
二（6） 李四 10004
三（4） 王二 10005
一（1） 张三 10002
table2
     id    type
10001 表扬
10001 表扬
10004 金奖
10001 金奖
10005 银奖
10004 表扬
10001 银奖
10002 银奖
10002 银奖
用以下SQL语句：
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.银奖
结果为：
Class     total         表扬      金奖         银奖
一（1）   2             1             1            2
三（4）   0               0          0              1
二（6）   1             1            1              0
要求按班级统计， total 是该班级里得到2个以上荣誉（一个类型只能算一个）的人头数COUNT，后面的就是各个荣誉的人头数，所以上面查询结果是错的，上面统计的是获得荣誉的个数，没有按照荣誉类型统计
我想要的结果为：
Class     total         表扬      金奖         银奖
一（1）   1             2             1             3
三（4）   0               0          0              1
二（6）   1             1            1              0
请高手帮忙修改一下，感谢！！！！！

    db1.rar (7.47 KB, 下载次数: 10)

2012-12-18 15:36 上传

点击文件名下载附件

amazeyeli

[code=sql]--query1
TRANSFORM COUNT(1)
SELECT CLASS
FROM TABLE1 AS A INNER JOIN TABLE2 AS B ON A.ID=B.ID
GROUP BY CLASS
PIVOT TYPE;

query2
SELECT CLASS, COUNT(1) AS TOTAL
FROM (SELECT CLASS, NAME, COUNT(1) FROM (SELECT DISTINCT * FROM TABLE1 AS A INNER JOIN TABLE2 AS B ON A.ID=B.ID)  AS C GROUP BY CLASS, NAME HAVING COUNT(1)>=2)  AS D
GROUP BY CLASS;

query3
SELECT A.*,B.TOTAL
FROM QUERY1 AS A LEFT JOIN QUERY2 AS B ON A.CLASS=B.CLASS;[/code]

amazeyeli

query2
[code=sql]SELECT CLASS, COUNT(1) AS TOTAL
FROM
(
SELECT CLASS, NAME, COUNT(1)
FROM
(
SELECT DISTINCT *
FROM TABLE1 AS A
INNER JOIN TABLE2 AS B
ON A.ID=B.ID
) AS C
GROUP BY CLASS,NAME
HAVING COUNT(1)>=2
) AS D
GROUP BY CLASS;
[/code]

samichang

谢谢回复，不知道能否用一句查询就解决呢（如下：）？因为我要放到VB中，谢谢！！
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.银奖

amazeyeli

[code=sql]SELECT A.*,B.TOTAL
FROM
(
  select a.class,
             sum(IIf(b.type = '表扬',1,null)) as 表扬,
             sum(IIf(b.type = '金奖',1,null)) as 金奖,
             sum(IIf(b.type = '银奖',1,null)) as 银奖
  from table1 a
  inner join table2 b
  on a.id = b.id
  group by a.class
) AS A
LEFT JOIN
(
  SELECT CLASS, COUNT(1) AS TOTAL
  FROM
  (
        SELECT CLASS, NAME, COUNT(1)
        FROM
        (
          SELECT DISTINCT *
          FROM TABLE1 AS A
          INNER JOIN TABLE2 AS B
          ON A.ID=B.ID
        ) AS C
        GROUP BY CLASS,NAME
        HAVING COUNT(1)>=2
  ) AS D
  GROUP BY CLASS
) AS B
ON A.CLASS=B.CLASS;[/code]

samichang

非常感谢！！我来试一下~

samichang

运行可以通过，不过统计有点问题，比如10001，同时有几个银奖，应该只算1个，这个语句的结果是相同的几个都统计了，请麻烦帮忙再修改一下啊！~谢谢！！

amazeyeli

SELECT DISTINCT *
          FROM TABLE1 AS A
          INNER JOIN TABLE2 AS B
          ON A.ID=B.ID
已经去掉重复了啊

samichang

运行结果统计的是奖励类型数量，一个类型的奖励同时获得几次都被统计了，应该是一个类型的奖励一个人只算一次统计，比如：一个人获得相同的荣誉只算一次，就是计算各类型荣誉的人头数，统计一个班里面同时有两种类型以上荣誉的人头数，而不是一个班里面获得各类型荣誉数量，如：表扬，10001获得了10次，统计时候以表扬为类型筛选，就是一次了~，麻烦你了~

amazeyeli

SELECT DISTINCT *
          FROM TABLE1 AS A
          INNER JOIN TABLE2 AS B
          ON A.ID=B.ID

就是这样啊,去掉重复只有一条记录啊
也许你还有其它字段啊,那你把*换成需要的字段好了

SELECT DISTINCT A.CLASS,A.NAME,A.ID,B.TYPE
          FROM TABLE1 AS A
          INNER JOIN TABLE2 AS B
          ON A.ID=B.ID