SQL 运行机制问题

[好奇都是要学的]

有一个人问： 每个产品随机只取一个规格型号（spec） 有一个人回答：sql代码如下：
if not object_id('ta') is null
drop table ta
Go
Create table ta([product] nvarchar(2),[spec] nvarchar(2))
Insert ta
select N'p1',N's1' union all
select N'p1',N's2' union all
select N'p1',N's3' union all
select N'p2',N's５' union all
select N'p2',N's4'
Go
select *
from ta t
where [spec] = (select top 1 [spec] from ta where [product]=t.[product] order by newid())

这个人写的， 已经实现了发问人的结果， 但是我不理解，
1、where [spec] = 一个数据集 ， 为什么该成in的时候。 。 结果就不同了，条数1到5条不固定，甚至还有一条没有的时候。 比如[spec] = （里面是 s1 s4）和我[spec] in（里面是 s1 s4）都是数据集为什么不同。
2、等我把 order by newid() 去了的时候， = 和 in 的结果就相同了 ， 想不明白。 在线等

[pengyouak]

where [product]=t.[product]

是导致你结果 数量不一致的罪魁祸首

[好奇都是要学的]

发现在 可能是 IN 和newid 的问题 ， 谁能给我结实下啊

把newid 该成固定列 就不会出现多条。 IN 和 NEWID 一起用 就会出现在条数不规定有的时候还一条都没有

[chuifengde]

查看执行计划，是不一样的

[jwwyqs]

看了一会没看出来为什么多了
没明白 虽然是in 但理论上 top 1 应该和=一样的
帮顶吧

[好奇都是要学的]

如果讲的明白。。 我追加分。 谢谢 高人了

[dawugui]

看第四点.

--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go

--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/

--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/

--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/

--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/

--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name,a.val
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/

--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go

select * , px = identity(int,1,1) into tmp from tb

select m.name,m.val,m.memo from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)

drop table tb,tmp

/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go

select m.name,m.val,m.memo from
(
  select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
  select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)

drop table tb

/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

(2 行受影响)
*/

[guguda2008]

用=号的话右边子查询里如果返回多条记录会报错，像COL1=(SELECT .......)这样的条件右边必须返回小于等于1条记录。用IN就没这个问题

[好奇都是要学的]

我自己写的办法。 。
select product , dbo.fun(product ) spec from ta
group by product
函数里返回
select top 1 spec from ta where product = 传近来的 order by newid()

[dawugui]

至于=和IN的区别在于.

字查询如果用=,则必须只能返回一个值.
如果用IN,可以有多个值返回.

[libinlink]

--多执行几次下边的话，你就明白了
select newid()
--执行完你应该明白newid()是随机的

--再执行下边的
select 8
union
select 4
union
select 5
order by 1 asc
--执行完你会明白order by是用来排序的，把8，4，5都变成随机的量不就产生了不同顺序码？

自己在理解一下把

[libinlink]

核心就是

1，select top 1
2，order by newid()

[好奇都是要学的]

没一个是我需要的回答啊。。 自己顶

[guguda2008]

[Quote=引用 8 楼 wang2129929 的回复:]

没一个是我需要的回答啊。。 自己顶
[/Quote]
你先把SELECT TOP 1 ... ORDER BY NEWID()的意思整明白了就行了

[好奇都是要学的]

7楼。。 我当然知道 order by 是排序了


我就是想问。 。 为什么 = 和in 结果不同。 。

当我把order by 去了。 。 =和in 结果就相同了。 。 莫非使用in的时候 orderby 忽略了，即使忽略了。 那多次按执行的时候怎么可能出现 有时候出现1条， 有时候出现2条。 有的时候出现5条。。 有的时候一条没有。 这是为什么？

[guguda2008]

- - 撤退

[好奇都是要学的]

9楼， SELECT TOP 1 ... ORDER BY NEWID() 随机取一条记录。。

[sql_sf]

= 后面的记录只能是唯一的
In 后面可以是一个集合

[好奇都是要学的]

13楼。 。 那有的时候一条记录也没有 是怎么回事。。 按我理解 最少一条啊。。 我top1了