本人在查询两个表时写了如下存储过程:
CREATE PROCEDURE Select_mhcustinfo
@yhm nvarchar(25),
@ddh nvarchar(255)
AS
declare @custid int
if @yhm is Null OR ltrim(rtrim(@yhm))=''
begin
select @custid=customerId from custinfo where CONVERT(nvarchar(255),orderid) LIKE '%'+@ddh+'%'
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON CONVERT(nvarchar(255),dbo.custinfo.orderid) LIKE '%'+@ddh+'%' and dbo.Customers.customerId=@custid
order by putdate desc
end
else
begin
select @custid=CustomerId from customers where UserId LIKE '%'+@yhm+'%'
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON dbo.custinfo.CustomerId = @custid and dbo.Customers.UserId LIKE '%'+@yhm+'%'
order by putdate desc
end;
GO现有两张表
1.custinfo
字段 CustomerId orderid ye jf about putdate
值 1 10 0 3 订单10积分3 2005-04-12
1 14 0 0 订单14积分0 2005-04-12
5 17 0 3 订单17积分3 2005-04-12
2.customers
字段 CustomerId UserId
值 1 hxf_wolf
5 bao
在查询时,如果输入定单号(orderid)为"1"时,表1上的三条记录都能查出来,但是得到的
UserId确都是"bao"。请问这是怎么回事,该如何解决?
CREATE PROCEDURE Select_mhcustinfo
@yhm nvarchar(25),
@ddh nvarchar(255)
AS
declare @custid int
if @yhm is Null OR ltrim(rtrim(@yhm))=''
begin
select @custid=customerId from custinfo where CONVERT(nvarchar(255),orderid) LIKE '%'+@ddh+'%'
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON CONVERT(nvarchar(255),dbo.custinfo.orderid) LIKE '%'+@ddh+'%' and dbo.Customers.customerId=@custid
order by putdate desc
end
else
begin
select @custid=CustomerId from customers where UserId LIKE '%'+@yhm+'%'
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON dbo.custinfo.CustomerId = @custid and dbo.Customers.UserId LIKE '%'+@yhm+'%'
order by putdate desc
end;
GO现有两张表
1.custinfo
字段 CustomerId orderid ye jf about putdate
值 1 10 0 3 订单10积分3 2005-04-12
1 14 0 0 订单14积分0 2005-04-12
5 17 0 3 订单17积分3 2005-04-12
2.customers
字段 CustomerId UserId
值 1 hxf_wolf
5 bao
在查询时,如果输入定单号(orderid)为"1"时,表1上的三条记录都能查出来,但是得到的
UserId确都是"bao"。请问这是怎么回事,该如何解决?
(select distinct(CustomerId ) from custinfo where orderid like '1%')
是不是这个意思?
如果以定单号模糊查询,就分别从表1和表2中取得,这些字段:
orderid(定单号) ye(余额) jf(积分)about(备注) putdate(录入时间)和UserId(会员名)
如果输入orderid=1 正确的应该是
会员名 定单号 余额 积分 备注 录入时间
hxf_wolf 10 0 3 订单10积分3 2005-04-12
hxf_wolf 14 0 0 订单14积分0 2005-04-12
boa 17 0 3 订单17积分3 2005-04-12现在的错误结果是
会员名 定单号 余额 积分 备注 录入时间
bao 10 0 3 订单10积分3 2005-04-12
bao 14 0 0 订单14积分0 2005-04-12
boa 17 0 3 订单17积分3 2005-04-12
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON CONVERT(nvarchar(255),dbo.custinfo.orderid) LIKE '%'+@ddh+'%' and dbo.Customers.customerId=@custid
order by putdate desc
你仔细检查这个逻辑...
select @custid=customerId from custinfo where CONVERT(nvarchar(255),orderid) LIKE '%'+@ddh+'%'
这句,返回一条记录,就是最后一条,bao的那个.SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo INNER JOIN
dbo.Customers ON CONVERT(nvarchar(255),dbo.custinfo.orderid) LIKE '%'+@ddh+'%' and dbo.Customers.customerId=@custid
order by putdate desc
---这个,你逻辑错误.On后面是关系,你用的是where里的内容了..
@yhm nvarchar(25),
@ddh nvarchar(255)
AS
declare @custid int
if @yhm is Null OR ltrim(rtrim(@yhm))=''
begin
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo left JOIN
dbo.Customers ON Customers.UserId=Custinfo.Userid
Where CONVERT(nvarchar(255),dbo.custinfo.orderid) LIKE '%'+@ddh+'%'order by putdate desc
end
else
begin
SELECT dbo.custinfo.orderid, dbo.custinfo.ye, dbo.custinfo.jf, dbo.custinfo.about,
dbo.custinfo.putdate, dbo.Customers.UserId
FROM dbo.custinfo left JOIN
dbo.Customers ON Customers.UserId=Custinfo.Userid
Where dbo.Customers.UserId LIKE '%'+@yhm+'%'
order by putdate desc
end;
GO
试了gabriel1(风林火山)的方法,也行
自己还在 ON custinfo.CustomerId = Customers.CustomerId 再应该再加上了
WHERE (Customers.CustomerId IN
(SELECT Customerid
FROM custinfo
WHERE CONVERT(nvarchar(255), orderid) LIKE '%' + @ddh + '%')) AND
(CONVERT(nvarchar(255), custinfo.orderid) LIKE '%' + @ddh + '%')
行是行,不过确实多余多谢gabriel1(风林火山)
给分了-_-