select a,b,count(*) as [count(1)] from tb group by a,b order by a,b,count(*)
--> By dobear_0922(小熊) 2008-11-17 10:30:12 --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([a] varchar(2),[b] varchar(2)) insert [tb] select 'a','b' union all select 'c','a' union all select 'd','a' union all select 'b','a'declare @val varchar(2) set @val='a'select a=@val, b=isnull(nullif(a, @val), b), [sum]=count(*) from [tb] where a=@val or b=@val group by isnull(nullif(a, @val), b) /* a b sum ---- ---- ----------- a b 2 a c 1 a d 1(3 行受影响) */drop table tb
结果为 a b 1 c a 1 d a 1 b a 1 如何能把a,b和b,a算为2次
2楼的方法定义了值为a,但表里有很多数据的,不一定是a,有可能是b,有可能是a 和 b ,我只是举一个小例子
这样? declare @tb table(a varchar(2),b varchar(2)) insert @tb SELECT 'a', 'b' UNION ALL SELECT 'c', 'a' UNION ALL SELECT 'd', 'a' UNION ALL SELECT 'b', 'a'select a,b,sum(c) as [count(1)] from ( select a,b,count(*) as c from @tb where a='a' group by a,b union all select b as a,a as b,count(*) as c from @tb where b='a' group by b,a ) t group by a,b /* a b count(1) ---- ---- ----------- a b 2 a c 1 a d 1 */
create table [tb]([a] varchar(2),[b] varchar(2)) insert [tb] select 'a','b' union all select 'c','a' union all select 'd','a' union all select 'b','a'select case when [a]>[b] then [a] else [b] end +' '+case when [a]>[b] then [b] else [a] end ,count(1) from tb group by case when [a]>[b] then [a] else [b] end +' '+case when [a]>[b] then [b] else [a] enddrop table tb/* ------ ----------- b a 2 c a 1 d a 1(所影响的行数为 3 行) */
create table [tb]([a] varchar(2),[b] varchar(2)) insert [tb] select 'a','b' union all select 'c','a' union all select 'd','a' union all select 'b','a'select a,b ,count(1) from (select case when [a]>[b] then [a] else [b] end as [a] ,case when [a]>[b] then [b] else [a] end as [b] from tb)as tc group by a,bdrop table tb/* a b ---- ---- ----------- b a 2 c a 1 d a 1(所影响的行数为 3 行) */
select a,b ,count(1) from (select case when [a]>[b] then [a] else [b] end as [a] ,case when [a]>[b] then [b] else [a] end as [b] from tb)as tc group by a,b只处理了a,b如果还有两列d,e 和a,b类似,这样的话还是有问题,可不可以不定义a,b
改造下熊的 if object_id('[tb]') is not null drop table [tb] go create table [tb]([a] varchar(2),[b] varchar(2)) go insert [tb] select 'a','b' union all select 'c','a' union all select 'd','a' union all select 'b','a' union all select 'a','a' union all select 'b','b' goselect a=a.a,b=isnull(nullif(b.a,a.a),b.b),[sum]=count(*) from (select a from tb union select b from tb ) a inner join tb b on b.a=a.a or b.b=a.a group by isnull(nullif(b.a,a.a),b.b),a.a/* a a 1 a b 2 a c 1 a d 1 b a 2 b b 1 c a 1 d a 1 */
这样? create function sortChar(@c1 varchar(10),@c2 varchar(10)) returns varchar(100) as begin declare @t table(c1c2 varchar(100)) declare @res varchar(100) insert into @t select @c1 union select @c2 set @res='' select @res=@res+','+c1c2 from @t order by c1c2 return stuff(@res,1,1,'') endselect dbo.sortchar(a,b) ab,count(*) cnt from #tb group by dbo.sortchar(a,b)/* a,b 2 a,c 1 a,d 1 */
select a,b,count(*) as [count(1)] from tb group by a,b order by a,b,count(*)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(2),[b] varchar(2))
insert [tb]
select 'a','b' union all
select 'c','a' union all
select 'd','a' union all
select 'b','a'declare @val varchar(2)
set @val='a'select a=@val, b=isnull(nullif(a, @val), b), [sum]=count(*)
from [tb]
where a=@val or b=@val
group by isnull(nullif(a, @val), b)
/*
a b sum
---- ---- -----------
a b 2
a c 1
a d 1(3 行受影响)
*/drop table tb
a b 1
c a 1
d a 1
b a 1
如何能把a,b和b,a算为2次
declare @tb table(a varchar(2),b varchar(2))
insert @tb
SELECT 'a', 'b' UNION ALL
SELECT 'c', 'a' UNION ALL
SELECT 'd', 'a' UNION ALL
SELECT 'b', 'a'select a,b,sum(c) as [count(1)]
from (
select a,b,count(*) as c
from @tb
where a='a'
group by a,b
union all
select b as a,a as b,count(*) as c
from @tb
where b='a'
group by b,a
) t
group by a,b
/*
a b count(1)
---- ---- -----------
a b 2
a c 1
a d 1
*/
create table [tb]([a] varchar(2),[b] varchar(2))
insert [tb]
select 'a','b' union all
select 'c','a' union all
select 'd','a' union all
select 'b','a'select case when [a]>[b] then [a] else [b] end +' '+case when [a]>[b] then [b] else [a] end ,count(1)
from tb
group by case when [a]>[b] then [a] else [b] end +' '+case when [a]>[b] then [b] else [a] enddrop table tb/*
------ -----------
b a 2
c a 1
d a 1(所影响的行数为 3 行)
*/
create table [tb]([a] varchar(2),[b] varchar(2))
insert [tb]
select 'a','b' union all
select 'c','a' union all
select 'd','a' union all
select 'b','a'select a,b ,count(1)
from (select case when [a]>[b] then [a] else [b] end as [a] ,case when [a]>[b] then [b] else [a] end as [b] from tb)as tc
group by a,bdrop table tb/*
a b
---- ---- -----------
b a 2
c a 1
d a 1(所影响的行数为 3 行)
*/
from (select case when [a]>[b] then [a] else [b] end as [a] ,case when [a]>[b] then [b] else [a] end as [b] from tb)as tc
group by a,b只处理了a,b如果还有两列d,e 和a,b类似,这样的话还是有问题,可不可以不定义a,b
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(2),[b] varchar(2))
go
insert [tb]
select 'a','b' union all
select 'c','a' union all
select 'd','a' union all
select 'b','a' union all
select 'a','a' union all
select 'b','b'
goselect a=a.a,b=isnull(nullif(b.a,a.a),b.b),[sum]=count(*)
from
(select a from tb
union
select b from tb
) a
inner join tb b
on b.a=a.a or b.b=a.a
group by isnull(nullif(b.a,a.a),b.b),a.a/*
a a 1
a b 2
a c 1
a d 1
b a 2
b b 1
c a 1
d a 1
*/
create function sortChar(@c1 varchar(10),@c2 varchar(10))
returns varchar(100)
as
begin
declare @t table(c1c2 varchar(100))
declare @res varchar(100)
insert into @t select @c1
union select @c2
set @res=''
select @res=@res+','+c1c2 from @t order by c1c2
return stuff(@res,1,1,'')
endselect dbo.sortchar(a,b) ab,count(*) cnt from #tb group by dbo.sortchar(a,b)/*
a,b 2
a,c 1
a,d 1
*/