表 t_record --记录表
字段 record_id, --记录ID
user_Id, --用户ID
.....表 t_user --用户表
字段 user_id --用户ID
user_sex --性别 (1:男;2:女)
.....要求: 查出在表t_record里有分别有几个男用户和几个女用户(去掉重复的 user_Id).
字段 record_id, --记录ID
user_Id, --用户ID
.....表 t_user --用户表
字段 user_id --用户ID
user_sex --性别 (1:男;2:女)
.....要求: 查出在表t_record里有分别有几个男用户和几个女用户(去掉重复的 user_Id).
count(distinct a.user_id)
from
t_record a
join t_user b on a.user_id=b.user_id
group by b.user_sex
from t_user a,(select user_id from t_record group by user_id) b
where a.user_id = b.userid
from t_record a inner join t_user b on a.user_Id=b.user_Id
group by b.user_sex
from t_record a inner join t_user b on a.user_Id=b.user_Id
select
sum(case when user_sex=1 then cnt else 0 end) as [男],
sum(case when user_sex=2 then cnt else 0 end) as [女]
from(
select b.user_sex,
count(distinct a.user_id) as cnt
from
t_record a
join t_user b on a.user_id=b.user_id
group by b.user_sex
) t
--sinpoal
------------ 测试数据-----------
if object_id('t_record')is not null drop table t_record
create table t_record(record_id int,[user_id] int)
insert t_record
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,3 union all
select 5,2 union all
select 6,2 union all
select 7,3 union all
select 8,4
if object_id('t_user')is not null drop table t_user
create table t_user([user_id] int,user_sex char(2))
insert t_user
select 1,'男' union all
select 2,'女'union all
select 3,'男'union all
select 4,'男'-------sqlstatement----------
select u.user_id,u.user_sex into #yy from
t_record t inner join t_user u on
t.[user_id]=u.[user_id] select user_sex, count(distinct(user_id)) 人数 into #tb from #yy group by user_sexselect
sum (case when user_sex='男' then 人数 else 0 end )as 男,
sum (case when user_sex='女' then 人数 else 0 end ) as 女
from #tb
/*-----result-----
男 女
3 1
*/
1> select * from t_record
2> go
record_id |user_id
-----------|-----------
1| 1
2| 1
3| 2
4| 3
5| 2
6| 2
7| 3
8| 4(8 rows affected)
1> select * from t_user
2> go
user_id |user_sex
-----------|--------
1|1
2|2
3|1
4|1(4 rows affected)
1> select sum(user_sex%2) as [男],sum(1-user_sex%2) as [女]
2> from (select distinct user_id from t_record) a inner join t_user b on a.user_
Id=b.user_Id
3> go
男 |女
-----------|-----------
3| 1(1 rows affected)
1>(1 rows affected)
1>
sum(case when user_sex=1 then 人数 else 0 end) as [男],
sum(case when user_sex=2 then 人数 else 0 end) as [女]
from
(
select
b.user_sex,
count(distinct a.user_id) as 人数
from
t_record a
join t_user b on
a.user_id=b.user_id
group by
b.user_sex
)t