SQL中 常用的处理方法（处理空值、更新字段、去掉小数后的0、查询重复列）

 -- 一： 处理未查到的空值

 select
 case when exists(select EndPrice from T_stock where BOMSN='0109')
 then (select EndPrice from T_stock where BOMSN='0109')
 else 0 end

 --二：更新字段值

 --strNum 数量
 --strId  序号
 update T_buy_Pro set CompentNum=isnull(CompentNum,0)+'strNum' ,
 Status= case when isnull(BuyNumber,0) > isnull(CompentNum,0)+ 'strNum'
 then '部分收料' else '全部收料' end
 where BuyProID='strId'

  --三：去掉小数点有效数字后面的所有0

decimal类型，小数位数是6位，如果插入的数据为2.5，则检索时显示为2.500000

select   cast(2.500000       as     real)

----------建一個函數完成-------------   
  Create     Function     trun(@i     decimal(18,6))   
  returns       varchar(30)   
  begin     
  declare     @s     varchar(30)   
  set     @s=cast(@i     as   varchar(30))   
  while   len(@s)>0   
  begin           
            if   cast(substring(rtrim(@s),len(rtrim(@s))-1,1)     as     int)<>0         
            begin   
                    set     @s=substring(rtrim(@s),1,len(@s)-1)   
                    break   
            end   
            set     @s=substring(rtrim(@s),1,len(@s)-1)   
  end   
        return(@s)   
  end   
  ----------------------------結束函數-----------   
    
  select     dbo.trun(12.26000)                   ----------測試函數   
  ---------------輸出   
  12.26   
  drop   function     trun

函數修改一點點就行了   
  -----------建一個函數完成-------------   
  Create     Function     trun(@i     decimal(18,6))   
  returns       varchar(30)   
  begin     
  declare     @s     varchar(30)   
  set     @s=cast(@i     as   varchar(30))   
  while   len(@s)>0   
  begin           
            if   cast(substring(rtrim(@s),len(rtrim(@s))-1,1)     as     varchar)<>'0'         
            begin   
                      if   cast(substring(rtrim(@s),len(rtrim(@s))-1,1)     as     varchar)='.'         
                            set     @s=substring(rtrim(@s),1,len(@s)-2)   
                    else   
                              set     @s=substring(rtrim(@s),1,len(@s)-1)   
                    break   
            end   
            set     @s=substring(rtrim(@s),1,len(@s)-1)   
  end   
        return(@s)   
  end   
  ----------------------------結束函數-----------   
    
  select     dbo.trun(120.010)                   ----------測試函數   
  select     dbo.trun(1200.0000)                   ----------測試函數   
  ---------------輸出   
    
  drop   function     trun   

 --四：查询重复列 去除重复列且排序

select a,Ntitle from
(
select COUNT(1) as a, Ntitle from T_Acurity group by Ntitle
) va
where va.a>1

 

create view v_MaxOrder
as
select * from Tsaleordermain as a  where
not exists(select i_typeOrder from Tsaleordermain as b where a.n_orderSN= b.n_orderSN
and a.i_typeOrder< b.i_typeOrder)

--order by n_ordernum, i_typeOrder

--五：批量删除大于10天以前的记录

字段是时间 shijian

sql="delete from bot where dateadd(day,10,shijian)<getdate()";

 

 

 

DATEADD (datepart , number, date )

--六：数据库中的日期批量加上1年

 

update tb set dt = dateadd(yy,1,dt)

 

 

 

 --七：Union与Union All的区别

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。

 

A-B,
B-C,
D-E,
E-F。
求替代料关系出现的一个问题，上面两列数据，想变成。
1,A
1,B
1,C
2,D
2,E
2,F
就是互为替代料的分为一组。

 

方法一

 create table tb(c varchar(10))
insert tb
select 'A-B' union all
select 'B-C' union all
select 'D-E' union all
select 'E-F'

with t1 as(
select left(c,1) c1,right(c,1) c2 from tb)

--select c1,c2 from t1 where c1 not in (select c2 from t1)

,t2 as(
select c1,c2,rn=row_number()over(order by getdate()) from t1 where c1 not in (select c2 from t1)
union all
select b.c1,b.c2,a.rn from t2 a,t1 b where b.c1=a.c2
union all
select b.c2,b.c2,a.rn from t2 a,t1 b where b.c1=a.c2 and b.c2 not in (select c1 from t1)
)

select rn,c1 from t2
order by rn,c1

 

方法二

 

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([c1] [nvarchar](10),[c2] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A','B' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'D','E' UNION ALL
SELECT 'E','F'

--SELECT * FROM [tb]

-->SQL查询如下:
;WITH T AS
(
    SELECT RN=ROW_NUMBER()OVER(ORDER BY C1),*
    FROM TB T
    WHERE NOT EXISTS(SELECT 1 FROM TB WHERE T.C1=C2)
    UNION ALL
    SELECT B.RN,A.*
    FROM TB A,T B
    WHERE A.C1=B.C2
)
SELECT DISTINCT RN,TYPE
FROM T
    UNPIVOT (TYPE FOR C IN(C1,C2)) UNP

方法三

 

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([c] [nvarchar](10))
INSERT TB
SELECT 'A-B' UNION ALL
SELECT 'B-C' UNION ALL
SELECT 'C-CC' UNION ALL
SELECT 'CC-H' UNION ALL
SELECT 'D-E' UNION ALL
SELECT 'E-F'

--SELECT * FROM [tb]

-->SQL查询如下:
;WITH TA AS
(
    SELECT CAST(LEFT(C,CHARINDEX('-',C)-1) AS VARCHAR(100)) C1,
        CAST(STUFF(C,1,CHARINDEX('-',C),'') AS VARCHAR(100)) C2
    FROM TB
)
,T AS
(
    SELECT RN=ROW_NUMBER()OVER(ORDER BY C1),* FROM TA T
    WHERE NOT EXISTS(SELECT 1 FROM TA WHERE T.C1=C2)
    UNION ALL
    SELECT B.RN,A.* FROM TA A,T B WHERE A.C1=B.C2
)
SELECT DISTINCT RN,TYPE
FROM T
    UNPIVOT (TYPE FOR C IN(C1,C2)) UNP

方法四

SET NOCOUNT ON
DECLARE @a TABLE(a VARCHAR(10),id INT IDENTITY)
INSERT @a SELECT 'A-B'
union all select 'B-C'
union all select 'D-E'
union all select 'E-F'
union all select 'F-G'

DECLARE @s TABLE(id INT,a VARCHAR(20),c VARCHAR(20))

INSERT @s
SELECT id,s,a FROM (SELECT a, LEFT(a,1) s,id FROM @a a
UNION ALL
SELECT a,RIGHT(a,1) s,id from @a a)aa
ORDER BY id

DECLARE @i INT ,@m INT
SELECT @m=1,@i=2
DECLARE @t TABLE(id INT,a VARCHAR(20),c VARCHAR(20))
INSERT @t SELECT * FROM @s WHERE id=@m

WHILE EXISTS(SELECT 1 FROM @s WHERE id=@i)
BEGIN
    IF NOT  EXISTS(SELECT 1 FROM @s a INNER JOIN @t t ON charindex(a.a,t.c) >0  AND a.id=@i)
        SET @m=@m+1
   
    INSERT @t SELECT @m,a,c FROM @s WHERE id=@i       
    SET @i=@i+1
END
SELECT DISTINCT id,a FROM @t