有个表格含有以下字段
itm mcu locn lotn pbin
1 m30 d01 s
1 M40 P
2 M30 P
2 M30 北京 S
10 M30 上海 S
11 M40 S要求每一个 itm+mcu的组合 必须对应一个pbin=p的纪录。即1+M30 必须对应一个PBIN=P的纪录。
现在想查找 itm+Mcu没有对应PBIN=P的纪录。应该如何查找?
itm mcu locn lotn pbin
1 m30 d01 s
1 M40 P
2 M30 P
2 M30 北京 S
10 M30 上海 S
11 M40 S要求每一个 itm+mcu的组合 必须对应一个pbin=p的纪录。即1+M30 必须对应一个PBIN=P的纪录。
现在想查找 itm+Mcu没有对应PBIN=P的纪录。应该如何查找?
--这样?
--> 测试数据: @s
declare @s table (itm int,mcu varchar(3),locn varchar(4),lotn varchar(1),pbin sql_variant)
insert into @s
select 1,'m30','d01',null,'s' union all
select 1,'M40',null,null,'P' union all
select 2,'M30',null,null,'P' union all
select 2,'M30','北京',null,'S' union all
select 10,'M30','上海',null,'S' union all
select 11,'M40',null,null,'S'select * from @s a where not exists(select 1 from @s where itm=a.itm and mcu=a.mcu and pbin='p')
where not exists (select from tb where itm=t.itm and mcu=t.mcu and PBIN=P )
Pbin<>p 还能选择出来 没看懂
INSERT @TB
SELECT 1, 'm30', N'd01', N's' UNION ALL
SELECT 1, 'M40', N'', N'P' UNION ALL
SELECT 2, 'M30', N'', N'P' UNION ALL
SELECT 2, 'M30', N'北京', N'S' UNION ALL
SELECT 10, 'M30', N'上海', N'S' UNION ALL
SELECT 11, 'M40', N'', N'S'SELECT *
FROM @TB AS T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE [itm]=T.[itm] AND [mcu]=T.[mcu] AND pbin='P')
/*
itm mcu locn pbin
----------- ---- ---- ----
1 m30 d01 s
10 M30 上海 S
11 M40 S
*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GOcreate table tb (itm int,mcu varchar(3),locn varchar(4),lotn varchar(1),pbin sql_variant)
insert into tb
select 1,'m30','d01',null,'s' union all
select 1,'M40',null,null,'P' union all
select 2,'M30',null,null,'P' union all
select 2,'M30','北京',null,'S' union all
select 10,'M30','上海',null,'S' union all
select 11,'M40',null,null,'S'
select * from tb t
where not exists (select * from tb where itm=t.itm and mcu=t.mcu and PBIN='P' )
/*------------
itm mcu locn lotn pbin
----------- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 m30 d01 NULL s
10 M30 上海 NULL S
11 M40 NULL NULL S(3 行受影响)
-------*/
select * from tb where pbin<>'P'
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-10 09:49:34
----------------------------------------------------------------
--> 测试数据:[tb]
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GOcreate table tb (itm int,mcu varchar(3),locn varchar(4),lotn varchar(1),pbin sql_variant)
insert into tb
select 1,'m30','d01',null,'s' union all
select 1,'M40',null,null,'P' union all
select 2,'M30',null,null,'P' union all
select 2,'M30','北京',null,'S' union all
select 10,'M30','上海',null,'S' union all
select 11,'M40',null,null,'S'
--------------开始查询--------------------------
select * from tb t
where not exists (select * from tb where itm=t.itm and mcu=t.mcu and PBIN='P' )
----------------结果----------------------------
/*itm mcu locn lotn pbin
----------- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 m30 d01 NULL s
10 M30 上海 NULL S
11 M40 NULL NULL S(所影响的行数为 3 行)
*/