不用那么复杂吧?----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-23 12:28:10
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t_thesis]
if object_id('[t_thesis]') is not null drop table [t_thesis]
go
create table [t_thesis]([thid] int,[title] nvarchar(6),[status] int)
insert [t_thesis]
select 1,'qqq',1 union all
select 2,'eee',1 union all
select 3,'rrr',0
--> 测试数据[t_th_stu]
if object_id('[t_th_stu]') is not null drop table [t_th_stu]
go
create table [t_th_stu]([thid] int,[sid] int)
insert [t_th_stu]
select 1,32
--------------生成数据--------------------------
select a.thid,a.title,ISNULL([SID],0)[SID]
from [t_thesis] a LEFT JOIN [t_th_stu] b ON a.thid=b.thid
WHERE a.[STATUS]=1
----------------结果----------------------------
/*
thid title SID
----------- ------ -----------
1 qqq 32
2 eee 0
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-23 12:28:10
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[t_thesis]
if object_id('[t_thesis]') is not null drop table [t_thesis]
go
create table [t_thesis]([thid] int,[title] nvarchar(6),[status] int)
insert [t_thesis]
select 1,'qqq',1 union all
select 2,'eee',1 union all
select 3,'rrr',0
--> 测试数据[t_th_stu]
if object_id('[t_th_stu]') is not null drop table [t_th_stu]
go
create table [t_th_stu]([thid] int,[sid] int)
insert [t_th_stu]
select 1,32
--------------生成数据--------------------------
select a.thid,a.title,ISNULL([SID],0)[SID]
from [t_thesis] a LEFT JOIN [t_th_stu] b ON a.thid=b.thid
WHERE a.[STATUS]=1
----------------结果----------------------------
/*
thid title SID
----------- ------ -----------
1 qqq 32
2 eee 0
*/
你应该把 t_thesis.status=1 放在where后面
因为要实现分页功能,所以才写这么长的。 居然忘记把条件写在where子句里面了~~谢谢版主!