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

最新推荐文章于 2025-07-07 09:53:36 发布

原创最新推荐文章于 2025-07-07 09:53:36 发布 · 6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #table #c #function #object

本文探讨了数据库中处理空值、更新字段值、去除小数点后多余零、使用函数处理数值、查询重复项、删除过期记录、日期操作等SQL技巧，并展示了通过Union与UnionAll区别来实现互为替代料数据的整理方法。

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

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