SQL列添加另一列

debugcn 发表于 Dev

用户名

SELECT * from
  (
    SELECT intUserID as [KidId], intAssessmentID as [assid] ,vchTopic as [Topics] from ManageAssessment WHERE intUserID=174 and IsRorW='yes'

   )AS S
  pivot
  (
  COUNT(KidId)

    FOR assid IN ([22],[19],[16],[17],[18])
   )AS s

嘿，我的代码创建了一个表，我想添加其他列，但是它来错了..！我要在第4列之后添加一列，即2值的差。任何人都可以帮助我解决..谢谢...在此处输入图像描述

马哈茂德·加马尔（Mahmoud Gamal）

你可以这样做：

SELECT 
  Topics,
  [22],
  [19],
  [16],
  [17],
  [18],
  [22] - [19] AS NewColumn      
from
(
   SELECT 
     intUserID as [KidId], 
     intAssessmentID as [assid] ,
     vchTopic as [Topics] 
   from ManageAssessment 
   WHERE intUserID=174 and IsRorW='yes'
)AS S
pivot
(
  COUNT(KidId)
  FOR assid IN ([22], [19], [16], [17], [18])
)AS s;

更新：

如果要为所有新列添加总计的新行，则可以执行以下操作：

WITH CTE AS
(
    SELECT 
      Topics,
      [22],
      [19],
      [16],
      [17],
      [18],
      [22] - [19] AS NewColumn      
    from
    (
       SELECT 
         intUserID as [KidId], 
         intAssessmentID as [assid] ,
         vchTopic as [Topics] 
       from ManageAssessment 
       WHERE intUserID=174 and IsRorW='yes'
    )AS S
    pivot
    (
      COUNT(KidId)
      FOR assid IN ([22], [19], [16], [17], [18])
    )AS s
)
SELECT
  Topics,
  [22],
  [19],
  [16],
  [17],
  [18],
  NewColumn    
FROM CTE
UNION ALL
SELECT
  'Total',
  SUM([22]),
  SUM([19]),
  SUM([16]),
  SUM([17]),
  SUM([18]),
  SUM(NewColumn)
FROM CTE;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。