select a,b,max(c) as c,max(d) as d from @a where c<>2 group by a,b /** a b c d ----------- ----------- ----------- ----------- 1 2 3 5 2 2 3 5(所影响的行数为 2 行) **/
declare @a table (a int ,b int,c int,d int)insert @a select 1,2,3,4 union all select 1,2,2,5 union all select 1,2,3,5 union all select 2,2,3,5--select * from @aselect a,b,max(c) c,d from (select a,b,c,max(d) d from @a group by a,b,c) t group by a,b,d having max(c)<>2a b c d ----------- ----------- ----------- ----------- 1 2 3 5 2 2 3 5(2 行受影响)
这样不行,你一开始就把C<>2的给去了,那样不行,我的都是有顺序的
declare @a table (a int ,b int,c int,d int)insert @a select 1,2,3,4 union all select 1,2,2,5 union all select 1,2,3,5 union all select 2,2,3,5--select * from @aselect a,b,max(c) c,d from (select a,b,c,max(d) d from @a group by a,b,c) t where c<>2 group by a,b,d /* a b c d ----------- ----------- ----------- ----------- 1 2 3 5 2 2 3 5(2 行受影响) */这样应该会快点
declare @a table (a int ,b int,c int,d int)insert @a select 1,2,3,4 union all select 1,2,2,5 union all select 1,2,3,5 union all select 2,2,3,5select T1.a,T1.b,max(T1.c) as c,T1.d from @a as T1 inner join (select a,b,max(d) as d from @a group by a,b) as T2 on T1.a=T2.a and T1.b=T2.b and T1.d=T2.d where T1.c<>2 group by T1.a,T1.b,T1.d/* 1 2 3 5 2 2 3 5 */
declare @t table (a int ,b int,c int,d int)insert @t select 1,2,3,4 union all select 1,2,2,5 union all select 1,2,3,5 union all select 2,2,3,5 ;with wang1 as (select * from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d)), wang2 as (select a,b,c=max(c),d from wang1 group by a,b,d) select * from wang2 where c<>2
不对,你那个c<>2的条件 还是早限制了一步,换个数据你这就错;俄
一种方法: select * from ( select a,b,c=max(c),d from (select * from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d) )k group by a,b,d) p where c<>2但是比较麻烦,第二种方法: 把上面的with 改成临时表,这样能好一点 select * into #1from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d) select a,b,c=max(c),d into #1 from #1 group by a,b,d) select * from #2 where c <>2
so easy!declare @a table (a int ,b int,c int,d int)insert @a select 1,2,3,4 union all select 1,2,2,5 union all select 1,2,3,5 union all select 2,2,3,5select a,b,c,d from @a a where not exists(select 1 from @a where a=a.a and b=a.b and (d>a.d or d=a.d and c>a.c)) and c!=2 /* 1 2 3 5 2 2 3 5*/
那你就把where c<>2套在外面好了啊暈select * from (原來語句去掉where c<>2) A where c<>2
这句话 能不能 加上括号 ,怎么个意思啊? d>a.d or d=a.d and c>a.c
select a,b,c,d from @a a where not exists(select 1 from @a where a=a.a and b=a.b and (d>a.d or d=a.d and c>a.c)) and c!=2 这种写法的话 建立什么样的索引 比较好,@a这个表中有100多万条数据建立索引也很慢
语句逻辑: /* 什么意思 ? 举个例子,跟下面的题一样.设stu表,name列唯一,得到学生的成绩排名,首先按english比较,english相同时再按phycal比较.结果如下:语句原理: 统计表中,name不等于当前记录 的个数, 且条件满足于下: eng > 当前eng 的记录,被算入 eng = 当前记录 eng 的情况下,如果 phy同时大于当前记录 phy 则,排名也较当前记录靠前 所以为 WHERE name != 当前行记录.name AND (eng > 当前行记录.eng 或者在 eng = 当前行记录.eng 时 并且 phy > 当前记录行.eng ) 楼主的这个问题,与我举给你的例子本质是一样的.
*/ DECLARE @stu TABLE(name VARCHAR(10), eng INT, phy INT) INSERT @stu SELECT 'a',81,33 UNION ALL SELECT 'b',99,33 UNION ALL SELECT 'c',81,55SELECT idx = 1+(SELECT COUNT(*) FROM @stu WHERE name!=a.name AND (eng>a.eng or eng = a.eng AND phy > a.phy) ) , * FROM @stu a ORDER BY idx/* 1 b 99 33 2 c 81 55 3 a 81 33*/速度很慢: 需求使然.索引建立: 尝试 a 上聚集索引 d,c上建立非聚集索引, d在为索引前导列.至于怎么样建最好,可以多做几份测试.
尝试在 a,b上建复合索引 d,c上建复合索引, d为前导列.
declare @a table (a int ,b int,c int,d int)insert @a select 1,2,2,6 union all select 1,2,1,6 union all select 1,2,3,6 union all select 2,2,3,5 union all select 2,3,2,6select T1.a,T1.b,substring(max(case when t1.c='2' then '0' else '1' end+cast(T1.c as varchar)),2,10) as c,T1.d from @a as T1 inner join (select a,b,max(d) as d from @a group by a,b) as T2 on T1.a=T2.a and T1.b=T2.b and T1.d=T2.d group by T1.a,T1.b,T1.d
/**
a b c d
----------- ----------- ----------- -----------
1 2 3 5
2 2 3 5(所影响的行数为 2 行)
**/
select 1,2,3,4
union all
select 1,2,2,5
union all
select 1,2,3,5
union all
select 2,2,3,5--select * from @aselect a,b,max(c) c,d from (select a,b,c,max(d) d from @a group by a,b,c) t group by a,b,d having max(c)<>2a b c d
----------- ----------- ----------- -----------
1 2 3 5
2 2 3 5(2 行受影响)
select 1,2,3,4
union all
select 1,2,2,5
union all
select 1,2,3,5
union all
select 2,2,3,5--select * from @aselect a,b,max(c) c,d from (select a,b,c,max(d) d from @a group by a,b,c) t where c<>2 group by a,b,d
/*
a b c d
----------- ----------- ----------- -----------
1 2 3 5
2 2 3 5(2 行受影响)
*/这样应该会快点
select 1,2,3,4
union all
select 1,2,2,5
union all
select 1,2,3,5
union all
select 2,2,3,5select T1.a,T1.b,max(T1.c) as c,T1.d
from @a as T1
inner join
(select a,b,max(d) as d
from @a
group by a,b) as T2
on T1.a=T2.a and T1.b=T2.b and T1.d=T2.d
where T1.c<>2
group by T1.a,T1.b,T1.d/*
1 2 3 5
2 2 3 5
*/
select 1,2,3,4
union all
select 1,2,2,5
union all
select 1,2,3,5
union all
select 2,2,3,5
;with
wang1 as (select * from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d)),
wang2 as (select a,b,c=max(c),d from wang1 group by a,b,d)
select * from wang2 where c<>2
select *
from ( select a,b,c=max(c),d from (select * from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d) )k group by a,b,d) p
where c<>2但是比较麻烦,第二种方法:
把上面的with 改成临时表,这样能好一点
select * into #1from @t t where not exists (select 1 from @t s where s.a=t.a and s.b=t.b and s.d>t.d)
select a,b,c=max(c),d into #1 from #1 group by a,b,d)
select * from #2 where c <>2
select 1,2,3,4
union all
select 1,2,2,5
union all
select 1,2,3,5
union all
select 2,2,3,5select a,b,c,d from @a a
where not exists(select 1 from @a where a=a.a and b=a.b and (d>a.d or d=a.d and c>a.c)) and c!=2
/*
1 2 3 5
2 2 3 5*/
那你就把where c<>2套在外面好了啊暈select *
from
(原來語句去掉where c<>2) A
where c<>2
这句话 能不能 加上括号 ,怎么个意思啊? d>a.d or d=a.d and c>a.c
where not exists(select 1 from @a where a=a.a and b=a.b and (d>a.d or d=a.d and c>a.c)) and c!=2
这种写法的话 建立什么样的索引 比较好,@a这个表中有100多万条数据建立索引也很慢
/*
什么意思 ? 举个例子,跟下面的题一样.设stu表,name列唯一,得到学生的成绩排名,首先按english比较,english相同时再按phycal比较.结果如下:语句原理:
统计表中,name不等于当前记录 的个数, 且条件满足于下:
eng > 当前eng 的记录,被算入
eng = 当前记录 eng 的情况下,如果 phy同时大于当前记录 phy 则,排名也较当前记录靠前 所以为 WHERE name != 当前行记录.name
AND
(eng > 当前行记录.eng
或者在
eng = 当前行记录.eng 时 并且 phy > 当前记录行.eng
) 楼主的这个问题,与我举给你的例子本质是一样的.
*/
DECLARE @stu TABLE(name VARCHAR(10), eng INT, phy INT)
INSERT @stu SELECT 'a',81,33
UNION ALL SELECT 'b',99,33
UNION ALL SELECT 'c',81,55SELECT
idx = 1+(SELECT COUNT(*) FROM @stu WHERE name!=a.name AND (eng>a.eng or eng = a.eng AND phy > a.phy) )
, *
FROM @stu a
ORDER BY idx/*
1 b 99 33
2 c 81 55
3 a 81 33*/速度很慢:
需求使然.索引建立:
尝试 a 上聚集索引
d,c上建立非聚集索引, d在为索引前导列.至于怎么样建最好,可以多做几份测试.
d,c上建复合索引, d为前导列.
select 1,2,2,6
union all
select 1,2,1,6
union all
select 1,2,3,6
union all
select 2,2,3,5
union all
select 2,3,2,6select T1.a,T1.b,substring(max(case when t1.c='2' then '0' else '1' end+cast(T1.c as varchar)),2,10) as c,T1.d
from @a as T1
inner join
(select a,b,max(d) as d
from @a
group by a,b) as T2
on T1.a=T2.a and T1.b=T2.b and T1.d=T2.d
group by T1.a,T1.b,T1.d