单据号 标识 值
01 1 8000
01 2 6000
02 1 7000
02 1 9000
02 2 6000
.... 得到所有值都大于6500的单据号和标识,如何操作即得到
单据号 标识
01 1
02 1
01 1 8000
01 2 6000
02 1 7000
02 1 9000
02 2 6000
.... 得到所有值都大于6500的单据号和标识,如何操作即得到
单据号 标识
01 1
02 1
from tablename
where 值 > 6500
select distinict 单据号,标识 from table where 值>6500
where 单据号 not in (select 单据号 from tb where 值<6000)
if object_id('A') is not null
drop table A
go
create table A(单据号 nvarchar(10),标识 int,值 int)
goinsert into A select
'01',1,8000 union all select
'01',2,6000 union all select
'02',1,7000 union all select
'02',1,9000 union all select
'02',2,6000 union all select
'02',2,7000
Select * from A
where 单据号 not in (select 单据号 from A where 值<6500)
if object_id('A') is not null
drop table A
go
create table A(单据号 nvarchar(10),标识 int,值 int)
goinsert into A select
'01',1,8000 union all select
'01',2,6000 union all select
'02',1,7000 union all select
'02',1,9000 union all select
'02',2,6000 union all select
'02',2,7000
Select DISTINCT 单据号,标识 from A
where 单据号 not in (select 单据号 from A where 值<6500)
Select DISTINCT 单据号,标识 from A
where (单据号+convert(nvarchar(1),标识)) not in (select (单据号+convert(nvarchar(1),标识)) from A where 值<6500)
drop table A
go
create table A(单据号 nvarchar(10),标识 int,值 int)
goinsert into A select
'01',1,8000 union all select
'01',2,6000 union all select
'02',1,7000 union all select
'02',1,9000 union all select
'02',2,6000 union all select
'02',2,7000
Select DISTINCT 单据号,标识 from A
where not exists
(select 1 from A a1 where a.单据号=a1.单据号 and a.标识=a1.标识 and a1.值<6500)drop table a单据号 标识
---------- -----------
01 1
02 1(2 行)
if object_id('A') is not null
drop table A
gocreate table A(单据号 nvarchar(10),标识 int,值 int)
goinsert into A select
'01',1,8000 union all select
'01',2,6000 union all select
'02',1,7000 union all select
'02',1,9000 union all select
'02',2,6000 union all select
'02',2,7000Select DISTINCT 单据号,标识 from A
where 单据号 in (select 单据号 from A where 值>=6500)