SQL返回行数

[weichangqing00]

select * from A where (A.Id in (select top 1 Uid from B where B.TypeId=1 order by newId()))
or
(A.Id in (select top 2 Uid from B where B.TypeId=2 order by newId()));


为什么不确定 有时是3 有时是4或5或1或2

[youfeng888]

楼主用的是slqserver数据库吧，oralce 没有top用法的；如果在oracle中，可以根据rownum 来限制，如果是随机的可以根据random来限制的；
希望大家多交流，欢迎访问我的空间.

[gxingmin]

select top 1 Uid from B where B.TypeId=1 order by newId()
与
select top 2 uid from B where B.TypeId=2 order by newId()
查询的结果有可能重叠

从你给你的表数据来看，查询A的结果要么是2行，要么是3行，怎么会有1，4，5行呢？

[weichangqing00]

我晕 不信的 试试就知道了

从语句看 只能是3行 因为select top 1 Uid from B where B.TypeId=1 order by newId()查出一个ID

select top 2 uid from B where B.TypeId=2 order by newId()查处两个ID

ID无重复 所以是三条记录 但是运行结果是1/2/3/4/5 这几种随机

[gxingmin]

可能是两行，ID无重复，但是筛选的uid有可能重叠，比方说第一个筛选的UID=4,第二个筛选的UID=4和7
那么从A里只能选出
4 dd
7 gg

[weichangqing00]

第一个的where条件是 typeID=1 第二个是typeID=2 怎么可能重复

[gxingmin]

哦，看错了，第一个TypeID=1，我还以为也是2呢

[wclaccp]

同意2楼

[weichangqing00]

来人。。。

[weichangqing00]

等。。。。

[gxingmin]

奇怪，下面的语句查询函数也是不确定

SELECT  *
FROM    A
WHERE   ( A.[Id] = ( SELECT TOP 1
                            B1.Uid
                    FROM    B B1
                    WHERE   B1.TypeId = 1
                    ORDER BY NEWID() ) )
        OR ( A.[Id] = ( SELECT TOP 1
                                B2.Uid
                       FROM     B B2
                       WHERE    B2.TypeId = 2
                       ORDER BY NEWID() ) )

[gxingmin]

函数====行数

[mailangel123]

newid本来是随机的

[gxingmin]

这个语句查询记录还是0行

SELECT  *
FROM    A
WHERE   A.[Id] = ( SELECT TOP 1
                            B1.Uid
                    FROM    B B1
                    WHERE   B1.TypeId = 1
                    ORDER BY NEWID() ) 
        or A.[Id] = ( SELECT TOP 1
                                B2.Uid
                       FROM     B B2
                       WHERE    B2.TypeId = 1
                       ORDER BY NEWID() ) 

[weichangqing00]

再来人。。。。。

[FtLover]

DECLARE @newID NVARCHAR(100)
SET @newID= NEWID()
SELECT  *
FROM    A
WHERE   A.[Id] = ( SELECT TOP 1
                            B1.Uid
                    FROM    B B1
                    WHERE   B1.TypeId = 1
                    ORDER BY @newID ) 
        or A.[Id] = ( SELECT TOP 1
                                B2.Uid
                       FROM     B B2
                       WHERE    B2.TypeId = 1
                       ORDER BY @newID ) 

[skep99]

试了下，没有楼主说的情况，始终是3行

数据库mssql 2008

select * from A where (A.id in (select top 1 Uid from B where B.typeId=1 order by NEWID()))
Or
(A.id in (select top 2 Uid from B where B.typeId=2 order by NEWID()))

[y2141006]

select count(*) from table

[gxingmin]

[Quote=引用 24 楼 skep99 的回复:]
试了下，没有楼主说的情况，始终是3行

数据库mssql 2008

SQL code
select * from A where (A.id in (select top 1 Uid from B where B.typeId=1 order by NEWID()))
Or
(A.id in (select top 2 Uid from B where B.typeId=2 ord……
[/Quote]


SQLSERVER 2005确实有楼主说的这种问题，问了一位SQL牛人，牛人说是微软的bug

[brucett]

newId()去的是随机数，所以结果不确定

[weichangqing00]

结贴了。。。平均给分