SQL将Count列临时添加到返回的结果集中

Question

我有以下代码，它将把两个不同的表连接在一起，并提供我需要的离散数据列。问题是我还需要一个列来计算另一个表中的另一个实例。请参阅select语句附近的指针引用。

  SELECT 
  //count(of records returned from below query and duplicates of those found in outc table),
  outage_duration.dgroup,
  outage_duration.cause,
  outage_duration.cdts,
  outage_duration.cust_minutes,
  outage_duration.evntnum,
  outage_duration.num_cust,
  outage_duration.outage_type,
  outage_duration.substation,
  outage_duration.feeder,
  outage_duration.out_minutes
  FROM outage_duration,
  aeven
  WHERE outage_duration.evntnum    = aeven.num_1
  AND aeven.outage_type            = 'T'
  AND aeven.cdts                  >= '20110101060000UT'
  AND aeven.curent                 = 'T'
  AND aeven.open_and_curent        = 'F'
  AND aeven.ag_id NOT             IN    ('MEMT','MTRRDR','MTRTECH','GRDLT','FSRSR','ELECOPS','AMS','REVSEC')
  AND outage_duration.out_minutes >= '240'
  AND outage_duration.curent = 'T' 
  GROUP BY outage_duration.dgroup, outage_duration.cause, outage_duration.cdts, outage_duration.cust_minutes, outage_duration.evntnum, outage_duration.num_cust, outage_duration.outage_type, outage_duration.substation, outage_duration.feeder, outage_duration.out_minutes
  ORDER BY outage_duration.evntnum

事件样例，Outage_Duration表

num_1
T344490
T344410
T344480

outage_duration
T344490
T344410

这将返回T344490和T44410，这就是上面的搜索所做的。现在，有了这些结果，我需要从另一个表中提取一个计数，并在该表中有多少个事件编号的旁边将计数显示为自己的列。

outc (第1列=唯一id，第2列= evntnum)

 1. 1, T344490
 2. 2, T344490
 3. 3, T344410
 4. 5, T344410
 5. 6, T344410
 6. 7, T344410
 7. 8, T344410

期望的结果如下：

 1. T344410, 5, "other columns that were specified"
 2. T344490, 2, "other columns that were specified"

我该如何做到这一点呢？

Answer 1

SELECT 
  COUNT(*), --this
  outage_duration.dgroup,
  outage_duration.cause,
  outage_duration.cdts,
  outage_duration.cust_minutes,
  outage_duration.evntnum,
  outage_duration.num_cust,
  outage_duration.outage_type,
  outage_duration.substation,
  outage_duration.feeder,
  outage_duration.out_minutes
  FROM
      outage_duration
      JOIN
      aeven ON outage_duration.evntnum    = aeven.num_1
      JOIN
      outc ON outage_duration.evntnum = outc. --insert column here
  WHERE 
  AND aeven.outage_type            = 'T'
  AND aeven.cdts                  >= '20110101060000UT'
  AND aeven.curent                 = 'T'
  AND aeven.open_and_curent        = 'F'
  AND aeven.ag_id NOT             IN           ('MEMT','MTRRDR','MTRTECH','GRDLT','FSRSR','ELECOPS','AMS','REVSEC')
  AND outage_duration.out_minutes >= '240'
  AND outage_duration.curent = 'T' 
  GROUP BY outage_duration.dgroup, outage_duration.cause, outage_duration.cdts, outage_duration.cust_minutes, outage_duration.evntnum, outage_duration.num_cust, outage_duration.outage_type, outage_duration.substation, outage_duration.feeder, outage_duration.out_minutes
  ORDER BY outage_duration.evntnum

只需将计数(*)添加到SELECT位，并将表outc添加到FROM

您已经有一个GROUP BY，所以只需插入itn即可