sql多表统计查询

[InSearch]

book表：
ID Name Price Author
01 N1 23 A1
02 N2 33 A2
03 N3 26 A5
04 N4 23 A2
05 N5 26 A1

buyer表：
ID bookId sex age
001 01 男 16
002 03 男 19
003 02 女 16
004 03 女 18
005 02 女 19

要求sql查询结果：

价格26的个数 价格23的个数 男购书的人数 女购书的人数 16岁的人数 19岁的人数
2 3 2 3 2 2

[tim_spac]

select
   key='价格'
  ,value=cast(price as varchar)
  ,cnt=count(1) 
from buyer a 
join book b on book.id=bookId 
group by cast(price as varchar)
union
select
   key='性别'
  ,value=cast(sex as varchar)
  ,cnt=count(1) 
from buyer a 
join book b on book.id=bookId 
group by cast(sex as varchar)
union
select
   key='年龄'
  ,value=cast(age as varchar)
  ,cnt=count(1) 
from buyer a 
join book b on book.id=bookId 
group by cast(age as varchar)

将得到如下结果集:
key,value,cnt
价格,26,2
价格,23,3
性别,男,2
性别,女,3
年龄,16,2
年龄,19,2
..
将这样的结果集在你的前台应用中(如Excel or jsp/php..)格式化整理输出

[xiaoxiangqing]

if object_id('book') is not null
drop table book

if object_id('buyer') is not null
drop table buyer

go

create table book(id varchar(10) not null primary key,Name varchar(10) not null,Price int not null,Author varchar(20) not null)
create table buyer(id varchar(10) not null primary key,bookId varchar(10) not null,sex char(2) not null,age int not null)
go

insert book
select '01','N1',23,'A1' union
select '02','N2',33,'A2' union
select '03','N3',26,'A5' union
select '04','N4',23,'A2' union
select '05','N5',26,'A1'
go

insert buyer
select '001','01','男',16 union
select '002','03','男',19 union
select '003','02','女',16 union
select '004','03','女',18 union
select '005','02','女',19

select * from book
/*
ID Name Price Author
01 N1 23 A1
02 N2 33 A2
03 N3 26 A5
04 N4 23 A2
05 N5 26 A1
*/
select * from buyer
/*
ID bookId sex age
001 01 男 16
002 03 男 19
003 02 女 16
004 03 女 18
005 02 女 19
*/

select (select count(*) from book where Price=26) as 价格26的个数,(select count(*) from book where Price=23) as 价格23的个数,(select count(*) from buyer where Sex='男') as 男购书的人数,(select count(*) from buyer where Sex='女') as 女购书的人数,(select count(*) from buyer where Age=16) as [16岁的人数],(select count(*) from buyer where Age=19) as [19岁的人数]
/*
价格26的个数 价格23的个数 男购书的人数 女购书的人数 16岁的人数 19岁的人数
----------- ----------- ----------- ----------- ----------- -----------
2 2 2 3 2 2
*/

[dawugui]

create table book(ID varchar(10), Name varchar(10), Price int, Author varchar(10))
insert into book values('01', 'N1', 23, 'A1') 
insert into book values('02', 'N2', 23, 'A2') 
insert into book values('03', 'N3', 26, 'A5') 
insert into book values('04', 'N4', 23, 'A2') 
insert into book values('05', 'N5', 26, 'A1') 
create table buyer(ID varchar(10), bookId varchar(10), sex varchar(10), age int)
insert into buyer values('001', '01', '男', 16) 
insert into buyer values('002', '03', '男', 19) 
insert into buyer values('003', '02', '女', 16) 
insert into buyer values('004', '03', '女', 18) 
insert into buyer values('005', '02', '女', 19)
go

select 
价格26的个数 = (select count(*) from book where price = 26),
价格23的个数 = (select count(*) from book where price = 23),
男购书的人数 = (select count(*) from buyer where sex = '男'),
女购书的人数 = (select count(*) from buyer where sex = '女'),
[16岁的人数] = (select count(*) from buyer where age = 16),
[19岁的人数] = (select count(*) from buyer where age = 19)

drop table book,buyer 

/*
价格26的个数     价格23的个数     男购书的人数      女购书的人数      16岁的人数      19岁的人数
----------- ----------- ----------- ----------- ----------- -----------
2           3           2           3           2           2

(1 行受影响)
*/