sql查询语句

/*

苦苦的潜行者:xiaolinyouni

*/



if object_id('student') is not null 

	drop table student

go

create table student(

id int identity(1,1) primary key,

name varchar(50),

class varchar(50),

blood varchar(50))

go

insert student select '张三','1','A'

union all select '李四', '2', 'C'

union all select '王五', '1', 'B'

union all select '黄六', '3', 'D'

union all select '朱八', '2', 'C'

go

--select * from student

select class,blood,count(blood) as num from student group by class,blood

/*

结果



（所影响的行数为 4  行）

class	blood	num

----	-----	---

1	A	1

1	B	1

2	C	2

3	D	1



*/

貼一下結果



create table student(

id INT identity primary key,

name varchar(50),

class varchar(50),

blood varchar(50)

)



insert into student(name,class, blood) values('张三','1','A')

insert into student(name,class, blood) values('李四','2','C')

insert into student(name,class, blood) values('王五','1','B')

insert into student(name,class, blood) values('黄六','3','D')

insert into student(name,class, blood) values('朱八','2','C')

SELECT 

	b.class,

	a.blood,

	COUNT(s.ID) AS num 

FROM 

(SELECT 'A' AS blood UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' )a 

CROSS JOIN 

(SELECT DISTINCT class from student)b 

LEFT JOIN student s ON a.blood=s.blood AND s.class=b.class GROUP BY b.class,a.blood

/*

class	blood	num

1	A	1

1	B	1

1	C	0

1	D	0

2	A	0

2	B	0

2	C	2

2	D	0

3	A	0

3	B	0

3	C	0

3	D	1

*/

DROP TABLE student

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-11-07 11:41:31

-- 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)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[name] varchar(4),[class] int,[blood] varchar(1))

insert [tb]

select 1,'张三',1,'A' union all

select 2,'李四',2,'C' union all

select 3,'王五',1,'B' union all

select 4,'黄六',3,'D' union all

select 5,'朱八',2,'C'

--------------开始查询--------------------------

select

   b.class,

   b.blood,

   COUNT(a.blood) as num

from  

   tb a

full join

   (select * from (select distinct class from tb)a ,(select  distinct blood from tb) b)b

on

   a.class=b.class

and

   a.blood=b.blood

group by

   b.class,

   b.blood



----------------结果----------------------------

/* class       blood num

----------- ----- -----------

1           A     1

2           A     0

3           A     0

1           B     1

2           B     0

3           B     0

1           C     0

2           C     2

3           C     0

1           D     0

2           D     0

3           D     1

警告: 聚合或其他 SET 操作消除了 Null 值。



(12 行受影响)

*/

SELECT 

b.class,a.blood,COUNT(s.ID) AS num

FROM 

(SELECT 'A'  AS blood UNION ALL

SELECT 'B' UNION ALL

SELECT 'C' UNION ALL

SELECT 'D' )a CROSS JOIN  (SELECT  DISTINCT class  from student)b

LEFT JOIN student s ON a.blood=s.blood AND s.class=b.class

GROUP BY b.class,a.blood

select

   b.class,

   SUM(case a.blood when 'a' then 1 else 0 end) as a,

   SUM(case a.blood when 'b' then 1 else 0 end) as b,   

   SUM(case a.blood when 'c' then 1 else 0 end) as c,  

   SUM(case a.blood when 'd' then 1 else 0 end) as d

from  

   (select * from (select distinct class from tb)a ,(select  distinct blood from tb) b)b

left join

   tb a

on

   a.class=b.class

group by

    b.class