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; ``` 其中 `<表名>` 和 `<条件>` 表示需要进行全连接的另外一个表的表名和连接条件。请在实际使用时将其替换为正确的表名和条件。

相关推荐

mismatched input 'count' expecting <EOF>(line 1, pos 379) == SQL == insert overwrite table spark_odc_data.D_RE_BA_RAD_MAP_H partition(p_hour = '2023060600') select '202306060000' TIME_ID, a.IMSI, a.GJ, a.YYS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXQQCS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MRWZGXCGCS, case when count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null)/count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXCGL, sum(c.MRZJCS) as MRZJCS, sum(c.MRZJHJCGCS) as MRZJHJCGCS, sum(d.MRBJCS) as MRBJCS, sum(d.MRBJHJCGCS) as MRBJHJCGCS, case when sum(c.MRZJCS)=0 then 0 else sum(c.MRZJHJCGCS)/sum(c.MRZJCS) end as MRZJWLJTL, case when sum(c.MRZJCS)=0 then 0 else sum(d.MRBJHJCGCS)/sum(d.MRBJCS) end as MRBJWLJTL, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXQQCS, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MCWZGXCGCS, case when count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end)/ count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXCGL, now() LOAD_TIME

最新推荐

recommend-type

MATLAB实验一二 数值计算

MATLAB实验一二 数值计算
recommend-type

Java毕业设计-ssm基于SSM的英语学习网站的设计与实现演示录像(高分期末大作业).rar

Java毕业设计-ssm基于SSM的英语学习网站的设计与实现演示录像(高分期末大作业)
recommend-type

zigbee-cluster-library-specification

最新的zigbee-cluster-library-specification说明文档。
recommend-type

管理建模和仿真的文件

管理Boualem Benatallah引用此版本:布阿利姆·贝纳塔拉。管理建模和仿真。约瑟夫-傅立叶大学-格勒诺布尔第一大学,1996年。法语。NNT:电话:00345357HAL ID:电话:00345357https://theses.hal.science/tel-003453572008年12月9日提交HAL是一个多学科的开放存取档案馆,用于存放和传播科学研究论文,无论它们是否被公开。论文可以来自法国或国外的教学和研究机构,也可以来自公共或私人研究中心。L’archive ouverte pluridisciplinaire
recommend-type

确保MATLAB回归分析模型的可靠性:诊断与评估的全面指南

![确保MATLAB回归分析模型的可靠性:诊断与评估的全面指南](https://img-blog.csdnimg.cn/img_convert/4b823f2c5b14c1129df0b0031a02ba9b.png) # 1. 回归分析模型的基础** **1.1 回归分析的基本原理** 回归分析是一种统计建模技术,用于确定一个或多个自变量与一个因变量之间的关系。其基本原理是拟合一条曲线或超平面,以最小化因变量与自变量之间的误差平方和。 **1.2 线性回归和非线性回归** 线性回归是一种回归分析模型,其中因变量与自变量之间的关系是线性的。非线性回归模型则用于拟合因变量与自变量之间非
recommend-type

引发C++软件异常的常见原因

1. 内存错误:内存溢出、野指针、内存泄漏等; 2. 数组越界:程序访问了超出数组边界的元素; 3. 逻辑错误:程序设计错误或算法错误; 4. 文件读写错误:文件不存在或无法打开、读写权限不足等; 5. 系统调用错误:系统调用返回异常或调用参数错误; 6. 硬件故障:例如硬盘损坏、内存损坏等; 7. 网络异常:网络连接中断、网络传输中断、网络超时等; 8. 程序异常终止:例如由于未知原因导致程序崩溃等。
recommend-type

JSBSim Reference Manual

JSBSim参考手册,其中包含JSBSim简介,JSBSim配置文件xml的编写语法,编程手册以及一些应用实例等。其中有部分内容还没有写完,估计有生之年很难看到完整版了,但是内容还是很有参考价值的。
recommend-type

"互动学习:行动中的多样性与论文攻读经历"

多样性她- 事实上SCI NCES你的时间表ECOLEDO C Tora SC和NCESPOUR l’Ingén学习互动,互动学习以行动为中心的强化学习学会互动,互动学习,以行动为中心的强化学习计算机科学博士论文于2021年9月28日在Villeneuve d'Asq公开支持马修·瑟林评审团主席法布里斯·勒菲弗尔阿维尼翁大学教授论文指导奥利维尔·皮耶昆谷歌研究教授:智囊团论文联合主任菲利普·普雷教授,大学。里尔/CRISTAL/因里亚报告员奥利维耶·西格德索邦大学报告员卢多维奇·德诺耶教授,Facebook /索邦大学审查员越南圣迈IMT Atlantic高级讲师邀请弗洛里安·斯特鲁布博士,Deepmind对于那些及时看到自己错误的人...3谢谢你首先,我要感谢我的两位博士生导师Olivier和Philippe。奥利维尔,"站在巨人的肩膀上"这句话对你来说完全有意义了。从科学上讲,你知道在这篇论文的(许多)错误中,你是我可以依
recommend-type

打造高性能MATLAB回归分析模型:特征工程的艺术

![matlab回归分析](http://blog.fens.me/wp-content/uploads/2016/07/m01.png) # 1. 回归分析模型概述 回归分析是一种预测建模技术,用于建立自变量和因变量之间的关系。在回归模型中,因变量是我们要预测的目标变量,而自变量是影响因变量的输入变量。 回归分析模型的目的是找到一个数学方程,该方程可以准确地预测因变量的值,给定自变量的值。该方程通常表示为: ``` y = b0 + b1x1 + b2x2 + ... + bnxn ``` 其中: * y 是因变量 * x1、x2、...、xn 是自变量 * b0、b1、...、b
recommend-type

爬取虎牙直播的英雄联盟板块热度并可视化显示

抱歉,作为AI语言模型,我无法执行网络爬虫操作。但我可以提供一个大体的思路: 1. 使用Python的requests库向虎牙直播英雄联盟板块的页面发送请求,获取页面内容。 2. 使用BeautifulSoup库解析页面内容,提取出热度相关的信息。 3. 使用matplotlib等可视化库将热度数据进行可视化展示。 需要注意的是,网站的爬取需要遵守相关法律法规和网站的使用协议,否则可能会引起法律问题。