select case ( a when a is not null then a else b end) from my_table错在哪
时间: 2024-05-13 18:19:50 浏览: 11
在 SQL 语法中,CASE 表达式应该是这样的:
```
SELECT CASE WHEN a IS NOT NULL THEN a ELSE b END FROM my_table;
```
其中,CASE 表达式的语法是:
```
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
ELSE default_result
END
```
在这个例子中,我们使用了 WHEN 和 THEN 子句来定义条件和结果,并在 CASE 表达式的结尾使用了 ELSE 子句来定义默认结果。注意,CASE 表达式必须以 END 结尾。
另外,根据 SQL 标准,NULL 值之间不能使用等于运算符来比较,应该使用 IS NULL 或 IS NOT NULL。因此,我们在这个例子中使用了 IS NOT NULL 来判断 a 是否为 NULL。
相关问题
你能把pg视图改为sqlserver的视图吗 SELECT c.relname AS table_name, a.attname AS column_name, d.description AS column_comment, CASE WHEN a.attnotnull AND con.conname IS NULL THEN 1 ELSE 0 END AS is_required, CASE WHEN con.conname IS NOT NULL THEN 1 ELSE 0 END AS is_pk, a.attnum AS sort, CASE WHEN "position"(pg_get_expr(ad.adbin, ad.adrelid), ((c.relname::text || '_'::text) || a.attname::text) || '_seq'::text) > 0 THEN 1 ELSE 0 END AS is_increment, btrim( CASE WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text ELSE CASE WHEN t.typtype = 'd'::"char" THEN format_type(t.typbasetype, NULL::integer) ELSE format_type(a.atttypid, NULL::integer) END END, '"'::text) AS column_type FROM pg_attribute a JOIN (pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid) ON a.attrelid = c.oid LEFT JOIN pg_description d ON d.objoid = c.oid AND a.attnum = d.objsubid LEFT JOIN pg_constraint con ON con.conrelid = c.oid AND (a.attnum = ANY (con.conkey)) LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum LEFT JOIN pg_type t ON a.atttypid = t.oid WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) AND a.attnum > 0 AND n.nspname = 'public'::name ORDER BY c.relname, a.attnum
抱歉,我无法将此查询转换为SQL Server的视图。这是因为该查询涉及到了一些PostgreSQL特定的函数和表达式,这些在SQL Server中并不存在。你可能需要手动重新编写查询以适应SQL Server的语法和函数。以下是一个可能的SQL Server等效查询的示例,供参考:
```sql
SELECT
t.name AS table_name,
c.name AS column_name,
CAST(ep.value AS NVARCHAR(MAX)) AS column_comment,
CASE
WHEN c.is_nullable = 0 AND pk.constraint_column_id IS NULL THEN 1
ELSE 0
END AS is_required,
CASE
WHEN pk.constraint_column_id IS NOT NULL THEN 1
ELSE 0
END AS is_pk,
c.column_id AS sort,
CASE
WHEN COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') = 1 THEN 1
ELSE 0
END AS is_increment,
CASE
WHEN t.name = 'varchar' AND c.max_length <> -1 THEN t.name + '(' + CAST(c.max_length AS NVARCHAR(MAX)) + ')'
WHEN t.name = 'nvarchar' AND c.max_length <> -1 THEN t.name + '(' + CAST(c.max_length / 2 AS NVARCHAR(MAX)) + ')'
ELSE t.name
END AS column_type
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1
LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND c.column_id = ic.column_id
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND ic.index_column_id = pk.unique_index_id
WHERE t.type = 'U'
AND SCHEMA_NAME(t.schema_id) = 'dbo'
ORDER BY t.name, c.column_id
```
请注意,这只是一个示例查询,具体取决于你的数据库结构和需求,你可能需要进行一些调整和修改。建议在转换之前备份你的数据库,以防出现意外情况。
insert table cdata_safe_income_new_day_analysis_item_status with t1 as ( select if(a.app_id is null,b.app_id,a.app_id) as app_id, if(a.product is null,b.product,a.product) as product, if(a.item_code_name is null,b.item_code_name,a.item_code_name) as item_code_name, case when a.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响' when b.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响' when a.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响' when b.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响' when a.app_id is null and c.voucher_begin > 0 then '优惠券影响' --- 原:续费使用了代金券 when a.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响' --- 原:续费使用了代金券 when b.app_id is null and c.voucher_begin > 0 then '优惠券影响' --- 原:续费由代金券转为正常 when b.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响' --- 原:续费由代金券转为正常 else 'item维度正常' end flag_status from (select app_id, product,item_code_name,sum(if(paymode_type = '调账',1,0)) as tiaozhang_cnt, sum(if(instr(paymode,'退费')>0,1,0)) as tuifei_cnt from cdata_safe_income_new_day where ftime = '${YYYYMMDD}' and paymode != '0元订单' and prod_class1_name = '基础安全' and prod_class2_name = '云原生安全' and product is not null and pay_type = '公有云' group by app_id,product,item_code_name ) a full join
cdata_safe_income_new_day_analysis_item_status 表中插入数据的SQL语句是:
```sql
insert table cdata_safe_income_new_day_analysis_item_status
with t1 as (
select if(a.app_id is null,b.app_id,a.app_id) as app_id,
if(a.product is null,b.product,a.product) as product,
if(a.item_code_name is null,b.item_code_name,a.item_code_name) as item_code_name,
case
when a.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响'
when b.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响'
when a.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响'
when b.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响'
when a.app_id is null and c.voucher_begin > 0 then '优惠券影响'
when a.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响'
when b.app_id is null and c.voucher_begin > 0 then '优惠券影响'
when b.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响'
else 'item维度正常'
end flag_status
from (
select app_id,
product,
item_code_name,
sum(if(paymode_type = '调账',1,0)) as tiaozhang_cnt,
sum(if(instr(paymode,'退费')>0,1,0)) as tuifei_cnt
from cdata_safe_income_new_day
where ftime = '${YYYYMMDD}'
and paymode != '0元订单'
and prod_class1_name = '基础安全'
and prod_class2_name = '云原生安全'
and product is not null
and pay_type = '公有云'
group by app_id,product,item_code_name
) a
full join <表名> b on <条件>
)
select * from t1;
```
其中 `<表名>` 和 `<条件>` 表示需要进行全连接的另外一个表的表名和连接条件。请在实际使用时将其替换为正确的表名和条件。