SELECT specID1, specID1 AS specID2
FROM Table1
GROUP BY specID1可以吗,你说的不是很清楚
FROM Table1
GROUP BY specID1可以吗,你说的不是很清楚
解决方案 »
- 关于一个带参数的存储过程
- Microsoft SQL Server 2000 Enterprise Edition服务器组件在此操作系统上不受支持
- 有法律效用的数字签名大家是如何实现的?
- sql server 2000 在win7下是否兼容??
- 应聘Sql Server
- Access连接SQL Server 2000数据库问题。(高分相送)
- 能建数据库视图as MDX在cube查询的结果集吗?
- 哪位做过IBM DB2的数据库备份,有什么好的备份方案?一定给分!
- 中国程序员,你有多少成就感???
- 请问怎样使用触发器在一表删除多条记录时,同时在另表插入多条记录?
- 如何把datetime的分和秒变为“00:00”
- ssqlserver中的关系图的作用!
FROM Table1
GROUP BY specID1
FROM Table1
GROUP BY specID1可是不行!!
where specID1=specID2
group by specID1可是這樣寫好象覺得沒有意思﹖請樓再說明清楚一點﹖﹖
ID con
1 a
1 bID ID1
1 2
如果是这样,用我上面的语句就可以了
FROM Table1
where specID1=specID2
GROUP BY specID1
select a.*,b.bnum from
(select a,count(a) as anum from a group by a) a,
(select b,count(b) as bnum from a group by b) b
where a.a=b.b
Insert into ta
select 1,2
union all select 1,2
union all select 1,null
union all select 2,3
union all select 3,1
union all select 3,1select * from ta--刪除
drop table taselect id,id_num=count(id),num=count(num) from ta group by id
--結果
id id_num num
-------------------
1 3 2
2 1 1
3 2 2
e f
1 2
1 2
1
2 3
3 1
3 1
结果:
a b c
1 3个(e列中有3个1) 2个(f列中有2个1)
2 1个(e列中有1个2) 2个(f列中有2个2)
3 2个(e列中有2个3) 1个(f列中有1个3)
生成的b列是e列中的个数,生成的c列是f列中等于e列的个数
的方法可以了,我写得差了
from
(select id,num=count(id) from ta group by id)a
--結果
a b c
-----------------------------
1 3 2
2 1 2
3 2 1
select specID1, count(specID1),count(*)
FROM Table1
WHERE specID1=specID2
GROUP BY specID1
可是结果不符合我的要求呀,请看青我的问题呀:
原表:
e f
1 2
1 2
1
2 3
3 1
3 1
结果:
a b c
1 3个(e列中有3个1) 2个(f列中有2个1)
2 1个(e列中有1个2) 2个(f列中有2个2)
3 2个(e列中有2个3) 1个(f列中有1个3)
生成的b列是e列中的个数,生成的c列是f列中等于e列的个数
Insert Into AA Values(1,'b', 2)
Insert Into AA Values(1,'c', null)
Insert Into AA Values(2,'d', 3)
Insert Into AA Values(3,'e', 1)
Insert Into AA Values(3,'f', 1)Select B.Specid1,B.N1,C.N2 from
(Select SpecID1,Count(Specid1) as N1 from AA where specid1 is not null Group by SpecID1) B,
(Select SpecID2,Count(Specid2) as N2 from AA where specid2 is not null Group by SpecID2 ) C where (B.SpecID1=C.SpecID2)
SPECID1 N1 N2
1 3 2
2 1 2
3 2 1
Insert into ta
select 1,2
union all select 1,2
union all select 1,null
union all select 2,3
union all select 3,1
union all select 3,1select * from ta
select id,id_num=count(id),num=(select count(1) from ta where num=a.id) from ta a group by id
drop table ta
我和这位兄弟一样,其它的方法也有就是不爽了
select specid1,count(*),(select count(*) from 表名 as t1 group by specid2 having t1.specid2=t2.specid1) from 表名 as t2 group by specid1
没测试的