TABLE1 F1 F2 F3 A 2 1 A 3 1 A 4 1 B 1 1 B 2 1 C 3 1 C 3 1 C 4 1 C 5 1请教一个SELECT语句,变成如下 A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 0 C 4 0 C 5 0不知道解释明白没有, 按照F2最小的, 就取1, 如果有2个最小的, 就随便取一个
select F1,F2,F3=CASE WHEN rn=1 THEN 1 ELSE 0 END from (select ROW_NUMBER() over(partition by F1 order by F2) as rn,* from table1 ) k
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([F1] varchar(1),[F2] int,[F3] int) insert [TB] select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1select id=identity(int,1,1),* into ## from TBselect f1,f2,f3=case when not exists(select 1 from ## where T.F1=F1 and T.id>id) then 1 else 0 end from ## t/* f1 f2 f3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 0 C 4 0 C 5 0(所影响的行数为 9 行)*/ drop table TB,##
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([F1] varchar(1),[F2] int,[F3] int) insert [TB] select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1select id=identity(int,1,1),* into #T from TB SELECT F1,F2,CASE WHEN NOT EXISTS(SELECT 1 FROM #T WHERE F1=T.F1 AND ID<T.ID) THEN 1 ELSE 0 END AS F3 FROM #T T--DROP TABLE #TF1 F2 F3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 0 C 4 0 C 5 0(所影响的行数为 9 行)
create table [TB]([F1] varchar(1),[F2] int,[F3] int) insert [TB] select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1select f1 , f2, case when f2 = (select min(f2) from tb where f1 = t.f1) then 1 else 0 end f3 from tb tdrop table tb/* f1 f2 f3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 1 C 4 0 C 5 0(所影响的行数为 9 行)*/
--存在相同的?得用临时表了.create table [TB]([F1] varchar(1),[F2] int,[F3] int) insert [TB] select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1select * , px = identity(int,1,1) into tmp from tbselect f1 , f2, case when (f2 = (select min(f2) from tmp where f1 = t.f1)) and (px = (select min(px) from tmp where f1 = t.f1 and f2 = t.f2)) then 1 else 0 end f3 from tmp tdrop table tb,tmp/* f1 f2 f3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 0 C 4 0 C 5 0(所影响的行数为 9 行)*/
最好就不用临时表了, 因为表中还有其他数据, 并且有用到PARTITION, OVER等函数.
select f1 , f2, case when f2 = (select min(f2) from tb where f1 = t.f1) then 1 else 0 end f3 from tb t======= 正解
有没有个办法将TABLE1里面的数据通过UPDATE语句, 变成后面的结果??
if object_id('[TB]') is not null drop table [TB] go create table [TB]([F1] varchar(1),[F2] int,[F3] int) insert [TB] select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1 go update tb set F3 = T1.F4 from (select F1 , F2 ,F3, F4 = (case when not exists(select 1 from tb where T.F1=F1 and T.F2>F2) then 1 else 0 end) from tb T)T1 where tb.F1 = T1.F1 and tb.F2 = T1.F2 and tb.F3 = T1.F3 select * from tb ----------------------------------- F1 F2 F3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 1 C 4 0 C 5 0(9 行受影响)
with test as ( select 'A' F1,2 F2,1 F3 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1 )select test.F1,test.F2,ISNULL(test2.F3,0) as F3 from test left join ( select F1,MIN(F2) F2,1 as F3 from test group by F1) test2 on test.F1 = test2.F1 and test.F2 = test2.F2F1 F2 F3 ---- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 1 C 4 0 C 5 0(9 行受影响)
select f1 , f2, case when f2 = (select min(f2) from tb where f1 = t.f1) then 1 else 0 end f3 from tb t
declare @tb table (f1 nvarchar(10),f2 int, f3 int) insert into @tb select 'A',2,1 union all select 'A',3,1 union all select 'A',4,1 union all select 'B',1,1 union all select 'B',2,1 union all select 'C',3,1 union all select 'C',3,1 union all select 'C',4,1 union all select 'C',5,1 select f1,f2,f3= case when exists (select 1 from @tb where f1=t.f1 and f2<t.f2) then 0 else 1 end from @tb tf1 f2 f3 ---------- ----------- ----------- A 2 1 A 3 0 A 4 0 B 1 1 B 2 0 C 3 1 C 3 1 C 4 0 C 5 0(9 行受影响)
select F1,F2,F3=CASE WHEN rn=1 THEN 1 ELSE 0 END
from
(select ROW_NUMBER() over(partition by F1 order by F2) as rn,* from table1 ) k
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([F1] varchar(1),[F2] int,[F3] int)
insert [TB]
select 'A',2,1 union all
select 'A',3,1 union all
select 'A',4,1 union all
select 'B',1,1 union all
select 'B',2,1 union all
select 'C',3,1 union all
select 'C',3,1 union all
select 'C',4,1 union all
select 'C',5,1select id=identity(int,1,1),* into ## from TBselect f1,f2,f3=case when not exists(select 1 from ## where T.F1=F1 and T.id>id) then 1 else 0 end
from ## t/*
f1 f2 f3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 0
C 4 0
C 5 0(所影响的行数为 9 行)*/
drop table TB,##
if object_id('[TB]') is not null drop table [TB]
create table [TB]([F1] varchar(1),[F2] int,[F3] int)
insert [TB]
select 'A',2,1 union all
select 'A',3,1 union all
select 'A',4,1 union all
select 'B',1,1 union all
select 'B',2,1 union all
select 'C',3,1 union all
select 'C',3,1 union all
select 'C',4,1 union all
select 'C',5,1select id=identity(int,1,1),* into #T from TB
SELECT F1,F2,CASE WHEN
NOT EXISTS(SELECT 1 FROM #T WHERE F1=T.F1 AND ID<T.ID) THEN 1 ELSE 0 END AS F3
FROM #T T--DROP TABLE #TF1 F2 F3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 0
C 4 0
C 5 0(所影响的行数为 9 行)
insert [TB]
select 'A',2,1 union all
select 'A',3,1 union all
select 'A',4,1 union all
select 'B',1,1 union all
select 'B',2,1 union all
select 'C',3,1 union all
select 'C',3,1 union all
select 'C',4,1 union all
select 'C',5,1select f1 , f2,
case when f2 = (select min(f2) from tb where f1 = t.f1) then 1 else 0 end f3
from tb tdrop table tb/*
f1 f2 f3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 1
C 4 0
C 5 0(所影响的行数为 9 行)*/
insert [TB]
select 'A',2,1 union all
select 'A',3,1 union all
select 'A',4,1 union all
select 'B',1,1 union all
select 'B',2,1 union all
select 'C',3,1 union all
select 'C',3,1 union all
select 'C',4,1 union all
select 'C',5,1select * , px = identity(int,1,1) into tmp from tbselect f1 , f2,
case when (f2 = (select min(f2) from tmp where f1 = t.f1)) and (px = (select min(px) from tmp where f1 = t.f1 and f2 = t.f2)) then 1 else 0 end f3
from tmp tdrop table tb,tmp/*
f1 f2 f3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 0
C 4 0
C 5 0(所影响的行数为 9 行)*/
case when f2 = (select min(f2) from tb where f1 = t.f1) then 1 else 0 end f3
from tb t=======
正解
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([F1] varchar(1),[F2] int,[F3] int)
insert [TB]
select 'A',2,1 union all
select 'A',3,1 union all
select 'A',4,1 union all
select 'B',1,1 union all
select 'B',2,1 union all
select 'C',3,1 union all
select 'C',3,1 union all
select 'C',4,1 union all
select 'C',5,1
go
update tb set F3 = T1.F4
from
(select F1 , F2 ,F3, F4 = (case when not exists(select 1 from tb where T.F1=F1 and T.F2>F2) then 1 else 0 end)
from tb T)T1
where tb.F1 = T1.F1 and tb.F2 = T1.F2 and tb.F3 = T1.F3
select * from tb
-----------------------------------
F1 F2 F3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 1
C 4 0
C 5 0(9 行受影响)
with test as
(
select 'A' F1,2 F2,1 F3
union all select 'A',3,1
union all select 'A',4,1
union all select 'B',1,1
union all select 'B',2,1
union all select 'C',3,1
union all select 'C',3,1
union all select 'C',4,1
union all select 'C',5,1
)select test.F1,test.F2,ISNULL(test2.F3,0) as F3
from test left join (
select F1,MIN(F2) F2,1 as F3
from test
group by F1) test2 on test.F1 = test2.F1 and test.F2 = test2.F2F1 F2 F3
---- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 1
C 4 0
C 5 0(9 行受影响)
f3 int)
insert into @tb select 'A',2,1
union all select 'A',3,1
union all select 'A',4,1
union all select 'B',1,1
union all select 'B',2,1
union all select 'C',3,1
union all select 'C',3,1
union all select 'C',4,1
union all select 'C',5,1
select f1,f2,f3= case when exists (select 1 from @tb where f1=t.f1 and f2<t.f2) then 0 else 1 end
from @tb tf1 f2 f3
---------- ----------- -----------
A 2 1
A 3 0
A 4 0
B 1 1
B 2 0
C 3 1
C 3 1
C 4 0
C 5 0(9 行受影响)