我写两行合并的sql语句为什么顺序会被打乱了？

[ask_ask5]

SELECT TOP 50 num, name, id = IDENTITY (int)
INTO #
FROM sales
ORDER BY num,id


用select * from #查询排序没错记录如下
num , name , id
4 小明 1
4 小红 2
7 大明 3
7 大红 4
......

SELECT TOP 50 boy.name as bonyName, girl.name as girlName
FROM # boy LEFT OUTER JOIN
# girl ON boy.id + 1 = girl.id
WHERE (boy.id % 2 = 1)

用上面的语句我想让他显示出
boyName girlName
小明 小红
大明 大红
.....

就是想让他左边显示男性右边显示女性，可是有些记录他把顺序弄乱了，我应该怎么改写这些语句？使他可以正确的在左边显示男性在右边显示女性呢？

我在WHERE (boy.id % 2 = 1)后加上order by boy.id也没排正确。。。

[ask_ask5]

SELECT TOP 50 num, name, id = IDENTITY (int),sex
INTO #
FROM sales
ORDER BY num,id


用select * from #查询排序没错记录如下
num , name , id , sex
4 小明 1 男
4 小红 2 女
7 大明 3 男
7 大红 4 女
......

现在加上了性别字段如何实现呢？

[paoluo]

id為奇數的一定是boy，id為偶數的一定是girl？

如果是的話，try

SELECT TOP 50 boy.name as bonyName, girl.name as girlName
FROM # boy LEFT OUTER JOIN
# girl ON boy.id + 1 = girl.id
WHERE (boy.id % 2 = 1) And (girl.id % 2 = 0)

[paoluo]

加上了性別字段，可以多借用一個臨時表處理。

SELECT TOP 50 num, name, id = IDENTITY (int),sex
INTO #T1
FROM sales
Where sex = N'男'
ORDER BY num,id

SELECT TOP 50 num, name, id = IDENTITY (int),sex
INTO #T2
FROM sales
Where sex = N'女'
ORDER BY num,id

Select
IsNull(A.name, '' ) As boyName,
IsNull(B.name, '' ) As girlName
From
#T1
Full Join
#T2
On A.ID = B.ID

[luoxia001]

boyname = (SELECT TOP 50 name where sex='男'),girlName= (SELECT TOP 50 name where sex='女'),
INTO #
FROM sales
ORDER BY num,id

[luoxia001]

insert into #(boyname ,girlName) values(boyname = (SELECT TOP 50 name FROM sales where sex='男' ORDER BY num,id),girlName= (SELECT TOP 50 name FROM sales where sex='女' ORDER BY num,id))

[paoluo]

樓上，SQL有這種寫法嗎？

[ask_ask5]

paoluo你的写法 和我的查出来的结果一样偶尔有一些记录顺序会打乱。。。

[haiqin1015]

paoluo你的写法 和我的查出来的结果一样偶尔有一些记录顺序会打乱。。。

[ask_ask5]

没想到加上sex字段你们把代码写的更复杂了。。。 我那代码看起来就很恐怖字段很多如果那么写恐怕不行 。。。 我把所有的代码列出来吧？？

[ask_ask5]

用两个临时表的做法我还没试过。。

太麻烦了

还是不把代码贴出来了 原理就是我举的那个例子那样如果贴出一大堆代码可能会把人弄糊涂

[paoluo]

ask_ask5() ( ) 信誉：100 Blog 2007-03-09 15:01:24 得分: 0


用两个临时表的做法我还没试过。。

太麻烦了

还是不把代码贴出来了 原理就是我举的那个例子那样如果贴出一大堆代码可能会把人弄糊涂


-------

你現在已經借用了一個臨時表，再加上應該不會有太大影響吧。

用兩個臨時表的話，更容易實現些。

[best8625]

up

[blackant2]

两个临时表可以这样实现


--准备测试表
declare @tmp table(
num int
,[name] nvarchar(10)
, id int)
--准备数据
insert into @tmp
select 4,'小明', 1
union all select 4,'小红',2
union all select 7, '大明',3
union all select 7,'大红',4
union all select 9,'超明',5
union all select 11,'微红',8
union all select 9, '巨明',9
union all select 11,'巨红',10


-- boy插入到临时表
select top 50 identity(int,1,1) as id, id as boyid,[name] as boyname,0 as girlid,convert(nvarchar(10),'') as girlname
into #tmp
from @tmp as body
where id%2=1

--girl 插入到临时表
select top 50 identity(int,1,1) as id,id as girlid,[name] as girlname
into #tmp2
from @tmp as body
where id%2=0

--更新总表
update #tmp
set girlid=b.girlid,girlname=b.girlname
from #tmp a
left outer join #tmp2 b on a.id=b.id

--删除临时表
drop table #tmp2
select * from #tmp
drop table #tmp

[ask_ask5]

我不是考虑效率 我是考虑代码的复杂度 我宁愿不要效率也不要代码变的很负责（很难看）

这是我完整的代码 烂的代码如果用两个表来做会不会很麻烦？ 这样的代码用两个表实现起来有简单的写法吗？

SELECT salesrecords.id as myid,pzd.htbh,quantity,price,unit,moneyType,comCode,datetime,salesRecords.gxFlag,pzd.jsqr,class,pzd.link,sendDate,ywy, pzd.zfFlag,jj,dfyf,sxf, wlf,qtfy,hj, id = IDENTITY (int) INTO # FROM salesrecords INNER JOIN pzd ON salesrecords.link = pzd.link inner join clients on salesrecords.link=clients.link where clients.gxFlag = salesrecords.gxFlag and salesRecords.delFlag=0 and (salesrecords.jsqr between '2007-02-21' and '2007-03-20' or salesrecords.jsqr is null) order by myid



select * from(select top 50 * from(select top 50 g.htbh AS htbh,g.comCode as gcomCode,x.comCode as xcomCode,g.unit as gUnit,g.moneyType as gMoneyType,x.moneyType as xMoneyType,g.price as gPrice,x.price as xPrice,g.quantity as gQuantity,g.gxFlag as gFlag,x.gxFlag as xFlag,g.class as class,g.jsqr as jsqr,g.zfFlag as zfFlag,g.dfyf as dfyf,g.jj as jj,g.link As link,g.ywy AS g_ywy, (g.wlf + g.sxf + g.qtfy)/2 AS g_fy, g.hj AS g_zje,g.datetime as datetime, x.ywy AS x_ywy, (x.wlf + x.sxf + x.qtfy)/2 AS x_fy, x.hj AS x_zje, x.hj - g.hj - x.sxf - x.wlf - x.qtfy AS ml FROM # g LEFT OUTER JOIN # x ON g.id + 1 = x.id WHERE (g.id % 2 = 1) and (x.id % 2 = 0) order by htbh)t1 order by htbh desc)t2 order by htbh

[ask_ask5]

多一个表我是否就要多列出那么多字段？ 我才不那么写。。

[ask_ask5]

用了 跑骡 的用两个临时表的方法确实是正确了 谢谢 虽然调试的时候看到一片秘密嬷嬷的代码 但是总算能用了

[paoluo]

是paoluo，不是跑骡。

砍

[ask_ask5]

为什么用 WHERE (g.id % 2 = 1) and (x.id % 2 = 0) 不行？？

[hunhun02]

up

[超级大笨狼]

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
用inner Join可以做到一个表返回纪录，速度要快些。<br/>

select * from 主表 inner Join 从表 on 主表.外键=从表.id<br/>

如果要显示上合并行，可以在客户端合并<br/>

<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1 id="tb">
<TR id="row">
<TD id="td">1</TD>
<TD>a</TD>
<TD>b</TD>
</TR>
<TR>
<TD>1</TD>
<TD>c</TD>
<TD>d</TD>
</TR>
<TR>
<TD>1</TD>
<TD>c</TD>
<TD>d</TD>
</TR>
<TR>
<TD>2</TD>
<TD>e</TD>
<TD>f</TD>
</TR>
<TR>
<TD>2</TD>
<TD>e</TD>
<TD>f</TD>
</TR>
<TR>
<TD>2</TD>
<TD>e</TD>
<TD>f</TD>
</TR>
<TR>
<TD>2</TD>
<TD>e</TD>
<TD>f</TD>
</TR>
</TABLE>
<SCRIPT LANGUAGE=javascript>


var Rows=document.getElementById("tb").rows;
var Col=1;//这个是要合并的第几列；
var lastValue;

for(i=0;i<Rows.length;i++)
{
lastValue=Rows[i].cells[Col-1].innerHTML;
var same=1;

while(lastValue==Rows[i+same].cells[Col-1].innerHTML )
{
Rows[i+same].removeChild(Rows[i+same].cells[Col-1]);
same+=1;
if((i+same)==Rows.length )
{
break;
}
}

Rows[i].cells[Col-1].rowSpan=same;
i=i+same-1;
}
</SCRIPT>


</BODY>
</HTML>