SELECT T1.* CASE WHEN T2.C > 0 THEN 1 ELSE 0 END AS status FROM 表1 T1 JOIN ( SELECT numcode, count(*) AS C FROM 表1 GROUP BY numcode ) T2 ON T1.numcode = T2.numcode ORDER BY T1.numcode
declare @t table(numcode varchar(10),numname varchar(10),numvalue float) insert into @t(numcode,numname,numvalue) select '1001','a',1.1 union all select '1002','b',1.2 union all select '1003','c',1.3 union all select '1004','d',1.4 union all select '1004','e',1.5 select ta.*, status=case when tb.counts is null then 0 else 1 end from @t ta left join (select numcode,count(1) as counts from @t group by numcode having count(1)>1 ) tb on ta.numcode=tb.numcode numcode numname numvalue status ---------- ---------- ---------------------- ----------- 1001 a 1.1 0 1002 b 1.2 0 1003 c 1.3 0 1004 d 1.4 1 1004 e 1.5 1(5 行受影响)
create table #t (numcode int,numname varchar(10),numvalue decimal(5,1))insert into #t select 1001,'A',1.1 union all select 1002,'B',1.2 union all select 1003,'C',1.3 union all select 1004,'D',1.4 union all select 1004,'E',1.5 with t as (select *,row_number() over(order by numcode) 'rn' from #t) select a.numcode,a.numname,a.numvalue, case when a.numcode=b.numcode or a.numcode=c.numcode then 1 else 0 end 'status' from t a left join t b on a.rn=b.rn+1 left join t c on a.rn=c.rn-1/* numcode numname numvalue status ----------- ---------- ------------- ----------- 1001 A 1.1 0 1002 B 1.2 0 1003 C 1.3 0 1004 D 1.4 1 1004 E 1.5 1(5 row(s) affected) */
测试可行: create table test (numcode char(4), numname char(2), numvalue decimal(4,1))insert into test values('1001','A',1.1), ('1002','B',1.2), ('1003','C',1.3), ('1004','D',1.4), ('1004','E',1.5), ('1005','F',1.6), ('1005','G',1.7)with ta as (select numcode from test group by numcode having COUNT(numcode)>1) select a.numcode,a.numname,a.numvalue, case when b.numcode IS null then 0 else 1 end as [status] from test a left join ta b on a.numcode=b.numcode order by a.numcode
SELECT T1.*
CASE WHEN T2.C > 0
THEN 1
ELSE 0
END AS status
FROM 表1 T1
JOIN ( SELECT numcode, count(*) AS C
FROM 表1
GROUP BY numcode
) T2
ON T1.numcode = T2.numcode
ORDER BY T1.numcode
declare @t table(numcode varchar(10),numname varchar(10),numvalue float)
insert into @t(numcode,numname,numvalue)
select '1001','a',1.1
union all
select '1002','b',1.2
union all
select '1003','c',1.3
union all
select '1004','d',1.4
union all
select '1004','e',1.5
select ta.*, status=case when tb.counts is null then 0 else 1 end
from @t ta left join (select numcode,count(1) as counts from @t
group by numcode
having count(1)>1 ) tb
on ta.numcode=tb.numcode
numcode numname numvalue status
---------- ---------- ---------------------- -----------
1001 a 1.1 0
1002 b 1.2 0
1003 c 1.3 0
1004 d 1.4 1
1004 e 1.5 1(5 行受影响)
create table #t
(numcode int,numname varchar(10),numvalue decimal(5,1))insert into #t
select 1001,'A',1.1 union all
select 1002,'B',1.2 union all
select 1003,'C',1.3 union all
select 1004,'D',1.4 union all
select 1004,'E',1.5
with t as
(select *,row_number() over(order by numcode) 'rn'
from #t)
select a.numcode,a.numname,a.numvalue,
case when a.numcode=b.numcode or a.numcode=c.numcode then 1 else 0 end 'status'
from t a
left join t b on a.rn=b.rn+1
left join t c on a.rn=c.rn-1/*
numcode numname numvalue status
----------- ---------- ------------- -----------
1001 A 1.1 0
1002 B 1.2 0
1003 C 1.3 0
1004 D 1.4 1
1004 E 1.5 1(5 row(s) affected)
*/
create table test
(numcode char(4),
numname char(2),
numvalue decimal(4,1))insert into test
values('1001','A',1.1),
('1002','B',1.2),
('1003','C',1.3),
('1004','D',1.4),
('1004','E',1.5),
('1005','F',1.6),
('1005','G',1.7)with ta as
(select numcode from test group by numcode having COUNT(numcode)>1)
select a.numcode,a.numname,a.numvalue,
case when b.numcode IS null then 0 else 1 end as [status] from test a
left join ta b on a.numcode=b.numcode
order by a.numcode
不对啊,人家要的是相邻的才变1
要会分析啊!
按 numcode 排序时,相邻、相等。不就等价与同一个 numcode 记录数不唯一!