我已经排好序了
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)但是执行完这个并不是我想要的结果
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)但是执行完这个并不是我想要的结果
INTO #
FROM sales
ORDER BY num,id
用select * from #查询排序没错记录如下
num , name , id , sex
4 小明 1 男
4 小红 2 女
7 大明 3 男
7 大红 4 女
......现在加上了性别字段如何实现呢?
FROM # boy LEFT OUTER JOIN
# girl ON boy.id + 1 = girl.id
WHERE (boy.id % 2 = 1) And (girl.id % 2 = 0)
INTO #T1
FROM sales
Where sex = N'男'
ORDER BY num,idSELECT TOP 50 num, name, id = IDENTITY (int),sex
INTO #T2
FROM sales
Where sex = N'女'
ORDER BY num,idSelect
IsNull(A.name, '' ) As boyName,
IsNull(B.name, '' ) As girlName
From
#T1
Full Join
#T2
On A.ID = B.ID
INTO #
FROM sales
ORDER BY num,id
用两个临时表的做法我还没试过。。太麻烦了还是不把代码贴出来了 原理就是我举的那个例子那样如果贴出一大堆代码可能会把人弄糊涂
-------你現在已經借用了一個臨時表,再加上應該不會有太大影響吧。用兩個臨時表的話,更容易實現些。
--准备测试表
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
<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>