500分求教一个巨难的sql语句！！！！

[qinamao]

现有表a，其有字段a1,a2,a3,a4
数据表视图形如：
a1 a2 a3 a4
1 A B 2
2 B A 3
3 A B 1
4 A A 10
...

现在要求将a 中 a2='A'的记录总数 和 a3='B'的记录总数 分别命名为 a2n、a3n,
并且将所得结果存入一个原来并不存在的表 c 中，存入格式为：
c表设定两个字段：gname、gvalue,
gname 字段存放 a2n、a3n 等命名；
gvalue 字段存放 a2n和a3n的值

能否用一个sql语句来实现？

[huxin1012]

select 'a2n' as gname, count(*) as gvalue from a where a2='A'
union select 'a3n' as gname, count(*) as gvalue from a where a3='B'
into c

[happydreamer]

是这个意思吧

select * into #c from
(
select 'a2n' as gname, count(*) as gvalue from #tt where a2='A'
union all
select 'a3n' as gname, count(*) as gvalue from #tt where a3='B'
)a

----
gname gvalue
a2n 3
a3n 2

[alexwoowf]

饥荒：你的这个问题跟刚才你已经结帐的问题有什么区别？

[zqllyh]

仔细再看，happydreamer(小黑) 才对

[yoki]

create table t(a1 int,a2 varchar(10),a3 varchar(10),a4 int)
insert INTO t values(1,'A','B',2)
insert into t values(2,'B','A',3)
insert into t values(3,'A','B',1)
insert into t values(4,'A','A',10)

select b.a2n as gname,b.a3n as gvalue into c from
(
select sum(case a2 when 'A' then 1 else 0 end) as a2n,
sum(case a3 when 'B' then 1 else 0 end )as a3n
from t
) b

drop table t

[qinamao]

happydreamer(小黑)
理解了我的意思，我试一下先...

[yoki]

sorry,会错意了,还是小黑的对

[qinamao]

咱们再深入一点，
如果连 a2n、a3n 等都是动态产生的:
例如上例中我不使用a2n、a3n作为新表的字段gname 的内容，
而是利用原 a 表中的a2、a3字段中的重复出现的内容（'A'、'B'等）为gname 的内容，
那将该如何来写sql 语句呢？？？？

各位高手能写出来吗？

[qinamao]

注意是a2n、a3n 等都是动态产生的！！

[brandyChen]

insert into #c
select *
(
select a2,count(*)
from a
group by a2
having count(*)>1
union all
select a3.count(*)
from a
group by a3
having count(*)>1
) as tempA

[pengdali]

select * into c from(select a2 gname,count(*) gvalue from where a2='A' union all select a2 gname,count(*) gvalue from where a2='B')

[brandyChen]

我上面那个是写错了。
select *
into #c
from
(
select a2,count(*)
from a
group by a2
having count(*)>1
union all
select a3.count(*)
from a
group by a3
having count(*)>1
) as tempA

[fengzeng]

同意小黑！

[Rewiah]

select * into #c
(
select rtrim(a2)+'2n' as gname ,count(*) as gvalue
from a
group by a2
union all
select rtrim(a3)+'3n' as gname ,count(*) as gvalue
from a
group by a2
) as tempA

注意：
1、不必having count(*)>1，没有说一定要大于1
2、a2和a3有重复，用后缀"2n"、"3n"区别

[Rewiah]

结果：
gname gvalue
A2n 3
B2n 1
A3n 2
B3n 2

[qinamao]

可能我的问题提的不太明确，我再重提一下：
现有表a，其有字段a1,a2,a3,a4
数据表视图形如：
a1 a2 a3 a4
1 A B 2
2 B A 3
3 A B 1
4 A A 10
5 C S 2
...

现要建一个新表 c ,她有gname 和 gvlaue 两个字段，
gname的内容是a 中 a2 字段所出现的内容（无重复）
gvalue的内容是a 中 a2中所出现的各不同内容各自的总次数

所的结果如下
gname gvalue
A 3
B 1
C 1


用一个sql语句如何来实现？

[Rewiah]

呵呵，每完了？！

不过这个简单：

select
a2 as gname ,count(*) as gvalue
into #c
from a
group by a2

[Rewiah]

select
a2 as gname ,count(*) as gvalue
into c
from a
group by a2

[wolfAone]

Trunc Table C
select
a2 as gname ,count(*) as gvalue
into c
from a
group by a2

[qinamao]

ok,给分中......