A表
ID Num RiQi
1 10 2010-1-5
2 8 2010-1-6
3 11 2010-1-6B表
ID Name
1 内存
2 硬盘
用储存过程写了一个查询。2张表联合查询用 A表 left join B 表。网页调用储存过程后,查询的结果,如果B表没有的项目,查询结果不显示。在网页中直接写SQL代码都会显示出来
结果如下
ID Name Num RiQi
1 内存 10 2010-1-5
2 硬盘 8 2010-1-6
3 NULL 11 2010-1-6 这行不显示
ID Num RiQi
1 10 2010-1-5
2 8 2010-1-6
3 11 2010-1-6B表
ID Name
1 内存
2 硬盘
用储存过程写了一个查询。2张表联合查询用 A表 left join B 表。网页调用储存过程后,查询的结果,如果B表没有的项目,查询结果不显示。在网页中直接写SQL代码都会显示出来
结果如下
ID Name Num RiQi
1 内存 10 2010-1-5
2 硬盘 8 2010-1-6
3 NULL 11 2010-1-6 这行不显示
--不要用left join,直接 select * from a,b where a.id = b.id
declare @A表 table (ID int,Num int,RiQi datetime)
insert into @A表
select 1,10,'2010-1-5' union all
select 2,8,'2010-1-6' union all
select 3,11,'2010-1-6'declare @B表 table (ID int,Name varchar(4))
insert into @B表
select 1,'内存' union all
select 2,'硬盘'select a.*,b.Name from @A表 a LEFT OUTER join @B表 b
ON a.ID=b.ID
/*
ID Num RiQi Name
----------- ----------- ----------------------- ----
1 10 2010-01-05 00:00:00.000 内存
2 8 2010-01-06 00:00:00.000 硬盘
3 11 2010-01-06 00:00:00.000 NULL
*/
A表 a INNER JOIN B表 b
ON a.ID = b.ID
ON a.ID=b.ID 把空项去掉都不行。
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_songjiaoSearch]--查询送交明细
@ddbh varchar(8000)=null,--销售客户
@jh varchar(8000)=null,--产品件号
@jm varchar(8000)=null,--产品件名
@stime datetime=null,--送货日期
@etime datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select jianhao,isnull(c.cpjm,'无件名') as cpjm,isnull(c.cpzl,'0') as cpzl,shuliang,riqi,res from songjiao a left join cpmx c on a.jianhao=c.cpjh where 1=1 and (@ddbh is NULL or c.cpto like '%'+@ddbh+'%') and (@jh is NULL or jianhao like '%'+@jh+'%') and (@jm is NULL or c.cpjm like '%'+@jm+'%')and (@stime is NULL or riqi between convert(varchar,@stime,120) and convert(varchar,@etime,120)) order by riqi desc
END
insert into songjiao
select 1,10,'2010-1-5',0 union all
select 2,8,'2010-1-6',0 union all
select 3,11,'2010-1-6',0
CREATE table cpmx(cpjh int,cpjm varchar(4),cpzl varchar(4),cpto varchar(10))
insert into cpmx
select 1,'内存','1','025' union all
select 2,'硬盘','1','025'
--假设了一些数据.
go
CREATE PROCEDURE [dbo].[pro_songjiaoSearch]--查询送交明细
@ddbh varchar(8000)=null,--销售客户
@jh varchar(8000)=null,--产品件号
@jm varchar(8000)=null,--产品件名
@stime datetime=null,--送货日期
@etime datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select jianhao,isnull(c.cpjm,'无件名') as cpjm,isnull(c.cpzl,'0') as cpzl,shuliang,riqi,res
from songjiao a left join cpmx c on a.jianhao=c.cpjh
where 1=1 and (@ddbh is NULL or c.cpto like '%'+@ddbh+'%')
and (@jh is NULL or jianhao like '%'+@jh+'%')
and (@jm is NULL or c.cpjm like '%'+@jm+'%')
and (@stime is NULL or riqi between convert(varchar,@stime,120)
and convert(varchar,@etime,120))
order by riqi desc
END
go
exec pro_songjiaoSearch
/*
jianhao cpjm cpzl shuliang riqi res
----------- ---- ---- ----------- ----------------------- -----------
2 硬盘 1 8 2010-01-06 00:00:00.000 0
3 无件 0 11 2010-01-06 00:00:00.000 0
1 内存 1 10 2010-01-05 00:00:00.000 0
*/
exec pro_songjiaoSearch '025'
/*
jianhao cpjm cpzl shuliang riqi res
----------- ---- ---- ----------- ----------------------- -----------
2 硬盘 1 8 2010-01-06 00:00:00.000 0
1 内存 1 10 2010-01-05 00:00:00.000 0
*/
--你的表设计得有问题,销售客户应该在songjiao表中.
--或者说,如果对left join 的右表给定条件,那它与左表连接的记录就必须满足该条件,如果右表没有的,左表也不会显示.
--换句话说,where条件并不管是什么连接,只要不符合条件,都不会出现在记录集中.
--如果你把cpmx中硬盘改其他字符,则只能显示一条记录了.
go
drop procedure pro_songjiaoSearch
drop table songjiao,cpmx
CREATE table songjiao(jianhao int,shuliang int,riqi datetime,res int)
insert into songjiao
select 1,10,'2010-1-5',0 union all
select 2,8,'2010-1-6',0 union all
select 3,11,'2010-1-6',0
CREATE table cpmx(cpjh int,cpjm varchar(8),cpzl varchar(4),cpto varchar(10))
insert into cpmx
select 1,'内存','1','025' union all
select 2,'硬盘','1','053'
--假设了一些数据.
go
CREATE PROCEDURE [dbo].[pro_songjiaoSearch]--查询送交明细
@ddbh varchar(8000)=null,--销售客户
@jh varchar(8000)=null,--产品件号
@jm varchar(8000)=null,--产品件名
@stime datetime=null,--送货日期
@etime datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select jianhao,isnull(c.cpjm,'无件名') as cpjm,isnull(c.cpzl,'0') as cpzl,shuliang,riqi,res
from songjiao a left join cpmx c on a.jianhao=c.cpjh
where 1=1 and (@ddbh is NULL or c.cpto like '%'+@ddbh+'%' or c.cpto is null) --此处将cpto设置为可为NULL
and (@jh is NULL or jianhao like '%'+@jh+'%')
and (@jm is NULL or c.cpjm like '%'+@jm+'%')
and (@stime is NULL or riqi between convert(varchar,@stime,120)
and convert(varchar,@etime,120))
order by riqi desc
END
go
exec pro_songjiaoSearch '025'
/*
jianhao cpjm cpzl shuliang riqi res
----------- -------- ---- ----------- ----------------------- -----------
3 无件名 0 11 2010-01-06 00:00:00.000 0
1 内存 1 10 2010-01-05 00:00:00.000 0
*/
go
drop procedure pro_songjiaoSearch
drop table songjiao,cpmx