select *,0 flag into # from 表 order by iddeclare @a int,@b intupdate # set @a=case when @b=id then @a+1 else 1 end,@b=id,flag=@aselect a.result,b.result from # a,# b where a.id=1 and b.id=2 and a.flag=b.flag
go
drop table #
go
drop table #
解决方案 »
- 请教SQL判断某字段含有某个代码自动替换语句。
- visual studio 2010 rtm 中自带的sqlserver 是什么版本的啊?
- SQL语句,反向比较的问题
- 表结构修改
- 备份数据库的时机??
- 如何解决合并发布中的主键冲突问题--急!
- 求教:知道对方的ip地址和sa密码,怎样配置可以对异地的sql数据库进行远程更新?
- datetime字段类型分离的问题
- 用"select distinct a,b,c from table"查詢出來的數據按a,b,c排了序
- 再发动态存储过程的问题,没有几天了,再做不出来,就要卷铺盖走人了,在线等待
- sql查询问题—极具挑战性
- 请问这样设计系统数据库是否合理?
Select identity(int,1,1) as id,result as result2 into #tmp2 from table where id=2
Select result1,result2 from #tmp1 join #tmp2 on #tmp1.id = #tmp2.id
INTO CompareScore
FROM QuestionResult
ORDER BY SurveyID DECLARE @a int, @b int
UPDATE #
SET @a = CASE WHEN @b = SurveyID THEN @a + 1 ELSE 1 END,
@b = SurveyID, flag = @a
SELECT a.AvgScore, b.AvgScore
FROM # a, # b
WHERE a.SurveyID = 1 AND b.SurveyID = 2 AND
a.flag = b.flag go DROP TABLE CompareScore
那我增加一个字段Num,根据Num排序,没有的置空,怎么实现那?感谢了阿,我再加分阿
ID|Result|Num
1 |aaa |1
1 |bbb |2
1 |fff |3
2 |ccc |2
2 |ddd |3
2 |eee |4
变成
Num| Result1|Result2
1 | aaa |
2 | bbb |ccc
3 | fff |ddd
4 | |eee
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from 表 where id=1
) a full join (
select result,num from 表 where id=2
) b on a.num=b.num
order by num
declare @tb table(ID int,Result varchar(4),Num int)
insert into @tb
select 1,'aaa',1
union all select 1,'bbb',2
union all select 1,'fff',3
union all select 2,'ccc',2
union all select 2,'ddd',3
union all select 2,'eee',4--查询结果
select Num=isnull(a.num,b.num)
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from @tb where id=1
) a full join (
select result,num from @tb where id=2
) b on a.num=b.num
order by num
/*--上面语句的执行结果
Num Result1 Result2
----------- ------- -------
1 aaa
2 bbb ccc
3 fff ddd
4 eee(所影响的行数为 4 行)
--*/
from (select distinct num from abc) a left join (select * from abc where id=1) b on a.num=b.num left join (select * from abc where id=2) c on a.num=c.num
如果我 有一列数据项,是由除法运算的来,但是可能除数为零
当除数为零时,自动置空,该怎么实现那
INTO #
FROM QuestionResult
ORDER BY SurveyID DECLARE @a int, @b int UPDATE #
SET @a = CASE WHEN @b = SurveyID THEN @a + 1 ELSE 1 END,
@b = SurveyID, flag = @a SELECT a.AvgScore, b.AvgScore
FROM # a, # b
WHERE a.SurveyID = 1 AND b.SurveyID = 2 AND
a.flag = b.flag
go
DROP TABLE CompareScore
当除数为零时,自动置空,该怎么实现那
select case when 你的列<>0 then 被除数列/你的列 else 0 end from 表
该放那儿呢?