有二个表,
订单:Order(order_uid,order_no,car_no)
柜子:CNTR(cntr_uid,cntr_no,order_uid)
对应关系:一张订单有多个柜子。
现在要得根据订单,查询出这样的查询结果,订单号对应的所有的柜子。
列名:order_no,cntr_no.由于一张订单可以有多个柜子,所以cntr_no这个列就返回多个柜子,用“/”隔开。
结果示例:
订单 柜号
HKSZ0910230001 CNTRNO1/CNTRNO2/CNTRNO3
HKSZ0910230002 CNTRNO5/CNTRNO6
HKSZ0910230003 CNTRNO9
HKSZ0910230004 CNTRNO10/CNTRNO11
我用拼接SQL查询,只能查询出指定订单号的记录。
查询多条订单的记录就没办法了。。
请教各位大大,有没有方法查询出来。小弟在此先谢谢您啦。
订单:Order(order_uid,order_no,car_no)
柜子:CNTR(cntr_uid,cntr_no,order_uid)
对应关系:一张订单有多个柜子。
现在要得根据订单,查询出这样的查询结果,订单号对应的所有的柜子。
列名:order_no,cntr_no.由于一张订单可以有多个柜子,所以cntr_no这个列就返回多个柜子,用“/”隔开。
结果示例:
订单 柜号
HKSZ0910230001 CNTRNO1/CNTRNO2/CNTRNO3
HKSZ0910230002 CNTRNO5/CNTRNO6
HKSZ0910230003 CNTRNO9
HKSZ0910230004 CNTRNO10/CNTRNO11
我用拼接SQL查询,只能查询出指定订单号的记录。
查询多条订单的记录就没办法了。。
请教各位大大,有没有方法查询出来。小弟在此先谢谢您啦。
from order o inner join CNTR R on O.order_uid=R.order_uid
where order_uid='订单号'
alter procedure sp_order
(
@as_order_uid
)
asbegin declare @sql varchar(8000) select @sql = ''
select @sql = @sql + CNTR.cntr_no
from CNTR with(nolock)
where CNTR.order_uid = @as_order_uid select order_no,
@sql as cntr_no
from order with(nolock),
cntr with(nolock)
where order.order.uid = cntr.order_uid and
order.order.uid = @as_order_uid
end
HKSZ0910230001 CNTRNO1
HKSZ0910230001 CNTRNO2
HKSZ0910230001 CNTRNO3
HKSZ0910230002 CNTRNO5
HKSZ0910230002 CNTRNO6
HKSZ0910230003 CNTRNO9
HKSZ0910230004 CNTRNO10
HKSZ0910230004 CNTRNO11
from [order] o inner join CNTR R on O.order_uid=R.order_uid
where o.order_no= '9999' --订单号
create table [order](order_uid int,order_no nvarchar(20),car_no nvarchar(20))
create table CNTR(cntr_uid int,cntr_no nvarchar(20),order_uid int) insert into [order] values(1,'HKSZ0910230001 ','aaa')
insert into [order] values(2,'HKSZ0910230002 ','aaa')
insert into [order] values(3,'HKSZ0910230003 ','aaa')insert into CNTR values(1,'CNTRNO1',1)
insert into CNTR values(2,'CNTRNO2',1)
insert into CNTR values(3,'CNTRNO1',2)
insert into CNTR values(4,'CNTRNO2',3)select order_no,cntr_no
from [order] o inner join CNTR R on O.order_uid=R.order_uid
--where o.order_no= '9999' --订单号Select order_no,
car_no=isnull(stuff((select ','+rtrim(cntr_no) from CNTR where [order].order_uid=CNTR.order_uid for XML path('')),1,1,''),'')
from [order]/*
order_no cntr_no
-------------------- --------------------
HKSZ0910230001 CNTRNO1
HKSZ0910230001 CNTRNO2
HKSZ0910230002 CNTRNO1
HKSZ0910230002 CNTRNO2
HKSZ0910230003 CNTRNO2(5 行受影响)order_no car_no
-------------------- --------------------
HKSZ0910230001 CNTRNO1,CNTRNO2
HKSZ0910230002 CNTRNO1,CNTRNO2
HKSZ0910230003 CNTRNO2(3 行受影响)*/
drop table [order],cntr
狼哥,你这种实现方法是思想是什么。
语法也很新鲜,我又要翻翻书本了,不然都看不明白。
if OBJECT_ID('[order]') is not null
drop table [order]
go
create table [order](order_uid int,order_no nvarchar(20),car_no nvarchar(20))
go
if OBJECT_ID('CNTR') is not null
drop table CNTR
go
create table CNTR(cntr_uid int,cntr_no nvarchar(20),order_uid int)
go
insert into [order] values(1,'HKSZ0910230001 ','aaa')
insert into [order] values(2,'HKSZ0910230002 ','aaa')
insert into [order] values(3,'HKSZ0910230003 ','aaa')insert into CNTR values(1,'CNTRNO1',1)
insert into CNTR values(2,'CNTRNO2',1)
insert into CNTR values(3,'CNTRNO1',2)
insert into CNTR values(4,'CNTRNO1',2)
insert into CNTR values(5,'CNTRNO2',3)select order_no,cntr_no
from [order] o inner join CNTR R on O.order_uid=R.order_uid
--where o.order_no= '9999' --订单号
---sql2005才能这样写
Select order_no,
cntr_no=isnull(stuff((select ','+rtrim(cntr_no) from CNTR where [order].order_uid=CNTR.order_uid for XML path('')),1,1,''),'')
from [order]/*
order_no cntr_no
-------------------- --------------------
HKSZ0910230001 CNTRNO1
HKSZ0910230001 CNTRNO2
HKSZ0910230002 CNTRNO1
HKSZ0910230002 CNTRNO1
HKSZ0910230003 CNTRNO2(5 行受影响)order_no cntr_no
-------------------- ------------------
HKSZ0910230001 CNTRNO1,CNTRNO2
HKSZ0910230002 CNTRNO1,CNTRNO1
HKSZ0910230003 CNTRNO2
*/
go
---sql2000的话要用函数
if OBJECT_ID('dbo.getStr') is not null
drop function dbo.getStr
go
create function dbo.getStr(@order_uid int)
returns nvarchar(200)
as
begin
declare @str as nvarchar(200)
select @str=isnull(@str,'')+cntr_no from CNTR where order_uid=@order_uid
if @str <> ''
select @str=right(@str,len(@str)-1)
return @str
end
goSelect order_no,car_no=dbo.getStr(order_uid) from [order]
/*order_no car_no
-------------------- ---------------
HKSZ0910230001 NTRNO1CNTRNO2
HKSZ0910230002 NTRNO1CNTRNO1
HKSZ0910230003 NTRNO2(3 行受影响)*/
订单号 柜子
HKSZ0910230001 CNTRNO1, CNTRNO2,CNTRNO3
如果要查询 这个的订单的中有没有 CNTRNO3 柜子
select * from CNTR where find_in_set('CNTRNO3', car_no
);