如下所示的表
-----------------------
num1 num2
a b
a c
a b
b a
c a
a c
-----------------------
将(a,b)和(b,a)视作相同
统计出例如(a,b)或者(b,a)出现的频率?
比如上表中(a,b)出现2次,(b,a)出现1次,即(a,b)或者(b,a)次数为3次 请教了
-----------------------
num1 num2
a b
a c
a b
b a
c a
a c
-----------------------
将(a,b)和(b,a)视作相同
统计出例如(a,b)或者(b,a)出现的频率?
比如上表中(a,b)出现2次,(b,a)出现1次,即(a,b)或者(b,a)次数为3次 请教了
解决方案 »
- SQL Server ERRORlOG文件过大,导致C盘空间为0
- 大家来反向分析下csdn结帖率的SQL算法
- 事务、锁以及存储过程的并发问题
- 如何用sql语句将tableA的数据倒到c:盘下面,并存成文件tableA.csv文件?在线等
- 各位仁兄帮忙解决一个sql问题:有三个字段,对其中一个字段做聚集函数,但要显示三个字段,急急急
- sql server 问题兄弟们帮忙啊55555555。。皱建哥哥看到了一定要进来帮小兄弟一把啊。
- 如何实现sql server2005 每天定时差异备份,在线等
- 转换smalldatetime列
- 一个数据库表中的数据要导到另一个数据库中的表中,用程序怎么实现比较好?
- ADODB.Recordset 错误 好象是连接。麻烦看一下。
- 在线满意度调查,提交要如何实现?
- MS SQL SERVER 无法连接.出现以下报错,求解
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'a', 'c'SELECT num1,num2,COUNT(*) AS CNT
FROM (
SELECT * FROM @TB
UNION ALL
SELECT num2,num1 FROM @TB
) T
GROUP BY num1,num2
/*
num1 num2 CNT
---- ---- -----------
b a 3
c a 3
a b 3
a c 3
*/
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'a', 'c'select [num1+num2],count( 'num1+num2')num
from
(select reverse(num1+num2) as 'num1+num2' from @TB where ascii(num1)<ascii(num2)
union all
select (num1+num2)as 'num1+num2' from @TB where ascii(num1)>ascii(num2))as t
group by [num1+num2](所影响的行数为 6 行)num1+num2 num
--------- -----------
ba 3
ca 3(所影响的行数为 2 行)只适合你这种!!!
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'a', 'c'select [num2+num1],cnt=count(1)
from
(
SELECT [num2+num1]=(case when num1>num2 then num2+num1 else num1+num2 end)
FROM @TB
) B
group by [num2+num1]num2+num1 cnt
--------- -----------
ab 3
ac 3(2 行受影响)
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' select (case when num1>num2 then num2+num1 else num1+num2 end) as[num2+num1],count(case when num1>num2 then num2+num1 else num1+num2 end) as cnt
from @TB
group by case when num1>num2 then num2+num1 else num1+num2 end
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'a', 'c' union all
select 'a','d'select [num2+num1],cnt=count(1)
from
(
SELECT [num2+num1]=(case when num1>num2 then num2+num1 else num1+num2 end)
FROM @TB
) B
group by [num2+num1]
/*----------------
ab 3
ac 3
ad 1
--------------------------------*/
INSERT @TB
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c' UNION ALL
SELECT 'a', 'b' UNION ALL
SELECT 'b', 'a' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'a', 'c' union all
select 'a','d' select [num2+num1],cnt=count(1)
from
(
SELECT [num2+num1]=(case when num1>num2 then num2+num1 else num1+num2 end)
FROM @TB
) B
group by [num2+num1]
/*----------------
ab 3
ac 3
ad 1
--------------------------------*/
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html