有两个表,一主一子。
主表order:orderid ordertype
1 A
2 B子表(order_car):
orderid carno
1 10
1 20
2 30我想写一条select 语句,得到如下效果 :orderid ordertype carno
1 A 10,20
2 B 30不写存储过程,能否实现?
主表order:orderid ordertype
1 A
2 B子表(order_car):
orderid carno
1 10
1 20
2 30我想写一条select 语句,得到如下效果 :orderid ordertype carno
1 A 10,20
2 B 30不写存储过程,能否实现?
解决方案 »
- 求T-sql 堆栈写法
- sql server 2005 中怎样用Business Intelligence Development studio 做报表啊?
- SQL2000作业问题
- 在sqlserver数据库中,如何得到当前日期任意天前的日期?
- 请大哥帮忙,数据库恢复问题
- [求助]SQL2000和SQL2005同时装在WEB服务器的问题...
- 修改一张有十几万数量的数据表,将其中几个字段的类型修改后保存,很长时间不响应,然后强行关闭,数据库就不能用了,怎么办
- 视图查询问题,在本地,视图查询是没有任何问题,放在服务器上,数据第一页,有数据,但第2页就没有数据了.(我是做web方面的分页)
- 求一条特殊情况的SQL更新语句
- sqlservre和ORACLE:分布式事物处理?版本要求?
- sql 2009-11-10
- 重命名索引
select
a.orderid,
a.ordertype,
carno=stuff((select ','+ltrim(b.carno) from order_car where orderid=b.orderid),1,1,'')
from
[order] a
join
order_car b
on
a.orderid=b.orderid
group by
a.orderid,
a.ordertype
go
create table [order]([orderid] int,[ordertype] varchar(1))
insert [order]
select 1,'A' union all
select 2,'B'
if object_id('[order_car]') is not null drop table [order_car]
go
create table [order_car]([orderid] int,[carno] int)
insert [order_car]
select 1,10 union all
select 1,20 union all
select 2,30select
b.orderid,
a.ordertype,
carno=stuff((select ','+ltrim(carno) from order_car where orderid=b.orderid for xml path('')),1,1,'')
from
[order] a
join
order_car b
on
a.orderid=b.orderid
group by
b.orderid,
a.ordertype/**
orderid ordertype carno
----------- --------- ----------------
1 A 10,20
2 B 30(所影响的行数为 2 行)**/
(
orderid int,
carno int
)
insert into #order_car select 1,10
insert into #order_car select 1,20
insert into #order_car select 2,30
select stuff((select ','+ltrim(r.carno) from #order_car where orderid=r.orderid for xml path('')),1,1,'') 'carno' from #order_car r
CREATE function [dbo].[f_union1](@department nvarchar)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+ ','+ carno from bbb where orderid = @department
set @ret = stuff(@ret,1,1, ' ')
--print(@ret)
IF @ret <> ' '
BEGIN
set @ret = substring(@ret , 2 , len(@ret))
END
return @ret
end
select * , dbo.f_union1(orderid)carno from aaa
这是以前项目中写过的,终于找出来了。
已经解决了。如下:
select
a.entrunstno,
wm_concat(b.vechicleid) carnm
from
tbt_ent_information a
join
tbt_asn_zhuanche b
on
a.entrunstno =b.entrunstno
where a.entrunstno ='0030000'
group by
a.entrunstno
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
create table order_car(orderid int ,carno int)/*哈哈,刚从小F那看到的方法 , 用上了,不过你们建表的速度也太快了吧,我才建好表你们解决完问题了*/
insert into order1(orderid,ordertype)
select 1,'A' union all
select 2,'b'insert into order_car(orderid,carno)
select 1,10 union all
select 1,20 union all
select 2,30 晕啊。郁闷的程序员 。 路过。