有2个表 想分别求出数据的个数 然后把个数相加
a表
user_id name
1 aaaa
2 aaa
1 aaaa
b表
user_id name
1 aaaa
2 aaa
1 aaaa
2 aaaaa
user_id 是主键 我想求出这样
user_id=1 在a表中条数=2 在b表中条数=2 还要求出和=4
user_id=2 在a表中条数=1 在b表中条数=2 还要求出和=3这个样子的 怎么才能实现
a表
user_id name
1 aaaa
2 aaa
1 aaaa
b表
user_id name
1 aaaa
2 aaa
1 aaaa
2 aaaaa
user_id 是主键 我想求出这样
user_id=1 在a表中条数=2 在b表中条数=2 还要求出和=4
user_id=2 在a表中条数=1 在b表中条数=2 还要求出和=3这个样子的 怎么才能实现
sum(case when id=2 then 1 else 0 end) [B],
count(*)
from (select id=1,* from A表 union all select id=2,* from B表) t
group by user_id
isnull(m.a_cnt,0) a_cnt,
isnull(n.b_cnt,0) b_cnt,
isnull(m.a_cnt,0) + isnull(n.b_cnt,0) cnt
from
(select user_id , count(1) a_cnt from a group by user_id) m
full join
(select user_id , count(1) b_cnt from b group by user_id) n
on m.user_id = n.user_id
insert into a values(1)
insert into a values(2)
insert into a values(1)
create table B([user_id] int)
insert into b values(1)
insert into b values(2)
insert into b values(1)
insert into b values(2)
goselect isnull(m.user_id,n.user_id) user_id,
isnull(m.a_cnt,0) a_cnt,
isnull(n.b_cnt,0) b_cnt,
isnull(m.a_cnt,0) + isnull(n.b_cnt,0) cnt
from
(select user_id , count(1) a_cnt from a group by user_id) m
full join
(select user_id , count(1) b_cnt from b group by user_id) n
on m.user_id = n.user_iddrop table a , b/*
user_id a_cnt b_cnt cnt
----------- ----------- ----------- -----------
1 2 2 4
2 1 2 3(所影响的行数为 2 行)
*/
create table A
(
user_id int,
name varchar(10)
)
create table B
(
user_id int,
name varchar(10)
)insert into A
select 1,'aaaa' union all
select 2,'aaa' union all
select 1,'aaaa'insert into B
select 1,'aaaa' union all
select 2,'aaa' union all
select 1,'aaaa' union all
select 2,'aaaaa'
select t1.user_id,t1.a表中条数,t2.b表中条数,t1.a表中条数 + t2.b表中条数 AS 总和 from (
select user_id,count(name) AS a表中条数 from A group by user_id) t1 left join (select user_id,count(name) AS b表中条数 from B group by user_id) t2 on t1.user_id=t2.user_iduser_id a表中条数 b表中条数 总和
----------- ----------- ----------- -----------
1 2 2 4
2 1 2 3
insert into a values(1)
insert into a values(2)
insert into a values(1)
create table B([user_id] int)
insert into b values(1)
insert into b values(2)
insert into b values(1)
insert into b values(2)select [user_id],count(*) as 出现次数总和 from (select * from a
union all
select * from b)a group by [user_id]user_id 出现次数总和
----------- -----------
1 4
2 3
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-25 11:40:16
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([user_id] int,[name] varchar(4))
insert [a]
select 1,'aaaa' union all
select 2,'aaa' union all
select 1,'aaaa'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([user_id] int,[name] varchar(5))
insert [b]
select 1,'aaaa' union all
select 2,'aaa' union all
select 1,'aaaa' union all
select 2,'aaaaa'
--------------开始查询--------------------------
select
distinct
ISNULL(a.user_id,b.user_id) as user_id,
a.num ,b.num ,a.num+b.num
from
(
select COUNT(user_id)over(partition by [user_id] ) as num ,user_id from a
)a
left join
(
select COUNT(user_id)over(partition by [user_id] ) as num,user_id from b
)b
on
a.user_id=b.user_id
----------------结果----------------------------
/* user_id num num
----------- ----------- ----------- -----------
1 2 2 4
2 1 2 3(2 行受影响)*/
insert into A表 values(1)
insert into A表 values(2)
insert into A表 values(1)
create table B表([user_id] int)
insert into B表 values(1)
insert into B表 values(2)
insert into B表 values(1)
insert into B表 values(2)select user_id,sum(case when id=1 then 1 else 0 end) [A],
sum(case when id=2 then 1 else 0 end) [B],
count(*) 总数
from (select id=1,* from A表 union all select id=2,* from B表) t
group by user_id/*
user_id A B 总数
----------- ----------- ----------- -----------
1 2 2 4
2 1 2 3(2 行受影响)
create table t1
(
userid int,
name varchar(10)
)
insert into t1
select 1, 'aaaa' union all
select 2, 'aaa' union all
select 1, 'aaaa'
create table t2
(
userid int,
name varchar(10)
)
insert into t2
select 1, 'aaaa' union all
select 2, 'aaa' union all
select 1, 'aaaa' union all
select 2, 'aaaaa'
select * from t1
select * from t2select a.userid,a.account as 'A表条数',b.account as 'B表条数',a.account+b.account as '总条数' from
(select userid,COUNT(userid) as account from t1 group by userid) as a inner join
(select userid,COUNT(userid) as account from t2 group by userid) as b on a.userid=b.userid