有这样一组数据
fjobseq fordseq fdzst
1 101 0
1 102 1
2 103 1
2 104 1
3 105 1
4 106 0
要得到下面的数据
fjobseq fordseq fdzst state
1 101 0 未回单
1 102 1 未回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单
原则跟据fjobseq,相同fjobseq的fdzst必须都为1,state才能为'已回单',只要有一个是0则state为'未回单'
fjobseq fordseq fdzst
1 101 0
1 102 1
2 103 1
2 104 1
3 105 1
4 106 0
要得到下面的数据
fjobseq fordseq fdzst state
1 101 0 未回单
1 102 1 未回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单
原则跟据fjobseq,相同fjobseq的fdzst必须都为1,state才能为'已回单',只要有一个是0则state为'未回单'
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 17:28:06
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([fjobseq] int,[fordseq] int,[fdzst] int)
insert #TB
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0
--------------开始查询--------------------------select *,
CASE WHEN NOT EXISTS(SELECT 1 FROM #TB
WHERE fjobseq=T.fjobseq AND fdzst=0) THEN '已回单' ELSE '未回单' END AS 状态
from #TB T
----------------结果----------------------------
/* (所影响的行数为 6 行)fjobseq fordseq fdzst 状态
----------- ----------- ----------- ------
1 101 0 未回单
1 102 1 未回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单(所影响的行数为 6 行)
*/
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 17:28:06
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([fjobseq] int,[fordseq] int,[fdzst] int)
insert #TB
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0
--------------开始查询--------------------------select *,
CASE WHEN EXISTS(SELECT 1 FROM #TB
WHERE fjobseq=T.fjobseq AND fdzst=0) THEN '未回单' ELSE '已回单' END AS 状态
from #TB T
----------------结果----------------------------
/* (所影响的行数为 6 行)fjobseq fordseq fdzst 状态
----------- ----------- ----------- ------
1 101 0 未回单
1 102 1 未回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单(所影响的行数为 6 行)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb] (fjobseq int,fordseq int,fdzst int)
insert into [tb]
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0select *,state=case when not exists(select 1 from tb where fjobseq=a.fjobseq and fdzst=0) then '已回单' else '未回单' end from [tb] a
go
create table #TB([fjobseq] int,[fordseq] int,[fdzst] int)
insert #TB
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0
select a.fjobseq,a.fordseq,case when b.fdzst = 1 THEN '已回单' ELSE '未回单' END AS 状态
from #tb a,(select fjobseq,min(fdzst) as fdzst from #tb group by fjobseq) b
where a.fjobseq = b.fjobseq
fjobseq fordseq 状态
----------- ----------- ------
1 101 未回单
1 102 未回单
2 103 已回单
2 104 已回单
3 105 已回单
4 106 未回单(所影响的行数为 6 行)
go
create table #TB([fjobseq] int,[fordseq] int,[fdzst] int)
insert #TB
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0select [fjobseq],[fordseq],[fdzst],
(case when
not exists(select 1 from #TB where [fdzst]=0 and [fjobseq]=T.[fjobseq])
then '已回单' else '未回单' end ) state
from #TB T
fjobseq fordseq fdzst state
----------- ----------- ----------- ------
1 101 0 未回单
1 102 1 未回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单(6 行受影响)
go
create table [tb]([fjobseq] int,[fordseq] int,[fdzst] int)
insert [tb]
select 1,101,0 union all
select 1,102,1 union all
select 2,103,1 union all
select 2,104,1 union all
select 3,105,1 union all
select 4,106,0select a.*,state=case when b.fordseq is null then '已回单' else '未回单' end
from tb a
left join(select fordseq from tb where fdzst=0) b
on a.fordseq=b.fordseq
--测试结果:
/*
fjobseq fordseq fdzst state
----------- ----------- ----------- ------
1 101 0 未回单
1 102 1 已回单
2 103 1 已回单
2 104 1 已回单
3 105 1 已回单
4 106 0 未回单(所影响的行数为 6 行)*/