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

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也没排正确。。。 --------------------编程问答-------------------- 左边显示男性右边显示女性
什么样的是男性?
什么样的是女性? --------------------编程问答-------------------- #表中的基数行是男的   偶数行是女的 --------------------编程问答-------------------- 为什么不在表中增加一个性别的字段呢 --------------------编程问答-------------------- 我已经排好序了

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)

但是执行完这个并不是我想要的结果。。。 --------------------编程问答-------------------- 也有性别字段   就比如说是sex字段吧  bit值0为男1为女    这样的话如何解决？ --------------------编程问答-------------------- 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     女
......

现在加上了性别字段如何实现呢？ --------------------编程问答-------------------- 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) --------------------编程问答-------------------- 加上了性別字段，可以多借用一個臨時表處理。

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 --------------------编程问答-------------------- boyname = (SELECT TOP 50 name where sex='男'),girlName= (SELECT TOP 50 name where sex='女'),
INTO #
FROM sales
ORDER BY num,id
--------------------编程问答-------------------- 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))
--------------------编程问答-------------------- 樓上，SQL有這種寫法嗎？ --------------------编程问答-------------------- paoluo你的写法和我的查出来的结果一样偶尔有一些记录顺序会打乱。。。 --------------------编程问答-------------------- paoluo你的写法和我的查出来的结果一样偶尔有一些记录顺序会打乱。。。
--------------------编程问答-------------------- 没想到加上sex字段你们把代码写的更复杂了。。。  我那代码看起来就很恐怖字段很多如果那么写恐怕不行  。。。  我把所有的代码列出来吧？？ --------------------编程问答-------------------- 用两个临时表的做法我还没试过。。

太麻烦了

还是不把代码贴出来了   原理就是我举的那个例子那样如果贴出一大堆代码可能会把人弄糊涂 --------------------编程问答-------------------- ask_ask5() ( ) 信誉：100    Blog  2007-03-09 15:01:24  得分: 0

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

太麻烦了

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


-------

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

用兩個臨時表的話，更容易實現些。 --------------------编程问答-------------------- up --------------------编程问答-------------------- 两个临时表可以这样实现

--准备测试表
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
--------------------编程问答-------------------- 我不是考虑效率我是考虑代码的复杂度  我宁愿不要效率也不要代码变的很负责（很难看）

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

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 --------------------编程问答-------------------- 多一个表我是否就要多列出那么多字段？  我才不那么写。。 --------------------编程问答-------------------- 用了  跑骡的用两个临时表的方法确实是正确了   谢谢虽然调试的时候看到一片秘密嬷嬷的代码但是总算能用了 --------------------编程问答-------------------- 是paoluo，不是跑骡。

砍 --------------------编程问答-------------------- 为什么用 WHERE (g.id % 2 = 1) and (x.id % 2 = 0)  不行？？ --------------------编程问答-------------------- 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>

补充：.NET技术 ,  ASP.NET