select * into B from A where A.字段 is not null ????
select col1,col2 into B from A where col1 is not null and col2 is not null
select * into B from A where cola is not null and colb is not nulL
insert into B(select * from A where 字段 is not null)
1、 select isnull(col1,''),isnull(col2,'') from A2、 select col1,col2 from A where col1 is not null and col2 is not null是哪个呢
select distinct col1,col2 into B from A where col1 is not null and col2 is not null
补充一下上面的SQL。‘where’后面的字段是你要求不能为空的字段
select into 我知道 但我要的不是那个 表B 其实在数据库里并不存在 我只是假设 我的意思就是通过合并出来的这张表 我好做下一步查询 这是我自己写的 select DisIdPresence from CheckId where DisTime='2010年6月'and DisIdPresence is not null union select Disnotexsit from CheckId where DisTimeexist='2010年6月'and Disnotexsit is not null 这个问题就是UNION后两个字段变成一个字段了
为什么不这样做,而用union呢select DisIdPresence ,Disnotexsit from CheckId where DisTime='2010年6月'and DisIdPresence is not null and Disnotexsit is not null
select DisIdPresence,Disnotexsit from ( (select DisIdPresence from CheckId where DisTime='2010年6月'and DisIdPresence is not null) union (select Disnotexsit from CheckId where DisTimeexist='2010年6月'and Disnotexsit is not null)) 你们看看应该明白我的意思 我要的就是这个但是我的问题是我用union后字段合并成一个了数据都在一个字段里了
insert into B(select * from CheckId where DisIdPresence is not null and Disnotexsit is not null)
我写错了 是orselect DisIdPresence ,Disnotexsit from CheckId where DisTime='2010年6月'and (DisIdPresence is not null or Disnotexsit is not null )
select DisIdPresence,Disnotexsit from CheckId where DisTime='2010年6月' and DisIdPresence is not null OR Disnotexsit is not null 这样不行?
union 肯定合到一个字段。 要想分列 要用join的方式了。 但这个问题好像用一句就解决了吧?
嗯对的 我靠 用OR 我没想到 就光用and了 然后走弯路了~~~
select col1+col2 from a where col1 is not null or col2 is not null
create table #table1 ( dt varchar(20) null, people int ) declare @people int declare @dt varchar(20) declare @peo int declare cur cursor for select people from table1 open cur Fetch next From Cur into @people while @@fetch_status=0 Begin select @dt=dt,@peo=people from table1 where people=@people if(@dt is not null) insert into #table1(dt,people) values(@dt,@peo) Fetch Next From Cur Into @people end close cur Deallocate Cur go select * from #table1 go
?? select isnull(col1,'') col1,isnull(col2,'') col2 from [Table] where isnull(col1,'')+isnull(col2,'')<>''
你这是把NULL 变为空字符显示出来其实是一回事啊~!
我现在有一张表假设表A 现在里面有两个字段 我需要单独取出这两个字段的数据 并且把NULL排除掉 然后重新合并成一张表B 字段还是保留这两个 数据都不变 我该怎么做呢!!!很急CREATE VIEW B as SELECT ISNULL(A.F1, '') F1, ISNULL(A.F2, '') F2 FROM A GOSELECT * FROM B
我的这张表的脚本 USE [dow] GO /****** 对象: Table [dbo].[CheckID] 脚本日期: 06/21/2010 17:10:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CheckID]( [DistributiorID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [DisIdPresence] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [DisTime] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Disnotexsit] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [DisTimeexist] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] 数据我怎么给你提供呢? [DisIdPresence]|[Disnotexsit] csd111 | null kis99 | null wissi | null ksiq | null null | wkssa null | lssa null | assa上面的是我手绘的 看的懂吧?字段列 有数据的 和NULL值 现在我希望汇总起来的是除了NULL值的这两列作为一张表
--你要啥,你随便选 CREATE TABLE [dbo].[CheckID]( [DisIdPresence] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [DisTimeexist] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL ) INSERT checkid select 'csd111', null union all select 'kis99', null union all select 'wissi', null union all select 'ksiq', null union all select null, 'wkssa' union all select null, 'lssa' union all select null, 'assa' Go --1.两列合一列 select coalesce([DisIdPresence],'')+coalesce([DisTimeexist],'') from CheckID
--2.两列非空向上移 --2000: select id=identity(int,1,1),DisIdPresence into [#1] from checkid where DisIdPresence is not null select id=identity(int,1,1),DisTimeexist into [#2] from checkid where DisTimeexist is not null
select DisIdPresence,DisTimeexist from [#1] a full join [#2] b on a.id=b.id drop table [#1],[#2] --2008: select DisIdPresence,DisTimeexist from ( select row_number() over(order by getdate()) id,DisIdPresence from checkid where DisIdPresence is not null )aa full join ( select row_number() over(order by getdate()) id,DisTimeexist from checkid where DisTimeexist is not null )bb on aa.id=bb.id --result /* DisIdPresence DisTimeexist csd111 wkssa kis99 lssa wissi assa ksiq null */
去掉NULL值之后怎么合并?顺序是否有关系?
这是原始数据 得到的结果就是把NULL去了 还是这样的两列
向上移 我不是很明白 我一句SQL怎么写
select a.DisIdPresence,b.Disnotexsit from ( select rn = row_number()over(order by getdate()),DisIdPresence from CheckId where DisTime='2010年6月' and DisIdPresence is not null ) a full join ( select rn = row_number()over(order by getdate()),Disnotexsit from CheckId where DisTimeexist='2010年6月' and Disnotexsit is not null ) b on a.rn = b.rn
--楼上的脚本在2005中适用 --以下脚本在2000中使用 select id= identity(int,1,1),DisIdPresence into #1 from CheckId where DisTime='2010年6月' and DisIdPresence is not nullselect id= identity(int,1,1),Disnotexsit into #2 from CheckId where DisTimeexist='2010年6月' and Disnotexsit is not nullselect a.DisIdPresence,b.Disnotexsit from #1 a full join #2 b on a.rn = b.rn
2000 select col1,col2 into b from a where col1 is not null and col2 is not null
into B
from A
where A.字段 is not null
????
into B
from A
where cola is not null and colb is not nulL
1、
select isnull(col1,''),isnull(col2,'')
from A2、
select col1,col2
from A
where col1 is not null and col2 is not null是哪个呢
select distinct col1,col2 into B from A where col1 is not null and col2 is not null
但我要的不是那个 表B 其实在数据库里并不存在 我只是假设 我的意思就是通过合并出来的这张表 我好做下一步查询 这是我自己写的 select DisIdPresence from CheckId where DisTime='2010年6月'and DisIdPresence is not null union select Disnotexsit from CheckId where DisTimeexist='2010年6月'and Disnotexsit is not null 这个问题就是UNION后两个字段变成一个字段了
from CheckId
where DisTime='2010年6月'and DisIdPresence is not null and Disnotexsit is not null
你们看看应该明白我的意思 我要的就是这个但是我的问题是我用union后字段合并成一个了数据都在一个字段里了
是orselect DisIdPresence ,Disnotexsit
from CheckId
where DisTime='2010年6月'and (DisIdPresence is not null or Disnotexsit is not null )
from CheckId
where DisTime='2010年6月'
and DisIdPresence is not null
OR Disnotexsit is not null
这样不行?
union 肯定合到一个字段。 要想分列 要用join的方式了。
但这个问题好像用一句就解决了吧?
from a
where col1 is not null or col2 is not null
(
dt varchar(20) null,
people int
)
declare @people int
declare @dt varchar(20)
declare @peo int
declare cur cursor for
select people from table1
open cur
Fetch next From Cur into @people
while @@fetch_status=0
Begin
select @dt=dt,@peo=people from table1 where people=@people
if(@dt is not null)
insert into #table1(dt,people) values(@dt,@peo)
Fetch Next From Cur Into @people
end
close cur
Deallocate Cur
go
select * from #table1
go
select isnull(col1,'') col1,isnull(col2,'') col2
from [Table]
where isnull(col1,'')+isnull(col2,'')<>''
as
SELECT ISNULL(A.F1, '') F1, ISNULL(A.F2, '') F2 FROM A
GOSELECT * FROM B
USE [dow]
GO
/****** 对象: Table [dbo].[CheckID] 脚本日期: 06/21/2010 17:10:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CheckID](
[DistributiorID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DisIdPresence] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DisTime] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Disnotexsit] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DisTimeexist] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
数据我怎么给你提供呢?
[DisIdPresence]|[Disnotexsit]
csd111 | null
kis99 | null
wissi | null
ksiq | null
null | wkssa
null | lssa
null | assa上面的是我手绘的 看的懂吧?字段列 有数据的 和NULL值 现在我希望汇总起来的是除了NULL值的这两列作为一张表
csd111 | null
kis99 | null
wissi | null
ksiq | null
null | wkssa
null | lssa
null | assa
这是原始数据,你想得到什么结果
CREATE TABLE [dbo].[CheckID](
[DisIdPresence] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DisTimeexist] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
)
INSERT checkid select 'csd111', null
union all select 'kis99', null
union all select 'wissi', null
union all select 'ksiq', null
union all select null, 'wkssa'
union all select null, 'lssa'
union all select null, 'assa'
Go
--1.两列合一列
select coalesce([DisIdPresence],'')+coalesce([DisTimeexist],'') from CheckID
--2.两列非空向上移
--2000:
select id=identity(int,1,1),DisIdPresence into [#1]
from checkid
where DisIdPresence is not null
select id=identity(int,1,1),DisTimeexist into [#2]
from checkid
where DisTimeexist is not null
select DisIdPresence,DisTimeexist
from [#1] a full join [#2] b
on a.id=b.id
drop table [#1],[#2]
--2008:
select DisIdPresence,DisTimeexist from
(
select row_number() over(order by getdate()) id,DisIdPresence from checkid
where DisIdPresence is not null
)aa
full join
(
select row_number() over(order by getdate()) id,DisTimeexist from checkid
where DisTimeexist is not null
)bb
on aa.id=bb.id
--result
/*
DisIdPresence DisTimeexist
csd111 wkssa
kis99 lssa
wissi assa
ksiq null */
select a.DisIdPresence,b.Disnotexsit
from
(
select rn = row_number()over(order by getdate()),DisIdPresence
from CheckId
where DisTime='2010年6月'
and DisIdPresence is not null
) a
full join
(
select rn = row_number()over(order by getdate()),Disnotexsit
from CheckId
where DisTimeexist='2010年6月'
and Disnotexsit is not null
) b
on a.rn = b.rn
--楼上的脚本在2005中适用
--以下脚本在2000中使用
select id= identity(int,1,1),DisIdPresence
into #1
from CheckId
where DisTime='2010年6月'
and DisIdPresence is not nullselect id= identity(int,1,1),Disnotexsit
into #2
from CheckId
where DisTimeexist='2010年6月'
and Disnotexsit is not nullselect a.DisIdPresence,b.Disnotexsit
from #1 a
full join #2 b
on a.rn = b.rn
select col1,col2 into b from a where col1 is not null and col2 is not null