我的sql得出的结果和条件不同 有没更好的办法DECLARE @intQuestionCount INT
SET @intQuestionCount=5 --数量条件
CREATE TABLE #t (id INT,
eCount INT
)
INSERT #t VALUES(1,1)
INSERT #t VALUES(2,2)
INSERT #t VALUES(3,1)
INSERT #t VALUES(4,3)
INSERT #t VALUES(5,1)
INSERT #t VALUES(5,2)
INSERT #t VALUES(6,1)
INSERT #t VALUES(7,4)
INSERT #t VALUES(8,2)
INSERT #t VALUES(9,1)
INSERT #t VALUES(10,1)
declare @tb table(num varchar(500),id int,eCount int)
insert @tb
select newid() as strID,id,eCount from #t
select SUM(eCount) from @tb c where @intQuestionCount>=(select sum(eCount) from @tb t where c.num >=t.num)DROP TABLE [#t]
SET @intQuestionCount=5 --数量条件
CREATE TABLE #t (id INT,
eCount INT
)
INSERT #t VALUES(1,1)
INSERT #t VALUES(2,2)
INSERT #t VALUES(3,1)
INSERT #t VALUES(4,3)
INSERT #t VALUES(5,1)
INSERT #t VALUES(5,2)
INSERT #t VALUES(6,1)
INSERT #t VALUES(7,4)
INSERT #t VALUES(8,2)
INSERT #t VALUES(9,1)
INSERT #t VALUES(10,1)
declare @tb table(num varchar(500),id int,eCount int)
insert @tb
select newid() as strID,id,eCount from #t
select SUM(eCount) from @tb c where @intQuestionCount>=(select sum(eCount) from @tb t where c.num >=t.num)DROP TABLE [#t]
SET @intQuestionCount=5 --数量条件
CREATE TABLE #t (id INT,
eCount INT
)
INSERT #t VALUES(1,1)
INSERT #t VALUES(2,2)
INSERT #t VALUES(3,1)
INSERT #t VALUES(4,3)
INSERT #t VALUES(5,1)
INSERT #t VALUES(5,2)
INSERT #t VALUES(6,1)
INSERT #t VALUES(7,4)
INSERT #t VALUES(8,2)
INSERT #t VALUES(9,1)
INSERT #t VALUES(10,1)
select identity(int,1,1) as strID,id,eCount into # from #t
select SUM(eCount) from # c
where @intQuestionCount>=(select sum(eCount) from # t where c.strID >=t.strID)DROP TABLE [#t],#/*
-----------
4(所影响的行数为 1 行)
*/
-----------
4(所影响的行数为 1 行)
*/@intQuestionCount>=(select 这个地方有问题,所以结果和条件不正确
SET @intQuestionCount=5 --数量条件
CREATE TABLE #t (id INT,
eCount INT
)
INSERT #t VALUES(1,1)
INSERT #t VALUES(2,2)
INSERT #t VALUES(3,1)
INSERT #t VALUES(4,3)
INSERT #t VALUES(5,1)
INSERT #t VALUES(5,2)
INSERT #t VALUES(6,1)
INSERT #t VALUES(7,4)
INSERT #t VALUES(8,2)
INSERT #t VALUES(9,1)
INSERT #t VALUES(10,1)
select identity(int,1,1) as strID,id,eCount into #
from (select top 100 percent* from #t order by newid()) a
select * from #
select SUM(eCount) from # c
where @intQuestionCount>=(select sum(eCount) from # t where c.strID >=t.strID)DROP TABLE [#t],#
但是where @intQuestionCount>=(
结果SUM(eCount)<=@intQuestionCount
你的句子:select SUM(eCount) from @tb c where @intQuestionCount>=(select sum(eCount) from @tb t where c.num >=t.num)@intQuestionCount 只是对子查询的条件有效, 而非对主查询所列的结果有效. 而子查询中所构成 sum(eCount) 的行与主查询不是完全重合的.如果sqlserver公布了源码, 你通过你这个句子, 然后通过另外的部分操作sqlserver的内存数据,看看它的子查询匹配了哪些行, 这些行才是你要的.没有这么简单能实现的.