sql - 从多个子查询中选择结果

Question

我试图从 9 个不同子查询的结果中创建一个表。这是我的代码:

SELECT

APR16
FROM(
SELECT Sum(APR.[kwh]) AS APR16
FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL)
,

MAY16
FROM(
SELECT Sum(MAY.[kwh]) AS MAY16
FROM Peak_Times, Meter_Buckets INNER JOIN MAY ON Meter_Buckets.METER = MAY.meter
WHERE (((MAY.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND MAY.kwh IS NOT NULL)
,

JUN16
FROM(
SELECT Sum(JUN.[kwh]) AS JUN16
FROM Peak_Times, Meter_Buckets INNER JOIN JUN ON Meter_Buckets.METER = JUN.meter
WHERE (((JUN.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUN.kwh IS NOT NULL)
,

JUL16
FROM(
SELECT Sum(JUL.[kwh]) AS JUL16
FROM Peak_Times, Meter_Buckets INNER JOIN JUL ON Meter_Buckets.METER = JUL.meter
WHERE (((JUL.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUL.kwh IS NOT NULL)
,

AUG16
FROM(
SELECT Sum(AUG.[kwh]) AS AUG16
FROM Peak_Times, Meter_Buckets INNER JOIN AUG ON Meter_Buckets.METER = AUG.meter
WHERE (((AUG.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND AUG.kwh IS NOT NULL)
,

SEP16
FROM(
SELECT Sum(SEP.[kwh]) AS SEP16
FROM Peak_Times, Meter_Buckets INNER JOIN SEP ON Meter_Buckets.METER = SEP.meter
WHERE (((SEP.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND SEP.kwh IS NOT NULL)
,

OCT16
FROM(
SELECT Sum(OCT.[kwh]) AS OCT16
FROM Peak_Times, Meter_Buckets INNER JOIN OCT ON Meter_Buckets.METER = OCT.meter
WHERE (((OCT.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND OCT.kwh IS NOT NULL)
,

NOV16
FROM(
SELECT Sum(NOV.[kwh]) AS NOV16
FROM Peak_Times, Meter_Buckets INNER JOIN NOV ON Meter_Buckets.METER = NOV.meter
WHERE (((NOV.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND NOV.kwh IS NOT NULL)
,

DEC16
FROM(
SELECT Sum(DEC.[kwh]) AS DEC16
FROM Peak_Times, Meter_Buckets INNER JOIN DEC ON Meter_Buckets.METER = DEC.meter
WHERE (((DEC.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND DEC.kwh IS NOT NULL)

如果我只做第一个子查询，

SELECT

APR16
FROM(
SELECT Sum(APR.[kwh]) AS APR16
FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL);

它返回一个没有错误的结果。但是，当我尝试像上面一样将它们链接在一起时，它在第二个 FROM 中给了我一个语法错误。我想要一个与此类似的结果:

APR16|MAY16|JUN16
57212|45681|721

谁能告诉我正确的语法来做到这一点？这可能是我忽略的一些简单的事情，也可能是我做不到的事情。预先感谢您的帮助。

Answer 1

您可以按照您正在寻找的格式获取多个子查询的结果，如下所示:

select
    (select count(*) from table1) as result1,
    (select count(*) from table2) as result2,
    ..

应用于您的查询，它看起来像这样:

SELECT
    (SELECT Sum(APR.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
    WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL)
     AS APR16
    ,
    (SELECT Sum(MAY.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN MAY ON Meter_Buckets.METER = MAY.meter
    WHERE (((MAY.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND MAY.kwh IS NOT NULL)
     AS MAY16
    ,
    (SELECT Sum(JUN.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN JUN ON Meter_Buckets.METER = JUN.meter
    WHERE (((JUN.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUN.kwh IS NOT NULL)
     AS JUN16
    ,
    (SELECT Sum(JUL.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN JUL ON Meter_Buckets.METER = JUL.meter
    WHERE (((JUL.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUL.kwh IS NOT NULL)
     AS JUL16
    ,
    (SELECT Sum(AUG.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN AUG ON Meter_Buckets.METER = AUG.meter
    WHERE (((AUG.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND AUG.kwh IS NOT NULL)
     AS AUG16
    ,
    (SELECT Sum(SEP.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN SEP ON Meter_Buckets.METER = SEP.meter
    WHERE (((SEP.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND SEP.kwh IS NOT NULL)
     AS SEP16
    ,
    (SELECT Sum(OCT.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN OCT ON Meter_Buckets.METER = OCT.meter
    WHERE (((OCT.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND OCT.kwh IS NOT NULL)
     AS OCT16
    ,
    (SELECT Sum(NOV.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN NOV ON Meter_Buckets.METER = NOV.meter
    WHERE (((NOV.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND NOV.kwh IS NOT NULL)
     AS NOV16
    ,
    (SELECT Sum(DEC.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN DEC ON Meter_Buckets.METER = DEC.meter
    WHERE (((DEC.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND DEC.kwh IS NOT NULL)
     AS DEC16