mysql - 将查询结果添加为sql中的新列

我想将查询结果显示为新列，这是我的代码，但显示在2个表中。

SELECT JobNo
      ,Prepared1
      ,Prepared2
  FROM [test]
SELECT
  COUNT(CASE WHEN Prepared1 >1  THEN 1 END) +
  COUNT(CASE WHEN Prepared2 >1 THEN 1 END) as 'Done'
  FROM [test]
GROUP BY JobNo

像这样在1个表中包含它的代码应该是什么

最佳答案

我认为JobNo是UNIQUE。如果是这样，您可以简单地使用：

SELECT JobNo
      ,Prepared1
      ,Prepared2
      ,CASE WHEN Prepared1 > 1  THEN 1 ELSE 0 END
       + CASE WHEN Prepared2 >1 THEN 1 ELSE 0 END
  FROM [test] t1

如果不：

select JobNo,
    Prepared1,
    Prepared2,
    t2.done
from [test] t1
join (
    select JobNo,
        COUNT(case
                when Prepared1 > 1
                    then 1
                end) + COUNT(case
                when Prepared2 > 1
                    then 1
                end) as done
    from [test] t2
    group by JobNo
    ) t2 on t1.JobNo = t2.JobNo;