34,838
社区成员




create table Student
(
S# varchar(50),
Sname varchar(50),
Ssex varchar(2)
)
insert into Student values ('A001','张三','1')
insert into Student values ('A002','李四','1')
insert into Student values ('A003','王五','0')
create table Course2
(
C# varchar(50),
CName varchar(50),
)
insert into Course2 values('C001','语文')
insert into Course2 values('C002','数学')
insert into Course2 values('C003','英语')
create Table SC
(
S# varchar(50),
C# varchar(50),
Score varchar(50)
)
insert into SC values ('A001','C001','88')
insert into SC values ('A001','C002','89')
insert into SC values ('A001','C003','98')
insert into SC values ('A002','C001','78')
insert into SC values ('A002','C002','97')
insert into SC values ('A003','C001','65')
select * from Student
select * from Course2
select * from SC
select * from Course2 A cross apply SC B where A.C#=B.C#
--查询缺考的学生及缺考的科目。
--思路: 先找出所有学生应该参加那些考试(cross join); 然后再左连接成绩信息,并选择null的项
select * from
(
select * from Student A cross join Course2 B
)t left join SC on t.C#=SC.C# and t.S#=sc.S#
where SC.C# is null
A002 李四 1 C003 英语 NULL NULL NULL
A003 王五 0 C002 数学 NULL NULL NULL
A003 王五 0 C003 英语 NULL NULL NULL
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-15 08:22:35
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] varchar(1))
insert [huang]
select 1,'a' union all
select 2,'b'
--> 测试数据:[numtb]
if object_id('[numtb]') is not null drop table [numtb]
go
create table [numtb]([id] int)
insert [numtb]
select 1 union ALL
select 2
--------------开始查询--------------------------
--select * from [numtb]
select a.* from [huang] a CROSS JOIN [numtb] b
----------------结果----------------------------
/*
id name
----------- ----
1 a
2 b
1 a
2 b
*/