select isnull(m.a , n.b) c , isnull(m.a_cnt, 0) c1 , isnull(n.b_cnt , 0) c2 from (select a , count(a) a_cnt from t group by a) m full join (select b , count(b) b_cnt from t group by a) n on m.a = n.b
select isnull(m.a , n.b) a , isnull(m.a_cnt, 0) c1 , isnull(n.b_cnt , 0) c2 from (select a , count(a) a_cnt from t group by a) m full join (select b , count(b) b_cnt from t group by a) n on m.a = n.b
select isnull(a.a,b.a) as a isnull(a.c1,0) as c1, isnull(b.c2,0) as c2 from ( select a,count(1) as c1 from t group by a ) as a full join ( select b as a,count(1) as c2 from t group by b ) as b on a.a=b.a
--Code select t.a, c1=(select count(a) from @T where a=t.a), c2=(select count(b) from @T where b=t.a) from ( select a from @T union select b from @T ) t --Drop--Result /* a c1 c2 ----------- ----------- ----------- 1 2 3 2 2 1 3 2 1 4 3 2 5 0 3 6 1 0 */
create table #(a int, b int) insert into # select 1, 2 union all select 3, 4 union all select 1, 3 union all select 2, 1 union all select 3, 5 union all select 4, 5 union all select 2, 5 union all select 4, 1 union all select 4, 4 union all select 6, 1 select DISTINCT a,(select Count(1) from # where A.a =A),(SELECT COUNT(1) FROM # where A.a =b) from # A/* 1 2 3 2 2 1 3 2 1 4 3 2 6 1 0(5 行受影响) */
比较下下面语句效率是不是高些:select a, sum(c1) as c1, sum(c2) as c2 from ( select a,cast(1 as int) as c1,cast(0 as int) as c2 from t union all select b as a,cast(0 as int) as c1,cast(1 as int) as c2 from t ) as t group by a
------------------------------------ -- Author: happyflsytone -- Date:2008-09-22 17:15:26 -------------------------------------- Test Data: ta IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(a INT,b INT) Go INSERT INTO ta SELECT 1,2 UNION ALL SELECT 3,4 UNION ALL SELECT 1,3 UNION ALL SELECT 2,1 UNION ALL SELECT 3,5 UNION ALL SELECT 4,5 UNION ALL SELECT 2,5 UNION ALL SELECT 4,1 UNION ALL SELECT 4,4 UNION ALL SELECT 6,1 GO --Start select isnull(a.a,b.b) as a,isnull(a.c,0) c1,isnull(b.c,0) as c2 from ( SELECT a,count(1) as c from ta group by a) a full join (SELECT b,count(1) as c from ta group by b) b on a.a = b.b--Result: /* a c1 c2 ----------- ----------- ----------- 1 2 3 2 2 1 3 2 1 4 3 2 5 0 3 6 1 0(6 行受影响)*/ --End
select a, sum(c1) as c1, sum(c2) as c2 from ( select a,cast(1 as int) as c1,cast(0 as int) as c2 from t union all select b as a,cast(0 as int) as c1,cast(1 as int) as c2 from t ) as t group by a
select qq.a ,c1,c2 from (select a, count(a)as c1 from t where a = b group by a)qq full join (select b,count(b)as c2 from t where a = b group by b)bb on qq.a=bb.b
(select a , count(a) a_cnt from t group by a) m
full join
(select b , count(b) b_cnt from t group by a) n
on m.a = n.b
isnull(a.a,b.a) as a
isnull(a.c1,0) as c1,
isnull(b.c2,0) as c2
from (
select a,count(1) as c1 from t group by a
) as a
full join (
select b as a,count(1) as c2 from t group by b
) as b
on a.a=b.a
select t.a,
c1=(select count(a) from @T where a=t.a),
c2=(select count(b) from @T where b=t.a)
from
(
select a from @T
union
select b from @T
) t
--Drop--Result
/*
a c1 c2
----------- ----------- -----------
1 2 3
2 2 1
3 2 1
4 3 2
5 0 3
6 1 0
*/
insert into #
select 1, 2 union all
select 3, 4 union all
select 1, 3 union all
select 2, 1 union all
select 3, 5 union all
select 4, 5 union all
select 2, 5 union all
select 4, 1 union all
select 4, 4 union all
select 6, 1 select DISTINCT a,(select Count(1) from # where A.a =A),(SELECT COUNT(1) FROM # where A.a =b) from # A/*
1 2 3
2 2 1
3 2 1
4 3 2
6 1 0(5 行受影响)
*/
a,
sum(c1) as c1,
sum(c2) as c2
from (
select a,cast(1 as int) as c1,cast(0 as int) as c2 from t
union all
select b as a,cast(0 as int) as c1,cast(1 as int) as c2 from t
) as t
group by a
-- Author: happyflsytone
-- Date:2008-09-22 17:15:26
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(a INT,b INT)
Go
INSERT INTO ta
SELECT 1,2 UNION ALL
SELECT 3,4 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,5 UNION ALL
SELECT 4,5 UNION ALL
SELECT 2,5 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,4 UNION ALL
SELECT 6,1
GO
--Start
select isnull(a.a,b.b) as a,isnull(a.c,0) c1,isnull(b.c,0) as c2
from (
SELECT a,count(1) as c
from ta
group by a) a
full join
(SELECT b,count(1) as c
from ta
group by b) b
on a.a = b.b--Result:
/*
a c1 c2
----------- ----------- -----------
1 2 3
2 2 1
3 2 1
4 3 2
5 0 3
6 1 0(6 行受影响)*/
--End
select a, sum(c1) as c1, sum(c2) as c2
from (
select a,cast(1 as int) as c1,cast(0 as int) as c2 from t
union all
select b as a,cast(0 as int) as c1,cast(1 as int) as c2 from t
) as t
group by a
(select a, count(a)as c1 from t where a = b group by a)qq
full join
(select b,count(b)as c2 from t where a = b group by b)bb
on
qq.a=bb.b