代码之家 › 专栏 › 技术社区 › masfenix

需要访问SQL查询

ms-access

1

masfenix · 技术社区 · 16 年前

好吧,这个让我很难堪。。主要是因为我一整天都在解决这个问题,我(和老板)决定走另一条路。然而我的大脑是油炸的。

我给你呈现以下图片:

_{(来源:

filmot.com}

现在 左侧桌子

我要最近一个月加上前11个月的数据。(在某些情况下,如示例所示,只有一个月的数据。因此,对于前11个月,我**确实**希望12个月显示为0作为计数条目。有可能吗?请看最后我的手绘照片)

注意:请注意左边的桌子不是实体桌子。它是SQL SELECT语句的结果

当前SQL语句如下所示。它抓住一切(不是12个月),它做了一个奇怪的连接,在我当前的例子中失败了:

SELECT A.month, A.count, B.count, C.count, D.count, E.count
FROM [table name] AS A, [table name]  AS B, [table name]  AS C, [table name]  AS D, [table name] AS E
WHERE B.month=A.month and C.month=A.month and D.month=A.month and E.month=A.month
ORDER BY A.month DESC;

我要画一幅我想要决赛的样子

+=============================================================+
|  month    | A.count | B.count | C.count | D.count | E.count |
+-------------------------------------------------------------+
|2010-08-01      3           2       0          5        18   |
|2010-07-01      0           0       0          0         0   |
|2010-06-01      0           0       0          0         0   |
|2010-05-01      0           0       0          0         0   |
|2010-04-01      0           0       0          0         0   |
|2010-03-01      0           0       0          0         0   |
|2010-02-01      0           0       0          0         0   |
|2010-01-01      0           0       0          0         0   |
|2010-12-01      0           0       0          0         0   |
|2010-11-01      0           0       0          0         0   |
|2010-10-01      0           0       0          0         0   |

etc

2 回复 | 直到 7 年前

1

Fionnuala 16 年前

SELECT x.Mnth, Nz([a].[Count],0) AS a, 
       Nz([b].[Count],0) AS b, Nz([c].[Count],0) AS c, 
       Nz([d].[Count],0) AS d, Nz([e].[Count],0) AS e
FROM (((((SELECT DateAdd("m",-[Num],#2010/7/1#) AS Mnth 
          FROM [Counter] WHERE Num<=12)  AS x 
LEFT JOIN a ON x.Mnth = a.month) 
LEFT JOIN b ON x.Mnth = b.month) 
LEFT JOIN c ON x.Mnth = c.month) 
LEFT JOIN e ON x.Mnth = e.month) 
LEFT JOIN d ON x.Mnth = d.month
ORDER BY a.Count DESC;

2

0

Leon 16 年前

您可以通过在SELECT中包含子查询来实现这一点。

    SELECT MonthDate,
    (
      SELECT COUNT(*) FROM A WHERE A.Month = M.MonthDate
    ) [A.Count],
    (
      SELECT COUNT(*) FROM B WHERE B.Month = M.MonthDate
    ) [B.Count],
    (
      SELECT COUNT(*) FROM C WHERE C.Month = M.MonthDate
    ) [C.Count],
...
    FROM Months M

http://allenbrowne.com/subquery-01.html