34,576
社区成员
发帖
与我相关
我的任务
分享
use students3;
go
if object_id('tb3')is not null
drop table tb3;
go
create table tb3
(
code varchar(5),
name varchar(20),
type varchar(20)
)
insert into tb3
select '101','金丝柚','半成品' union all
select '102','黄云香','半成品' union all
select '103','白枫','半成品' union all
select '201','水曲柳','成品' union all
select '201-7','不织布','成品' union all
select '203','泰柚','成品' union all
select '248-1', '加工珍珠', NULL union all
select '301' ,'花梨' ,'普通木片' union all
select '302' ,'泰柚' ,'普通木片' union all
select '303' ,'人造泰柚', '普通木片' union all
select '401', '白板', '夹板' union all
select '402' ,'面板' ,'夹板'
select * from tb3 where type<>'半成品'and type<>'成品'
/*code name type
----- -------------------- --------------------
301 花梨 普通木片
302 泰柚 普通木片
303 人造泰柚 普通木片
401 白板 夹板
402 面板 夹板
*/
没有问题啊create table [tb]([code] varchar(5),[name] varchar(8),[type] varchar(8))
insert [tb]
select '101','金丝柚','半成品' union all
select '102','黄云香','半成品' union all
select '103','白枫','半成品' union all
select '201','水曲柳','成品' union all
select '201-7','不织布','成品' union all
select '203','泰柚','成品' union all
select '248-1','加工珍珠',null union all
select '301','花梨','普通木片' union all
select '302','泰柚','普通木片' union all
select '303','人造泰柚','普通木片' union all
select '401','白板','夹板' union all
select '402','面板','夹板'
go
select * from tb where [type]<>'半成品' and [type]<>'成品' or [type] is null
/*
code name type
----- -------- --------
248-1 加工珍珠 NULL
301 花梨 普通木片
302 泰柚 普通木片
303 人造泰柚 普通木片
401 白板 夹板
402 面板 夹板
(6 行受影响)
*/
go
drop table tb
select * from tb where [type]<>'半成品' and [type]<>'成品'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(5),[name] varchar(8),[type] varchar(8))
insert [tb]
select '101','金丝柚','半成品' union all
select '102','黄云香','半成品' union all
select '103','白枫','半成品' union all
select '201','水曲柳','成品' union all
select '201-7','不织布','成品' union all
select '203','泰柚','成品' union all
select '248-1','加工珍珠',null union all
select '301','花梨','普通木片' union all
select '302','泰柚','普通木片' union all
select '303','人造泰柚','普通木片' union all
select '401','白板','夹板' union all
select '402','面板','夹板'
select * from tb where [type]<>'半成品' and [type]<>'成品'
/*
code name type
----- -------- --------
301 花梨 普通木片
302 泰柚 普通木片
303 人造泰柚 普通木片
401 白板 夹板
402 面板 夹板
(5 行受影响)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 17:15:53
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(5),[name] varchar(8),[type] varchar(8))
insert [tb]
select '101','金丝柚','半成品' union all
select '102','黄云香','半成品' union all
select '103','白枫','半成品' union all
select '201','水曲柳','成品' union all
select '201-7','不织布','成品' union all
select '203','泰柚','成品' union all
select '248-1','加工珍珠',null union all
select '301','花梨','普通木片' union all
select '302','泰柚','普通木片' union all
select '303','人造泰柚','普通木片' union all
select '401','白板','夹板' union all
select '402','面板','夹板'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where (type='成品' or type='半成品') and code=t.code and name=t.name)
----------------结果----------------------------
/*code name type
----- -------- --------
248-1 加工珍珠 NULL
301 花梨 普通木片
302 泰柚 普通木片
303 人造泰柚 普通木片
401 白板 夹板
402 面板 夹板
(6 行受影响)
*/
select * from goods where not (type='半成品' or type='成品')
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 17:15:53
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(5),[name] varchar(8),[type] varchar(8))
insert [tb]
select '101','金丝柚','半成品' union all
select '102','黄云香','半成品' union all
select '103','白枫','半成品' union all
select '201','水曲柳','成品' union all
select '201-7','不织布','成品' union all
select '203','泰柚','成品' union all
select '248-1','加工珍珠',null union all
select '301','花梨','普通木片' union all
select '302','泰柚','普通木片' union all
select '303','人造泰柚','普通木片' union all
select '401','白板','夹板' union all
select '402','面板','夹板'
--------------开始查询--------------------------
select * from tb where type not in ('半成品','成品')
----------------结果----------------------------
/* code name type
----- -------- --------
301 花梨 普通木片
302 泰柚 普通木片
303 人造泰柚 普通木片
401 白板 夹板
402 面板 夹板
(5 行受影响)
*/