向group和having子句添加多个条件
提问于 2015-03-10 08:09:06
3I有以下查询
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM table
WHERE run_date >= Dateadd(month, -4, Getdate())
and test_alias='BOND_P'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 1.79 AND 4.5 和
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM ems.cdotests
WHERE run_date >= Dateadd(month, -3, Getdate())
and test_alias='CAA/CCC'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 2.79 AND 2我需要将这个查询组合起来，这样我就可以将输出作为
1: result set of query 1 intersected with result set of query 2
2: result set of query 1 unioned with result set of query 2“‘Intersect”或“Union”将取决于传递给存储过程的参数
Ps注意:两个查询之间的运行日期条件可能是不同的，任何帮助都将不胜感激。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-03-10 08:56:02
IF @myBitParameter = 0
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM [table]
WHERE run_date >= Dateadd(month, -4, Getdate())
and test_alias='BOND_P'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 1.79 AND 4.5
UNION ALL --or UNION, if you don't want duplicates
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM ems.cdotests
WHERE run_date >= Dateadd(month, -3, Getdate())
and test_alias='CAA/CCC'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 2.79 AND 2
ELSE
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM [table]
WHERE run_date >= Dateadd(month, -4, Getdate())
and test_alias='BOND_P'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 1.79 AND 4.5
INTERSECT
SELECT deal_id, max(test_value) - min(test_value) AS deviation
FROM ems.cdotests
WHERE run_date >= Dateadd(month, -3, Getdate())
and test_alias='CAA/CCC'
group by deal_id
having (max(test_value) - min(test_value)) BETWEEN 2.79 AND 2我觉得应该有一个方法来做这个清洁剂与CTE的，但没有时间来愚弄它了。有人可以把这件事清理干净，或者找出一个更好的答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28968610
复制相关文章
![group by和order by having where 执行顺序[order by升序]](https://ask.qcloudimg.com/http-save/yehe-1011815/990163e9c47d21600b2be3b76d8ce5c5.jpeg)
腾讯云开发者
