sql - 修改 T-SQL 查询以根据其他列值包含/排除列
问题描述
我有以下查询,我需要一些帮助来修改。在下面的查询中,我得到不为空的列数和百分比:
SELECT COUNT(v.col) as num_not_null, COUNT(v.col) * 1.0 / COUNT(*) * 100 as percent_not_null, COUNT(*) as toltalColsNeedsFilled
FROM EFP_EmploymentUser t
CROSS APPLY (VALUES (t.ITAdvicedFirst),
(t.ITAdvicedSecond),
(t.ITDepartmentDone),
(t.CFOAdvicedFirst),
(t.CFOInfoProvided),
(t.CFOAdvicedSecond),
(t.CFODone),
(t.EconomyAdviced),
(t.EconomyDone),
(t.AcademyAdviced),
(t.AcademyDone),
(t.PublicatorAdviced),
(t.PublicatorDone),
(t.PortraitAdviced),
(t.PortraitDone),
(t.WhoIsWhoAdviced),
(t.WhoIsWhoDone),
(t.BogportalAdviced),
(t.BogportalDone),
(t.KeyCardAdviced),
(t.KeyCardDone) ) v(col)
WHERE ID = '19';
在 ID 19 的情况下返回:
num_not_null percent_not_null toltalColsNeedsFilled
5 23.809523809500 21
但是我需要检查同一个表(Publicator、Bogportal、Academy)中的以下列是否填充了值“yes”,并且根据我需要从上述查询中包含或排除某些列:
i.e.: IF Academy = YES then include t.AcademyAdviced & t.AcademyDone
IF Publicator= YES then include t.PublicatorDone & t.PortraitAdviced
IF Bogportal = YES then include t.BogportalAdviced & t.BogportalDone
谁能帮助我如何修改查询以实现这一目标?:-)
最好的问候斯蒂格
解决方案
您可以使用UNION ALL
和WHERE
谓词来决定将哪些列添加到 unpivot:
SELECT COUNT(v.col) as num_not_null, COUNT(v.col) * 1.0 / COUNT(*) * 100 as percent_not_null, COUNT(*) as toltalColsNeedsFilled
FROM EFP_EmploymentUser t
CROSS APPLY (
SELECT * FROM
( VALUES (t.ITAdvicedFirst),
(t.ITAdvicedSecond),
(t.ITDepartmentDone),
(t.CFOAdvicedFirst),
(t.CFOInfoProvided),
(t.CFOAdvicedSecond),
(t.CFODone),
(t.EconomyAdviced),
(t.EconomyDone),
(t.PortraitAdviced),
(t.PortraitDone),
(t.WhoIsWhoAdviced),
(t.WhoIsWhoDone),
(t.KeyCardAdviced),
(t.KeyCardDone) ) v(col)
UNION ALL
SELECT *
FROM (VALUES (t.AcademyAdviced), (t.AcademyDone) ) v(col)
WHERE t.Academy = 'YES'
UNION ALL
SELECT *
FROM (VALUES (t.PublicatorDone), (t.PortraitAdviced) ) v(col)
WHERE t.Publicator = 'YES'
UNION ALL
SELECT *
FROM (VALUES (t.BogportalAdviced), (t.BogportalDone ) ) v(col)
WHERE t.Bogportal = 'YES'
) v
WHERE t.ID = '19';
推荐阅读
- react-native - react-native 在图像周围创建动画圆形进程栏
- dynamics-business-central - 业务中心页面上的选定/标记记录
- vue.js - vue lodash debounce方法可以工作,但是这个打错了,怎么解决?
- asynchronous - async 和 mapAsync AKKA 流的输出之间的区别
- python - 创建子列表 Python3
- google-cloud-platform - 如何在 GCP 中获取出口目标位置
- arrays - c中的常量数组-检查是否在编译时定义了所有元素
- c++ - 如何制作一个永远运行的线程?
- sql - 使用 ROWID 运行 SQL Oracle 命令时出现问题
- python - 当我切换随机变量 Z(X, Y) 的 X 和 Y 时,为什么线性回归是错误的?