复杂问题！（对我而言）

[andrew999]

表结构如下：
A B
a1 b1
b1 a1
a1 b2
a1 b1
b1 a1
a2 b2
a1 b3
b2 a1
b3 a1
...

想实现如下功能：在A=a1或B=a1条件下，求A,B为a1,b1(即A=a1,B=b1或A=b1,B=a1)....到A,B为a1,bn(即A=a1,B=bn或A=bn,B=a1)的统计数。
请高手指点。

[Jane_64]

根据A,B的分组打印其他列，不明白

[andrew999]

类似：
SELECT A, B, C，COUNT(*) AS times
FROM (SELECT A, B，C
FROM @tb
WHERE A = 'a1'
UNION ALL
SELECT B, A，C
FROM @tb
WHERE B = 'a1') a
GROUP BY A, B

但这样是行不通的。不知怎么整？

[CSDMN]

?

[andrew999]

如上表，统计出来应该是
A B times
a1 b1 4
a1 b2 2
a1 b3 2

[andrew999]

SELECT A, B, COUNT(*) AS times
FROM test
WHERE (A = 'a1') OR
(B = 'a1')
GROUP BY A, B得到
A B times
a1 b1 2
a1 b2 1
a1 b3 1
b1 a1 2
b2 a1 1
b3 a1 1
就是怎么把
a1 b1 2
b1 a1 2
这两行进行累加统计？

[Jane_64]

declare @tb table(A varchar(10),B varchar(10))
insert @tb
select 'a1','b1' union all
select 'b1','a1' union all
select 'a1','b2' union all
select 'a1','b1' union all
select 'b1','a1' union all
select 'a2','b2' union all
select 'a1','b3' union all
select 'b2','a1' union all
select 'b3','a1'

select A,B,times=count(*)
from (select A,B
from @tb
where left(A,1)='a'
union all
select B,A
from @tb
where left(A,1)='b') a
group by A,B

/*
A B times
a1 b1 4
a1 b2 2
a1 b3 2
*/

[Jane_64]

结果是：
A B times
---------------------
a1 b1 4
a1 b2 2
a2 b2 1
a1 b3 2

[wwh999]

你的原表数据呢..? 原始的.把第三列列出来。

[andrew999]

a，b只是字符串常量，并不一定是a，b。

[andrew999]

to：wwh999(印钞机V2.0...开发中....)

原表数据1000多万条，怎么列？
第三列是时间。有关系吗？

[andrew999]

to Jane_64()
好像这样可以：
SELECT A, B, COUNT(*) AS times
FROM (SELECT A, B
FROM @tb
WHERE A = 'a1'
UNION ALL
SELECT B, A
FROM @tb
WHERE B = 'a1') a
GROUP BY A, B

[Jane_64]

to andrew999(休闲谷)：
这样是可以，但你不是说“a，b只是字符串常量，并不一定是a，b。”吗？
这样不是指定'a1'了吗？

你那个字符串常量有规律吗？

[Jane_64]

改成这样吧：
declare @tb table(A varchar(10),B varchar(10))
insert @tb
select 'a1','b1' union all
select 'b1','a1' union all
select 'a1','b2' union all
select 'a1','b1' union all
select 'b1','a1' union all
select 'a2','b2' union all
select 'a1','b3' union all
select 'b2','a1' union all
select 'b3','a1'


SELECT A, B, COUNT(*) AS times
FROM (SELECT a.A, a.B
FROM @tb a,(select top 1 A from @tb) b
WHERE a.A = b.A
UNION ALL
SELECT a.B, a.A
FROM @tb a,(select top 1 A from @tb) b
WHERE a.B = b.a) c
GROUP BY A, B

[andrew999]

to Jane_64()

字符串完全没有规律。

这样可以：
SELECT A, B, COUNT(*) AS times
FROM (SELECT A, B
FROM @tb
WHERE A = 'a1'
UNION ALL
SELECT B, A
FROM @tb
WHERE B = 'a1') a
GROUP BY A, B
但要根据A,B的分组打印其他列，怎么弄？