有一表如下
c1 c2 c3 c4 ......
------------------------------
a b null 12
a c null 20
b c e 30
d null k 25
a sd ds 51
......要求查询c1+c2+c3 as cc “c1,c2,c3为string c4为int数据类型”
当其中一个为null时 cc=c4
比如上面五行要求的结果为:
cc ....
-------------
12
20
bce
25
asdds不知我说的是否明白
谢谢~~
c1 c2 c3 c4 ......
------------------------------
a b null 12
a c null 20
b c e 30
d null k 25
a sd ds 51
......要求查询c1+c2+c3 as cc “c1,c2,c3为string c4为int数据类型”
当其中一个为null时 cc=c4
比如上面五行要求的结果为:
cc ....
-------------
12
20
bce
25
asdds不知我说的是否明白
谢谢~~
假设你的表的名字为ABC
select (c1+c2+c3) as cc FROM ABC WHERE NOT((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
UNION ALL
SELECT C4 AS CC FROM ABC WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))你试试看.成不.不成再说一声.我来仔细研究研究
服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 'a b c ' 转换为数据类型为 int 的列时发生语法错误。
SELECT AAA.IDNO,CC FROM (
select IDNO,(c1+c2+c3) as cc FROM ABC WHERE NOT((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
UNION ALL
SELECT IDNO,C4 AS CC FROM ABC WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
) AS AAA ORDER BY AAA.IDNO
UNION ALL
SELECT CAST(C4 as varchar(10)) AS CC FROM testcc WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
order by c0服务器: 消息 104,级别 15,状态 1,行 1
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。不能排序的:(
SELECT AAA.IDNO,CC FROM (
select IDNO,(c1+c2+c3) as cc FROM ABC WHERE NOT((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
UNION ALL
SELECT IDNO,STR(C4) AS CC FROM ABC WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
) AS AAA ORDER BY AAA.IDNO
这样就不报错了,把数字转换趁字符
SELECT AAA.c0,CC FROM (
select c0,(c1+c2+c3) as cc FROM ABC WHERE NOT((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
UNION ALL
SELECT c0,STR(C4) AS CC FROM ABC WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
) AS AAA ORDER BY AAA.c0注意扩号,SELECT AAA.c0,CC FROM ( ... ) AS AAA ORDER BY AAA.c0
... <-- 是一个查询哟.
select c0,(c1+c2+c3) as cc FROM testcc WHERE NOT((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
UNION ALL
SELECT c0,cast(C4 as varchar(10)) AS CC FROM testcc WHERE ((C1 IS NULL) OR (C2 IS NULL) OR (C3 IS NULL))
) AS AAA ORDER BY AAA.c0
呵呵,谢谢了
成功!
我用了cast
:(
Insert T1 Select 'a','b',NULL,12
Union all Select 'a','c',Null,20
Union all Select 'b','c','e',30
Union all Select 'd',Null,'k',25
Union all Select 'a','sd','ds',51
--Test
Select
CASE
WHEN ((C1 IS NOT NULL) And (C2 Is Not Null) And (C3 IS Not Null)) THEN C1+C2+C3
Else Rtrim(C4) End As CC
From T1