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