有两张表:A 和 B
其中A有一个字段name,在B中也有与A表对应的字段name,现在要把A、B两张表连接起来,形成一张表。
规则是把B中的name字段和其他字段按照A.name的顺序来排序。其中A.name和B.name的所有行的数据相等,
只是要以A.name的书序来排序。
试了很多中方法,还不得其解。等高手来回答,谢了。
其中A有一个字段name,在B中也有与A表对应的字段name,现在要把A、B两张表连接起来,形成一张表。
规则是把B中的name字段和其他字段按照A.name的顺序来排序。其中A.name和B.name的所有行的数据相等,
只是要以A.name的书序来排序。
试了很多中方法,还不得其解。等高手来回答,谢了。
select a.*,b.* from a inner join b
on a.name = b.name
order by a.name
--try
select * from a join b on a.name=b.name
order by a.name
select a.*,b.* from a inner join b
on a.name = b.name
order by a.name ascselect a.*,b.* from a inner join b
on a.name = b.name
order by a.name desc
刚才试了下代码,还是不行,没有按照A.name中的顺序来排序。
select a.*,b.* from a inner join b
on a.name = b.name
order by replace(a.name,' ','') asc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE expandfield (@current varchar(8000),@makestack int) AS
SET NOCOUNT ON
Declare @pno varchar(8000)
SELECT @current=cInvCode FROM TB_temptable_BOM where cParentNo='root'
if(@makestack=1) --make temp stack?
BEGIN
DECLARE @lvl int, @line varchar(8000)
DECLARE @cpno varchar(8000)
if(not exists(SELECT name FROM sysobjects where type='U' and name='cparent')) --table not exists?
CREATE TABLE cparent (#no varchar(8000),#cpno varchar(8000))
if exists(SELECT top 1 * FROM cparent)
delete FROM cparent
CREATE TABLE #stack (item varchar(8000), lvl int,cpno varchar(8000))
INSERT INTO #stack VALUES (@current, 1,@current)
SELECT @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item
FROM #stack
WHERE lvl = @lvl
SELECT @cpno=cpno FROM #stack
SELECT @line = space(@lvl - 1) + @current
INSERT INTO cparent(#no,#cpno) VALUES (@line,@cpno)
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
INSERT #stack
SELECT cInvCode, @lvl + 1 , cParentNo
FROM TB_temptable_BOM
WHERE cParentNo = @current
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
END
if(exists(SELECT name FROM sysobjects where type='U' and name='TB_newTempTable')) --table exists?
DROP TABLE TB_newTempTable
--SELECT replace(a.#no,space(1),space(9)) as cParentNo_NEW,b.cParentNo,b.cInvCode,b.cInvname,b.cinvaddcode FROM
--(SELECT * FROM cparent) as a,
--(SELECT * FROM tb_temptable_bom where exists (SELECT * FROM cparent)) as b
--where ltrim(a.#no)=b.cinvcode and a.#cpno=b.cParentNoSELECT replace(a.#no,space(1),space(9)) as cParentNo_NEW,b.* FROM
cparent a join tb_temptable_bom b
on ltrim(a.#no)=b.cinvcode and a.#cpno=b.cparentno