如题,在一个表中查询不同属性的值,然后,列在一个新的表里,我有一种方法可以实现,但是,感觉有点笨拙,看看各位有没有好的写法。谢谢。select name from article where(type=1) or (type=2) or (type=3) or (type=4) or (type=5) or (type=6) 有没有更简便的方法
select name from article where type in (1,2,3,4,5,6)
select name from article where type between 1 and 6
select name from article where type between 1 and 6
select name from article where type in (1,2,3,4,5,6)
declare @a table (name varchar(1),type int) insert into @a select 'a',1 union all select 'b',1 union all select 'c',2 union all select 'd',2 union all select 'e',3 union all select 'f',7 union all select 'g',8 union all select 'h',9 union all select 'j',10declare @b table (typeid int,col varchar(2)) insert into @b select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd' union all select 5,'ee' union all select 6,'ff' --第一种 select * from @a where type in(select typeid from @b) --第二种 select a.* from @a a left join @b b on a.type=b.typeid where b.typeid is not null/* name type ---- ----------- a 1 b 1 c 2 d 2 e 3 */
select name from article where type<7
select top(10-4) name from atticle where type not in (select top(10-10) name from article)--取前6行 去掉里面的 前(10-10)行,
declare @a table (name varchar(1),type int)
insert into @a
select 'a',1 union all
select 'b',1 union all
select 'c',2 union all
select 'd',2 union all
select 'e',3 union all
select 'f',7 union all
select 'g',8 union all
select 'h',9 union all
select 'j',10declare @b table (typeid int,col varchar(2))
insert into @b
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd' union all
select 5,'ee' union all
select 6,'ff'
--第一种
select * from @a where type in(select typeid from @b)
--第二种
select a.* from @a a left join @b b on a.type=b.typeid
where b.typeid is not null/*
name type
---- -----------
a 1
b 1
c 2
d 2
e 3
*/
select top(10-4) name from atticle where type not in (select top(10-10) name from article)--取前6行 去掉里面的 前(10-10)行,