mysql - SQL查询过程: adding new column based on select query

Question

好吧，昨天我问如何根据某些条件在 sql 查询中创建额外的列，有人告诉我使用 CASE WHEN 语句(再次感谢!)，它确实有效，但它只产生了以下字段如果 true 并排除了其他所有内容，我需要显示所有字段，并且必须向查询中添加更多联接，现在我什至无法判断它是否有效，因为它在 2 小时后仍在执行。当我删除 case 语句时，它会在 6 秒内执行，因此我假设问题出在 case 语句上。是否有其他方法可以将名为“Eligibilty”的新列添加到选择查询，其值基于:

If the 'End Use' field is civil AND it exists in the p_SpecialPrograms table its value in the new column would be 'C'.

If it 'END USE' field does not equal to civil then the value in the new column would be just blank

Select distinct 
pn.PNumber, 
rp.PDescriptionText, 
ht.HNumber AS 'HTS', 
chs.CHistoryState AS 'HTS State', 
ct.CWhereUsedName AS 'End Use',
--CASE WHEN 'End Use' LIKE 'Civil%' AND pts.PSpecialTradeProgramAdded IS NOT NULL THEN ' ' ELSE 'C' END as 'Eligibility'
from t_COMPClients cc 
  join t_part pt on pt.coclientid=cc.coclientid AND cc.coclientid = 279
  join t_PARTNumbers pn on pn.PID=pt.pid
  join t_PARTDescriptions rp on rp.PID=pt.PID
  join t_PARTDescriptionTypes sp on   sp.PDescriptionTypeID=rp.PDescriptionTypeID AND sp.PDescriptionTypeID=1
  JOIN t_CLASS cs ON pt.PID = cs.PID
  JOIN t_CLASSHistory ch on ch.CID = cs.CID AND (ch.CHistoryStateID = 1 OR ch.CHistoryStateID = 2 OR ch.CHistoryStateID = 3)
  JOIN t_HTS ht on ht.HID = ch.HID AND ht.SYSubprocessID=8
  JOIN t_SYSSubProcesses sb on ht.SYSubprocessID = sb.SYSubProcessID
  JOIN t_CLASSHistoryStates chs ON chs.CHistoryStateID=ch.CHistoryStateID
  JOIN t_CLASSBufferCommodity cb on cb.SYSubProcessID=ht.SYSubprocessID 
  JOIN t_COMPClientSubProcessLink cw ON cw.SYSubProcessID=sb.SYSubProcessID
  JOIN t_CLASSWhereUsedTypes ct ON ct.COClientSubProcessLinkID=cw.COClientSubProcessLinkID
  left join t_PARTSources ps on ps.PID=pt.PID
  left join P_PARTSpecialTradePrograms pts on pts.PSourceID=ps.PSourceID 

Answer 1

问题是您尝试在同一查询级别内使用别名。

例如，这是无效的，因为第二个字段'三'在此级别不存在

 SELECT 1 + 2  as 'three', 'three' * 2 as 'six'

你需要做这样的事情

SELECT subQuery.'three' * 2 as 'six'
FROM 
    (SELECT 1 + 2  as 'three') as subQuery

你创造

ct.CWhereUsedName AS 'End Use',

并尝试在同一级别的 CASE 中使用“最终使用”

所以替换

  CASE WHEN 'End Use'
  --with
  CASE WHEN t.CWhereUsedName