修改一下:select sum(case a2 when 'A' then 1 else 0 end) as gname、 sum(case a3 when 'B' then 1 else 0 end ) as gvalue into c from a
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
是这个意思吧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
仔细再看,happydreamer(小黑) 才对
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 ) bdrop table t
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
select * into c from(select a2 gname,count(*) gvalue from where a2='A' union all select a2 gname,count(*) gvalue from where a2='B')
我上面那个是写错了。 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
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"区别
结果: gname gvalue A2n 3 B2n 1 A3n 2 B3n 2
可能我的问题提的不太明确,我再重提一下: 现有表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语句如何来实现?
呵呵,每完了?!不过这个简单:select a2 as gname ,count(*) as gvalue into #c from a group by a2
select a2 as gname ,count(*) as gvalue into c from a group by a2
Trunc Table C select a2 as gname ,count(*) as gvalue into c from a group by a2
sum(case a3 when 'B' then 1 else 0 end ) as gvalue into c from a
union select 'a3n' as gname, count(*) as gvalue from a where a3='B'
into c
(
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
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
) bdrop table t
理解了我的意思,我试一下先...
如果连 a2n、a3n 等都是动态产生的:
例如上例中我不使用a2n、a3n作为新表的字段gname 的内容,
而是利用原 a 表中的a2、a3字段中的重复出现的内容('A'、'B'等)为gname 的内容,
那将该如何来写sql 语句呢????各位高手能写出来吗?
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
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
(
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"区别
gname gvalue
A2n 3
B2n 1
A3n 2
B3n 2
现有表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语句如何来实现?
a2 as gname ,count(*) as gvalue
into #c
from a
group by a2
a2 as gname ,count(*) as gvalue
into c
from a
group by a2
select
a2 as gname ,count(*) as gvalue
into c
from a
group by a2