sql - Select, Left Join 和 Group By 中的情况
问题描述
我如何归档以下内容?
如果@chk = 0,则在结果中包含第 4 列,否则仅保留 3 列,如果选择了第 4 列,则第 4 列需要左连接,并将第 4 列包含在 group by
DECLARE @chk AS INT= 0;
SELECT a.Ledgerid,
b.LedgerCity,
CASE WHEN @chk = 0 THEN SUM(a.TotalAmount) ELSE SUM(a.NetAmount) END,
CASE WHEN @chk = 0 THEN c.ledgername END (is it possilbe to completly do not have this column if @chk <> 0)
FROM ExpenseMaster A
LEFT JOIN LedgerAddress AS B ON A.LedgerID = B.LedgerID
LEFT JOIN LedgerMaster AS C ON A.LedgerID = C.LedgerID --This left join is required only if @chk=0, same logic is there in above select statement
GROUP BY A.LedgerID,
B.LedgerCity;
C.LedgerName; -- this 3rd group required only if @chk=0
一些帖子建议在 Group by 中如何使用案例。我尝试了“当@chk=0 然后a.Ledgerid,b.Ledgercity,c.LedgerName else a.Ledgerid,b.Ledgercity END; 时分组;但这没有用
解决方案
如果您确定,数据库外部的任何人都无法运行您的查询并执行 sql-injection,只需使用简单的 dynamic-sql 语句
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @chk AS INT= 0;
set @sql = N'SELECT a.Ledgerid,
b.LedgerCity, ' +
CASE WHEN @chk = 0 THEN 'SUM(a.TotalAmount) ' ELSE 'SUM(a.NetAmount) ' END +
CASE WHEN @chk = 0 THEN ', c.ledgername ' ELSE ' ' END +
'FROM ExpenseMaster A
LEFT JOIN LedgerAddress AS B ON A.LedgerID = B.LedgerID ' +
CASE WHEN @chk=0 THEN 'LEFT JOIN LedgerMaster AS C ON A.LedgerID = C.LedgerID ' ELSE '' END +
'GROUP BY A.LedgerID,
B.LedgerCity' +
CASE WHEN @chk = 0 THEN ', C.LedgerName;' ELSE ';' END;
exec sp_executesql @sql
推荐阅读
- jquery - 按下提交按钮后,有没有办法延迟页面刷新?
- python - 在单个 Django 模板上显示来自 2 个模型的内容
- javascript - 将所有内容移至 1 个文件时无法启动我的服务器
- javascript - 获取 HTML 简单列表的项目
- python - 使用 Regex python 在代码中查找函数
- python - 与 Appium Java 相比,Appium python 客户端是否有足够的支持?
- c++ - ZeroMQ 不同速度的订阅者看到相同的消息
- c# - Roslyn 分析仪缺少装配警告
- lua - nodemcu string.format 奇怪的结果
- python - python sql TypeError:'str'对象不可调用