这条SQL语句怎么写？

[icy_csdn]

有一个表：ta
n1　　n2　　n3　　n4　　　'四个字段
a　　　b　　c　　　d
b　　　c　　d　　　e
d　　　c　　a　　　e

每条记录每个字段的值可能是a,b,c,d,e中的一个

现在要统计n1,n2,n3,n4每个字段中a,b,c,d,e的个数
如：
abcde　　n1count　　n2count　　n3count　　n4count
　a　　　　1　　　　　0　　　　　1　　　　　　0
　b　　　　1　　　　　1　　　　　0　　　　　　0
　c　　　　0　　　　　2　　　　　1　　　　　　0
　d　　　　1　　　　　0　　　　　1　　　　　　1
　e　　　　0　　　　　0　　　　　0　　　　　　1

要统计单独的字段很容易，如
select n1,count("n1") as n1count group by n1

但是如何把每个字段的统计结果合在一个表内？
(Access2000,VB6.0)
谢谢！

[junglerover]

你这个稍微有点麻烦，不过其实也简单。按你的意思，首先，要获得一个包含ABCDE全集的视图
作为基础，就是
select n1 as abcde from ta union select n2 from ta
union select n3 from ta union select n4 from ta
然后在这个视图上，LEFT JOIN 各个由例如
select n1,count(n1) from ta group by n1
的视图。完整的SQL语句如下：

select a.abcde, b.n1count, c.n2count, d.n3count, e.n4count
from
(select n1 as abcde from ta union select n2 from ta
union select n3 from ta union select n4 from ta)
as a
left join (select n1, count(n1) as n1count from ta group by n1) as b on a.abcde=b.n1
left join (select n2, count(n2) as n2count from ta group by n2) as c on a.abcde=c.n2
left join (select n3, count(n3) as n3count from ta group by n3) as d on a.abcde=d.n3
left join (select n4, count(n4) as n4count from ta group by n4) as e on a.abcde=e.n4

这样就可以了。

[junglerover]

唉，才发现这条语句ACCESS竟然不认。我是用SQL SERVER 用习惯了。改成这样才行：

select a.abcde, b.n1count, c.n2count, d.n3count, e.n4count
from
(((((select n1 as abcde from ta union select n2 from ta
union select n3 from ta union select n4 from ta)
as a
left join (select n1, count(n1) as n1count from ta group by n1) as b on a.abcde=b.n1)
left join (select n2, count(n2) as n2count from ta group by n2) as c on a.abcde=c.n2)
left join (select n3, count(n3) as n3count from ta group by n3) as d on a.abcde=d.n3)
left join (select n4, count(n4) as n4count from ta group by n4) as e on a.abcde=e.n4)

每一个LEFT JOIN都得加一层括号。已经调试通过了。

[junglerover]

返回结果如下：
abcde n1count n2count n3count n4count
a 1 1
b 1 1
c 2 1
d 1 1 1
e 2

[icy_csdn]

thnak you very much!