none
cross apply是不是不能用Top或者order by RRS feed

  • 问题

  • select cast(Temp.MyVal as decimal(16,2)) from A
    cross apply
    (
     select top 1 MyVal from B
     where B.BId=A.B_Id
     order by B.BID desc
    )Temp
    where A.ItemType=1

    ItemType=1时,B.MyVal 是数字,其它不是数字。
    查询时出错,从数据类型 varchar 转换为 numeric 时出错。去掉cast就不出错了,但查询结果都是能够转换decimal(16,2),是不是cross apply是不是不能用Top或者Order by,还是一个bug
    2009年11月30日 11:34

答案

  • 搞錯SQL版本了,以上是在05環境下執行的

    這是引擎造成的 與 "cross apply" 的使用無關

    在SQL2008里
    是先執行select 的類型轉換
    cast(t.MyVal as decimal(16,2))--1
    再執行 where 條件 --2

    在SQL2005執行步驟相反

    處理方法:改變執行步驟,效率與Cross Apply差不多,可查看執行計劃
    select
    (
     select top 1 cast(b.MyVal as decimal(16,2)) as MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )   as MyVal
    from #A a
    where  a.ItemType=0

    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 6:40
    2009年12月1日 6:24
    版主
  • 用SQL2000寫法,需要多一個嵌套,在效率區別不大,曾測過

    只有在特定的條件情況下用Cross apply的效率高

    樓主用查看執行計劃的第一步可以看到

    --加上Order by 和不加時的執行步驟相反,剛查看了一下,同以下情況一樣

    當在SQL08時用表變量的情況下,執行步驟是 先where 再 select 轉換條件

    用臨時表和表時,執行步驟是先 select 轉換條件再 where


    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 7:15
    2009年12月1日 6:58
    版主
  • select
    cast(t.MyVal as decimal(16,2))
    from #A a
    cross apply
    (
     select top 1 b.MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )t
    where a.ItemType=0 option(force order)

    问题解决
    • 已标记为答案 SQL STUDIO 2009年12月4日 6:45
    2009年12月4日 6:45

全部回复

  • What's data type of MyVal? Contains any non-numeric data?
    2009年11月30日 16:51
  • MyVal varchar(200),当ItemType=1时,MyVal 是数字如:1.1(一位整数一位小数),ItemType!=1时,是用逗号隔开的多个数字如:1.1,2.2

    delete A from B where A.B_Id=B.BId and A.ItemType!=1--删除不等于1的数据就没有出错了(级联删除B表数据)

    或者改成下面语句都是没有出错

    create function ChangeType(@string varchar(200)) returns decimal(16,2) as
    begin
     return cast(@string as decimal(16,2))
    end
    go

    select dbo.ChangeType(Temp.MyVal) from A
    cross apply
    (
     select top 1 MyVal from B
     where B.BId=A.B_Id
     order by B.BID desc
    )Temp
    where A.ItemType=1

    不知道为什么?
    2009年12月1日 1:12
  • 以上出錯信息已說明清楚了,顯然結果集中有非數字類型,不滿足轉換
    ROY WU(吳熹)
    2009年12月1日 3:00
    版主
  • begin tran
    create table #A(AID int primary key,ItemType int)
    create table #B(A_ID int,MyVal varchar(200),Sort int)

    begin
    insert #A
    select 37,0 union all select 38,0 union all select 39,0 union all select 40,0 union all select 41,0 union all select 42,0 union all select 43,0 union all select 81,0 union all select 82,0 union all select 83,0 union all select 84,0 union all select 85,0 union all select 86,0 union all select 87,0 union all select 88,0 union all select 89,0 union all select 90,0 union all select 91,0 union all select 92,0 union all select 93,0 union all select 94,0 union all select 95,0 union all select 96,0 union all select 97,0 union all select 98,0 union all select 99,0 union all select 100,0 union all select 101,0 union all select 102,0 union all select 103,0 union all select 104,0 union all select 105,0 union all select 106,0 union all select 107,0 union all select 108,0 union all select 109,0 union all select 110,0 union all select 111,0 union all select 112,0 union all select 113,0 union all select 114,0 union all select 115,0 union all select 116,0 union all select 117,0 union all select 118,0 union all select 119,0 union all select 120,0 union all select 121,0 union all select 122,0 union all select 123,0 union all select 124,0 union all select 125,0 union all select 126,0 union all select 127,0 union all select 128,0 union all select 129,0 union all select 130,0 union all select 131,0 union all select 132,0 union all select 133,0 union all select 134,0 union all select 135,0 union all select 136,0 union all select 137,0 union all select 138,0 union all select 139,1

    insert #B
    select 37,'1',0 union all select 37,'1',41 union all select 37,'1',42 union all select 37,'1',43 union all select 37,'1',44 union all select 38,'1',0 union all select 38,'1',41 union all select 38,'1',42 union all select 38,'1',43 union all select 38,'1',44 union all select 39,'1',0 union all select 39,'1',41 union all select 39,'1',42 union all select 39,'1',43 union all select 39,'1',44 union all select 40,'1',0 union all select 40,'1',41 union all select 40,'1',42 union all select 40,'1',43 union all select 40,'1',44 union all select 41,'1',0 union all select 41,'1',41 union all select 41,'1',42 union all select 41,'1',43 union all select 41,'1',44 union all select 42,'1',0 union all select 42,'1',41 union all select 42,'1',42 union all select 42,'1',43 union all select 42,'1',44 union all select 43,'1',0 union all select 43,'1',41 union all select 43,'1',42 union all select 43,'1',43 union all select 43,'1',44 union all select 81,'1',0 union all select 81,'1',41 union all select 81,'1',42 union all select 81,'1',43 union all select 81,'1',44 union all select 82,'1',0 union all select 82,'1',41 union all select 82,'1',42 union all select 82,'1',43 union all select 82,'1',44 union all select 83,'1',0 union all select 83,'1',41 union all select 83,'1',42 union all select 83,'1',43 union all select 83,'1',44 union all select 84,'1',0 union all select 84,'1',41 union all select 84,'1',42 union all select 84,'1',43 union all select 84,'1',44 union all select 85,'1',0 union all select 85,'1',41 union all select 85,'1',42 union all select 85,'1',43 union all select 85,'1',44 union all select 86,'1',0 union all select 86,'1',41 union all select 86,'1',42 union all select 86,'1',43 union all select 86,'1',44 union all select 87,'1',0 union all select 87,'1',41 union all select 87,'1',42 union all select 87,'1',43 union all select 87,'1',44 union all select 88,'1',0 union all select 88,'1',41 union all select 88,'1',42 union all select 88,'1',43 union all select 88,'1',44 union all select 89,'1',0 union all select 89,'1',41 union all select 89,'1',42 union all select 89,'1',43 union all select 89,'1',44 union all select 90,'1',0 union all select 90,'1',41 union all select 90,'1',42 union all select 90,'1',43 union all select 90,'1',44 union all select 91,'1',0 union all select 91,'1',41 union all select 91,'1',42 union all select 91,'1',43 union all select 91,'1',44 union all select 92,'1',0 union all select 92,'1',41 union all select 92,'1',42 union all select 92,'1',43 union all select 92,'1',44 union all select 93,'1',0 union all select 93,'1',41 union all select 93,'1',42 union all select 93,'1',43 union all select 93,'1',44 union all select 94,'1',0 union all select 94,'1',41 union all select 94,'1',42 union all select 94,'1',43 union all select 94,'1',44 union all select 95,'1',0 union all select 95,'1',41 union all select 95,'1',42 union all select 95,'1',43 union all select 95,'1',44 union all select 96,'1',0 union all select 96,'1',41 union all select 96,'1',42 union all select 96,'1',43 union all select 96,'1',44 union all select 97,'1',0 union all select 97,'1',41 union all select 97,'1',42 union all select 97,'1',43 union all select 97,'1',44 union all select 98,'1',0 union all select 98,'1',41 union all select 98,'1',42 union all select 98,'1',43 union all select 98,'1',44 union all select 99,'1',0 union all select 99,'1',41 union all select 99,'1',42 union all select 99,'1',43 union all select 99,'1',44 union all select 100,'1',0 union all select 100,'1',41 union all select 100,'1',42 union all select 100,'1',43 union all select 100,'1',44 union all select 101,'1',0 union all select 101,'1',41 union all select 101,'1',42 union all select 101,'1',43 union all select 101,'1',44 union all select 102,'1',0 union all select 102,'1',41 union all select 102,'1',42 union all select 102,'1',43 union all select 102,'1',44 union all select 103,'1',0 union all select 103,'1',41 union all select 103,'1',42 union all select 103,'1',43 union all select 103,'1',44 union all select 104,'1',0 union all select 104,'1',41 union all select 104,'1',42 union all select 104,'1',43 union all select 104,'1',44 union all select 105,'1',0 union all select 105,'1',41 union all select 105,'1',42 union all select 105,'1',43 union all select 105,'1',44 union all select 106,'1',0 union all select 106,'1',41 union all select 106,'1',42 union all select 106,'1',43 union all select 106,'1',44 union all select 107,'1',0 union all select 107,'1',41 union all select 107,'1',42 union all select 107,'1',43 union all select 107,'1',44 union all select 108,'1',0 union all select 108,'1',41 union all select 108,'1',42 union all select 108,'1',43 union all select 108,'1',44 union all select 109,'1',0 union all select 109,'1',41 union all select 109,'1',42 union all select 109,'1',43 union all select 109,'1',44 union all select 110,'1',0 union all select 110,'1',41 union all select 110,'1',42 union all select 110,'1',43 union all select 110,'1',44 union all select 111,'1',0 union all select 111,'1',41 union all select 111,'1',42 union all select 111,'1',43 union all select 111,'1',44 union all select 112,'1',0 union all select 112,'1',41 union all select 112,'1',42 union all select 112,'1',43 union all select 112,'1',44 union all select 113,'1',0 union all select 113,'1',41 union all select 113,'1',42 union all select 113,'1',43 union all select 113,'1',44 union all select 114,'1',0 union all select 114,'1',41 union all select 114,'1',42 union all select 114,'1',43 union all select 114,'1',44 union all select 115,'1',0 union all select 115,'1',41 union all select 115,'1',42 union all select 115,'1',43 union all select 115,'1',44 union all select 116,'1',0 union all select 116,'1',41 union all select 116,'1',42 union all select 116,'1',43 union all select 116,'1',44 union all select 117,'1',0 union all select 117,'1',41 union all select 117,'1',42 union all select 117,'1',43 union all select 117,'1',44 union all select 118,'1',0 union all select 118,'1',41 union all select 118,'1',42 union all select 118,'1',43 union all select 118,'1',44 union all select 119,'1',0 union all select 119,'1',41 union all select 119,'1',42 union all select 119,'1',43 union all select 119,'1',44 union all select 120,'1',0 union all select 120,'1',41 union all select 120,'1',42 union all select 120,'1',43 union all select 120,'1',44 union all select 121,'1',0 union all select 121,'1',41 union all select 121,'1',42 union all select 121,'1',43 union all select 121,'1',44 union all select 122,'1',0 union all select 122,'1',41 union all select 122,'1',42 union all select 122,'1',43 union all select 122,'1',44 union all select 123,'1',0 union all select 123,'1',41 union all select 123,'1',42 union all select 123,'1',43 union all select 123,'1',44 union all select 124,'1',0 union all select 124,'1',41 union all select 124,'1',42 union all select 124,'1',43 union all select 124,'1',44 union all select 125,'1',0 union all select 125,'1',41 union all select 125,'1',42 union all select 125,'1',43 union all select 125,'1',44 union all select 126,'1',0 union all select 126,'1',41 union all select 126,'1',42 union all select 126,'1',43 union all select 126,'1',44 union all select 127,'1',0 union all select 127,'1',41 union all select 127,'1',42 union all select 127,'1',43 union all select 127,'1',44 union all select 128,'1',0 union all select 128,'1',41 union all select 128,'1',42 union all select 128,'1',43 union all select 128,'1',44 union all select 129,'1',0 union all select 129,'1',41 union all select 129,'1',42 union all select 129,'1',43 union all select 129,'1',44 union all select 130,'1',0 union all select 130,'1',41 union all select 130,'1',42 union all select 130,'1',43 union all select 130,'1',44 union all select 131,'1',0 union all select 131,'1',41 union all select 131,'1',42 union all select 131,'1',43 union all select 131,'1',44 union all select 132,'1',0 union all select 132,'1',41 union all select 132,'1',42 union all select 132,'1',43 union all select 132,'1',44 union all select 133,'1',0 union all select 133,'1',41 union all select 133,'1',42 union all select 133,'1',43 union all select 133,'1',44 union all select 134,'1',0 union all select 134,'1',41 union all select 134,'1',42 union all select 134,'1',43 union all select 134,'1',44 union all select 135,'1',0 union all select 135,'1',41 union all select 135,'1',42 union all select 135,'1',43 union all select 135,'1',44 union all select 136,'1',0 union all select 136,'1',41 union all select 136,'1',42 union all select 136,'1',43 union all select 136,'1',44 union all select 137,'1',0 union all select 137,'1',41 union all select 137,'1',42 union all select 137,'1',43 union all select 137,'1',44 union all select 138,'1',0 union all select 138,'1',41 union all select 138,'1',42 union all select 138,'1',43 union all select 138,'1',44 union all select 139,'1.1,2.1',0 union all select 139,'1.1,2.1',41 union all select 139,'1.1,2.1',42
    end

    select
    cast(t.MyVal as decimal(16,2))
    from #A a
    cross apply
    (
     select top 1 b.MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )t
    where a.ItemType=0
    drop table #A
    drop table #B
    rollback

    --数据量太多,又不能删除部分的,奇怪的是临时表改成表变量就不出错了!!

    2009年12月1日 3:43
  • 加上這個條件,應該不會出錯
    where isnumeric(t.MyVal)=1 and len(t.MyVal)-len(replace(t.MyVal,'.',''))<2 and t.MyVal not like '%,%'

    ROY WU(吳熹)
    2009年12月1日 3:55
    版主
  • ItemType=0时是数字,ItemType=1时是逗号隔开的数字

    不过
    where isnumeric(t.MyVal)=1 and len(t.MyVal)-len(replace(t.MyVal,'.',''))<2 and t.MyVal not like '%,%'
    也是出错的
    2009年12月1日 3:57
  • ItemType=0时是数字,ItemType=1时是逗号隔开的数字

    不过
    where isnumeric (t.MyVal)=1 and len (t.MyVal)-len (replace (t.MyVal,'.' ,'' ))<2 and t.MyVal not like '%,%'
    也是出错的
    以上條件是處理轉換條件
    cast(t.MyVal as decimal(16,2))

    樓主加上還會出錯?
    加多一個'.'的判斷
    where (isnumeric(t.MyVal)=1 and len(t.MyVal)-len(replace(t.MyVal,'.',''))<2 and t.MyVal not like '%,%') or right(t.MyVal,1)<>'.'

    ROY WU(吳熹 )
    2009年12月1日 4:12
    版主
  • where (isnumeric(t.MyVal)=1 and len(t.MyVal)-len(replace(t.MyVal,'.',''))<2 and t.MyVal not like '%,%') or right(t.MyVal,1)='.'
    还是出错

    就算指定了 where t.MyVal='1'都是出错呢
    2009年12月1日 4:17
  • 改為不等於
    right(t.MyVal,1)<>'.'

    ROY WU(吳熹)
    2009年12月1日 4:19
    版主
  • 不行不行
    2009年12月1日 4:21
  • 不行不行
    where t.MyVal like '%[^0-9]%'
    查非數字類型

    select t.MyVal --去掉轉換
    加上條件,把結果貼出來看看都是什麼數據

    ROY WU(吳熹 )
    2009年12月1日 4:24
    版主
  • where t.MyVal like '%[^0-9]%'

    去掉轉換结果:1.1,2.1
    2009年12月1日 4:43
  • declare @s nvarchar(20)
    set @s='1.1,2.1'
    select 1 where (isnumeric(@s)=1 and len(@s)-len(replace(@s,'.',''))<2 and @s not like '%,%') or right(@s,1)='.'
    這樣的數據,用我以上的條件是不成立的.

    樓主的語句是否為這樣?

    select
    cast(t.MyVal as decimal(16,2))
    from #A a
    cross apply
    (
     select top 1 b.MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )t
    where a.ItemType=0 and ((isnumeric(t.MyVal)=1 and len(t.MyVal)-len(replace(t.MyVal,'.',''))<2 and t.MyVal not like '%,%') or right(t.MyVal,1)<>'.')

    用條件篩選一下就可找出原因了,按我的條件是不會出現轉換錯的,除非溢出。



    ROY WU(吳熹 )
    2009年12月1日 5:00
    版主
  • 也是不行,我怀疑cross apply 不能用order by

    where a.ItemType=0查出65条数据全都是1,却就是不能转换,但通过自己定义的函数转换却行,真不知道何解?


    create function ChangeType(@string varchar(200)) returns decimal(16,2) as
    begin
     return cast(@string as decimal(16,2))
    end
    2009年12月1日 5:02
  • 也是不行,我怀疑cross apply 不能用order by
    樓主的SQL2005以上版本都可以用,不會有問題。

    以上數據在我這里是不會出錯,細心一點就可看到問題了。

    ROY WU(吳熹)
    • 已标记为答案 SQL STUDIO 2009年12月1日 5:17
    2009年12月1日 5:08
    版主
  • 真是辛苦了中國風


    可能是sqlserver 2008的Bug,我在sqlserver 2005执行却没出错,在sqlserver 2008执行却出错了。

    看来微软得解决这个问题!!
    2009年12月1日 5:11
  • 搞錯SQL版本了,以上是在05環境下執行的

    這是引擎造成的 與 "cross apply" 的使用無關

    在SQL2008里
    是先執行select 的類型轉換
    cast(t.MyVal as decimal(16,2))--1
    再執行 where 條件 --2

    在SQL2005執行步驟相反

    處理方法:改變執行步驟,效率與Cross Apply差不多,可查看執行計劃
    select
    (
     select top 1 cast(b.MyVal as decimal(16,2)) as MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )   as MyVal
    from #A a
    where  a.ItemType=0

    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 6:40
    2009年12月1日 6:24
    版主
  • 这还是可以的,不过有些情况子查询的效率不如cross apply,例如a.AID换成固定值1时,子查询会查询N次,cross apply只查询1次然后复制N次结果


    其实,如果不用 order by的话,我的语句在sqlserver 2008用cross apply都是不会出错的。

    2009年12月1日 6:46
  • 用SQL2000寫法,需要多一個嵌套,在效率區別不大,曾測過

    只有在特定的條件情況下用Cross apply的效率高

    樓主用查看執行計劃的第一步可以看到

    --加上Order by 和不加時的執行步驟相反,剛查看了一下,同以下情況一樣

    當在SQL08時用表變量的情況下,執行步驟是 先where 再 select 轉換條件

    用臨時表和表時,執行步驟是先 select 轉換條件再 where


    ROY WU(吳熹 )
    • 已标记为答案 SQL STUDIO 2009年12月1日 7:15
    2009年12月1日 6:58
    版主
  • select
    cast(t.MyVal as decimal(16,2))
    from #A a
    cross apply
    (
     select top 1 b.MyVal from #B b
     where b.A_ID=a.AID
     order by b.Sort desc
    )t
    where a.ItemType=0 option(force order)

    问题解决
    • 已标记为答案 SQL STUDIO 2009年12月4日 6:45
    2009年12月4日 6:45