帮忙写个查询的SQL文

eversun820226 2011-10-19 01:41:22

table中有A,B,C,D四个字段，
1、如果ABCD都不为空，则取D的值
2、如果ABC不为空，D为空，则取C的值
3、如果AB不为空，CD为空，则取B的值
4、如果A不为空，BCD为空，则取A的值

这个SQL文怎么写

...全文

58 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

areswang 2011-10-19

打赏
举报



create table tb(a varchar(2),b varchar(2),c varchar(2),d varchar(2))

go

insert into tb select 1,'','',''

insert into tb select 1,null,null,null



set nocount on



--1

select case when ISNULL(a,'')<>'' and ISNULL(b,'')<>'' and ISNULL(c,'')<>'' and ISNULL(d,'')<>''

			then ISNULL(d,'')

			when ISNULL(a,'')<>'' and ISNULL(b,'')<>'' and ISNULL(c,'')<>'' and ISNULL(d,'')=''

			then ISNULL(c,'')

			when ISNULL(a,'')<>'' and ISNULL(b,'')<>'' and ISNULL(c,'')='' and ISNULL(d,'')=''

			then ISNULL(b,'')

			when ISNULL(a,'')<>'' and ISNULL(b,'')='' and ISNULL(c,'')='' and ISNULL(d,'')=''

			then ISNULL(a,'')

		end col

from tb



--2

SELECT coalesce(d,c,b,a) FROM tb

--3



SELECT isnull(isnull(isnull(d,c),b),a) FROM tb

-----------

col

----

1

1





----



1





----



1

eversun820226 2011-10-19

打赏
举报

还有这函数，强

快溜 2011-10-19

打赏
举报

isnull(isnull(isnull(d,c),b),a)

--小F-- 2011-10-19

打赏
举报

[Quote=引用 3 楼 roy_88 的回复:]
SQL code
SELECT coalesce(d,c,b,a) FROM TABLE1
[/Quote]

VERY GOOD 忘记这个了

中国风 2011-10-19

打赏
举报

SELECT coalesce(d,c,b,a) FROM TABLE1

--小F-- 2011-10-19

打赏
举报



SELECT

CASE WHEN A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL AND D IS NOT NULL THEN D

     WHEN A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL AND D IS NULL THEN C

     WHEN A IS NOT NULL AND B IS NOT NULL AND C IS NULL AND D IS NULL THEN B

     WHEN A IS NOT NULL AND B IS NULL AND C IS  NULL AND D IS NULL THEN A

END

FROM

    TB