declare @a table (id1 int, id2 int, id3 int)
insert @a select 1, 2, 4
insert @a select 1, 1, 2
insert @a select 2, 4, 1
select id,count(*) from (
select id1 as id from @a
union all
select id2 as id from @a
union all
select id3 as id from @a
) a
group by id
insert @a select 1, 2, 4
insert @a select 1, 1, 2
insert @a select 2, 4, 1
select id,count(*) from (
select id1 as id from @a
union all
select id2 as id from @a
union all
select id3 as id from @a
) a
group by id
解决方案 »
- 刚开始学SQL 碰到个难题
- 问一下,怎么把繁体的数据库变成简体的?可以整个数据库导出,不要一个个选吗?在线等!!!!
- sqlserver2005 怎么知道在一段时间内修改了那些表或视图或者存储过程?
- 从sql server 2005 SP1 安装sql server 2005 SP2 补丁时,不能安装数据库服务
- 如何通过存储过程和触发器实现对远程服务器的操作
- sql语句问题
- mssql 怎样才能支持 set lock mode wait # 的语句呢?
- sql拆表问题
- 请问如何创建这样的触发器????
- 为什么我用exists相关子查询 子查询中没有的记录的也会被查出来啊
- 数据文件和事务日志文件的路径
- 事务日志的备份与还原:时点还原,怎么不能用?
,次数=sum(isnull(a.次数,0)+isnull(b.次数,0)+isnull(c.次数,0))
from(
select id1,次数=count(*) from MYTable group by id1
) a full join (
select id2,次数=count(*) from MYTable group by id2
) b on a.id1=b.id2 full join(
select id3,次数=count(*) from MYTable group by id3
) c on a.id1=c.id3
group by isnull(a.id1,isnull(b.id2,c.id3))
insert @a select 1, 2, 4
insert @a select 1, 1, 2
insert @a select 2, 4, 1select 数据=isnull(a.id1,isnull(b.id2,c.id3))
,次数=sum(isnull(a.次数,0)+isnull(b.次数,0)+isnull(c.次数,0))
from(
select id1,次数=count(*) from @a group by id1
) a full join (
select id2,次数=count(*) from @a group by id2
) b on a.id1=b.id2 full join(
select id3,次数=count(*) from @a group by id3
) c on a.id1=c.id3
group by isnull(a.id1,isnull(b.id2,c.id3))
不会是sql = "declare @a table............
.............................................group by id"
然后m_pConn->Excute(sql,....)吧?
select id1 as id from @a
union all
select id2 as id from @a
union all
select id3 as id from @a
) a
group by id
select id,sum(*) from (
select id1 id,count(id1) num from @a group by id1
union all
select id2 id, count(id2) num from @a group by id2
union all
select id3 id, count(id3) num from @a group by id3
) a
group by id
select id,sum(id) from (
select id1 id,count(id1) num from @a group by id1
union all
select id2 id, count(id2) num from @a group by id2
union all
select id3 id, count(id3) num from @a group by id3
) a
group by id
insert into @n select 11,2,1
insert into @n select 1,2,13
insert into @n select 3,11,2
insert into @n select 2,12,11
insert into @n select 11,3,3
insert into @n select 11,2,3
select n,sum(counts) as 次数
from
(
(select i as n,count(i) as counts from @n group by i)
union all
(select ii,count(ii) from @n group by ii)
union all
(select iii,count(iii) from @n group by iii)
) as n
group by n