sql - 使用多个 where 条件运行特定条件的问题
问题描述
我正在尝试创建一个查询,该查询将允许我通过一系列条件运行多个信息表,但是在使用多个相同类型的 WHERE 子句时,我似乎无法让它工作。
use leads;
select s.knum as Knum
, s.customer_id as Cust_#
, s.[full down] as FD_Date
, s.season, s.inv as Room_Type
, s.Owner_type
, s.status
, s.trw1
, s.trw2
, s.trw21
, s.state
, m.Knum as MagnaK
, c.[pbs id] as MF_ID
, c.[pbs disp] as MF_disp
, c.[pbs freq] as Freq
, c.[PBS DAYS DELQ] as MF_del
, c.[PBS TOTAL DUE] as MF_Due
, c.[LN ID]
, c.[LN DISP]
, c.[LN CURR BAL]
, c.[LN NPDD]
, st.customer_id as ST_custID
, p.[LOAN #] as P_Knum
, p.pmts_made as Paymnts_made
, p.int
, p.d30
, p.d60
, p.d90
, d.knum as DVN_Knum
, (
select max(HighTrw)
from (values (trw1), (trw2), (trw21)) as Value(HighTrw)
) as [high_trw]
from dbo.spi s
LEFT JOIN dbo.magna m on s.knum = m.knum
LEFT JOIN dbo.book7 C on s.knum = C.K
LEFT JOIN dbo.spi_tours ST on s.customer_id = st.customer_id
LEFT JOIN dbo.payments P on s.knum = P.[LOAN #]
LEFT JOIN dbo.dvn D on s.knum = d.knum
Where m.knum = null
and d.knum = null
and st.customer_id = null
or
(
select max(HighTrw)
from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
) between 600 and 625
and p.current_bal >= '15000'
and p.CURRENT_Bal >= '7500'
or
(
select max(HighTrw)
from (values (trw1), (trw2), (trw21)) As updatedate (HighTrw)
) between 575 and 599
and p.current_bal >= '7499'
and p.CURRENT_Bal >= '5000';
我希望能够使用代码中链接的 6 个表以及特定标准来根据该标准得出最终列表。
解决方案
您的查询中有几个拼写错误。我试图格式化和简化它以便能够更正它。
SELECT s.knum as Knum,
s.customer_id as Cust_#,
s.[full down] as FD_Date,
s.season,
s.inv as Room_Type,
s.Owner_type,
s.status,
s.trw1,
s.trw2,
s.trw21,
s.state,
m.Knum as MagnaK,
c.[pbs id] as MF_ID,
c.[pbs disp] as MF_disp,
c.[pbs freq] as Freq,
c.[PBS DAYS DELQ] as MF_del,
c.[PBS TOTAL DUE] as MF_Due,
c.[LN ID],
c.[LN DISP],
c.[LN CURR BAL],
c.[LN NPDD],
st.customer_id as ST_custID,
p.[LOAN #] as P_Knum,
p.pmts_made as Paymnts_made,
p.int,
p.d30,
p.d60,
p.d90,
d.knum as DVN_Knum,
v.HighTrw as [high_trw]
FROM dbo.spi s
LEFT JOIN dbo.magna m on s.knum = m.knum
LEFT JOIN dbo.book7 C on s.knum = C.K
LEFT JOIN dbo.spi_tours ST on s.customer_id = st.customer_id
LEFT JOIN dbo.payments P on s.knum = P.[LOAN #]
LEFT JOIN dbo.dvn D on s.knum = d.knum
CROSS APPLY (select max(HighTrw)
from (values (s.trw1), (s.trw2), (s.trw21))val(trw)) as v(HighTrw)
Where m.knum IS NULL
and d.knum IS NULL
and st.customer_id IS NULL
or (v.HighTrw between 600 and 625 and p.current_bal BETWEEN '7500' AND '15000')
or (v.HighTrw between 575 and 599 and p.current_bal BETWEEN '5000' AND '7499');
推荐阅读
- excel - 输入不同的值后更改单元格的颜色
- typescript - Vue.prototype.$property 不适用于 Typescript
- python - 数据透视表中的条形图,包含总计和每组聚合的百分比
- c - 使用#define 给结构成员起别名
- c# - 如何更新\刷新多个视图上使用的相同用户控件
- c# - 将项目添加到事件中的组合框时出现 InvalidOperationException
- swift - 在 if 和 else 语句 Swift 中保存整数
- java - 如何在 Android 应用中集成搜索功能
- c# - 无法访问后面代码中的 WPF 故事板名称
- angular - 使用 RXJS 将项目添加到可观察数组后如何触发可观察值发射?