关于我的 SQL 脚本的两个问题。如何添加小计/总计行以及排序问题

发布于 2024-09-28 02:18:20 字数 2010 浏览 5 评论 0原文

我有一些 T-SQL 可以生成一份很好的报告,按月总结一些内容。

我有 2 个问题,有没有办法让按日历顺序而不是按 alpha 对月份进行排序?而且,我想做的是添加每年的总行以及整个报告的总行?

SELECT
    CASE WHEN tmpActivity.Year IS NULL THEN 
        CASE WHEN tmpCreated.Year IS NULL THEN
            CASE WHEN tmpContactsCreated.Year IS NULL THEN
                null
            ELSE tmpContactsCreated.Year END
        ELSE tmpCreated.Year END
    ELSE tmpActivity.Year END As Year, 

    CASE WHEN tmpActivity.Month IS NULL THEN 
        CASE WHEN tmpCreated.Month IS NULL THEN 
            CASE WHEN tmpContactsCreated.Month IS NULL THEN 
                null
            ELSE DateName(month, DateAdd(month, tmpContactsCreated.Month - 1, '1900-01-01' )) END
        ELSE DateName(month, DateAdd(month, tmpCreated.Month - 1, '1900-01-01' )) END
    ELSE DateName(month, DateAdd(month, tmpActivity.Month - 1, '1900-01-01' )) END As Month, 

    CASE WHEN tmpActivity.ActiveAccounts IS NULL THEN 0 ELSE tmpActivity.ActiveAccounts END AS ActiveAccounts, 
    CASE WHEN tmpCreated.NewAccounts IS NULL THEN 0 ELSE tmpCreated.NewAccounts END AS NewAccounts, 
    CASE WHEN tmpContactsCreated.NewContacts IS NULL THEN 0 ELSE tmpContactsCreated.NewContacts END AS NewContacts
FROM
(
SELECT YEAR(LastLogon) As Year, MONTH(LastLogon) As Month, COUNT(*) As ActiveAccounts
FROM Users
WHERE LastLogon >= '1/1/1800'
GROUP BY YEAR(LastLogon), MONTH(LastLogon)
) as tmpActivity

FULL JOIN
(
SELECT YEAR(Created) As Year, MONTH(Created) As Month, COUNT(*) As NewAccounts
FROM Users
WHERE Created >= '1/1/1800'
GROUP BY YEAR(Created), MONTH(Created)
) as tmpCreated ON tmpCreated.Year = tmpActivity.Year AND tmpCreated.Month = tmpActivity.Month

FULL JOIN
(
SELECT YEAR(Created) As Year, MONTH(Created) As Month, COUNT(*) As NewContacts
FROM Contacts
WHERE Created >= '1/1/1800'
GROUP BY YEAR(Created), MONTH(Created)
) as tmpContactsCreated ON tmpContactsCreated.Year = tmpCreated.Year AND tmpContactsCreated.Month = tmpCreated.Month

Order By Year DESC, Month DESC

I have some T-SQL that generates a nice report giving a summary some stuff by month.

I have 2 questions, is there a way to get the to sort the months by calendar order, not by alpha? And, what i would like to do is add a total line for each year, and a total line for the whole report?

SELECT
    CASE WHEN tmpActivity.Year IS NULL THEN 
        CASE WHEN tmpCreated.Year IS NULL THEN
            CASE WHEN tmpContactsCreated.Year IS NULL THEN
                null
            ELSE tmpContactsCreated.Year END
        ELSE tmpCreated.Year END
    ELSE tmpActivity.Year END As Year, 

    CASE WHEN tmpActivity.Month IS NULL THEN 
        CASE WHEN tmpCreated.Month IS NULL THEN 
            CASE WHEN tmpContactsCreated.Month IS NULL THEN 
                null
            ELSE DateName(month, DateAdd(month, tmpContactsCreated.Month - 1, '1900-01-01' )) END
        ELSE DateName(month, DateAdd(month, tmpCreated.Month - 1, '1900-01-01' )) END
    ELSE DateName(month, DateAdd(month, tmpActivity.Month - 1, '1900-01-01' )) END As Month, 

    CASE WHEN tmpActivity.ActiveAccounts IS NULL THEN 0 ELSE tmpActivity.ActiveAccounts END AS ActiveAccounts, 
    CASE WHEN tmpCreated.NewAccounts IS NULL THEN 0 ELSE tmpCreated.NewAccounts END AS NewAccounts, 
    CASE WHEN tmpContactsCreated.NewContacts IS NULL THEN 0 ELSE tmpContactsCreated.NewContacts END AS NewContacts
FROM
(
SELECT YEAR(LastLogon) As Year, MONTH(LastLogon) As Month, COUNT(*) As ActiveAccounts
FROM Users
WHERE LastLogon >= '1/1/1800'
GROUP BY YEAR(LastLogon), MONTH(LastLogon)
) as tmpActivity

FULL JOIN
(
SELECT YEAR(Created) As Year, MONTH(Created) As Month, COUNT(*) As NewAccounts
FROM Users
WHERE Created >= '1/1/1800'
GROUP BY YEAR(Created), MONTH(Created)
) as tmpCreated ON tmpCreated.Year = tmpActivity.Year AND tmpCreated.Month = tmpActivity.Month

FULL JOIN
(
SELECT YEAR(Created) As Year, MONTH(Created) As Month, COUNT(*) As NewContacts
FROM Contacts
WHERE Created >= '1/1/1800'
GROUP BY YEAR(Created), MONTH(Created)
) as tmpContactsCreated ON tmpContactsCreated.Year = tmpCreated.Year AND tmpContactsCreated.Month = tmpCreated.Month

Order By Year DESC, Month DESC

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

走过海棠暮 2024-10-05 02:18:20

要按月排序,请使用以下命令:

  ORDER BY DATEPART(Month,Created) ASC

DatePart() 返回指定部分的整数,1 表示一月,2 表示二月等。


您的 SQL 可以使用 COALESCE() 和 ISNULL( ) 函数。这与您的第一个选择相同:

SELECT
    COALESCE(tmpActivity.Year,tmpCreated.Year,tmpContactsCreated.Year) as Year, 
    COALESCE(tmpActivity.Month,tmpCreated.Month,tmpContactsCreated.Month) as Month,
    ISNULL(tmpActivity.ActiveAccounts,0) AS ActiveAccounts, 
    ISNULL(tmpCreated.NewAccounts,0) AS NewAccounts, 
    ISNULL(tmpContactsCreated.NewContacts,0) AS NewContacts

我认为您的选择中有一个错误,我相信您的最后一行必须是这样的:

) as tmpContactsCreated ON (tmpContactsCreated.Year = tmpCreated.Year AND tmpContactsCreated.Month = tmpCreated.Month) OR
                           (tmpContactsCreated.Year = tmpActivity.Year AND tmpContactsCreated.Month = tmpActivity.Month)     

但我必须对此进行测试以确定。


添加汇总很难做到——通常这是在控件中的 SQL 或任何显示结果的外部完成的。您可以这样做(人为的示例):

SELECT 1 as reportOrder, date, amount, null as total
FROM invoices
UNION ALL
SELECT 2 , null, null, sum(amount)
FROM invoices
ORDER BY reportOrder, date

或者您不能拥有“额外”总计列并将其放入金额列中。

To order by the month use the following:

  ORDER BY DATEPART(Month,Created) ASC

DatePart() returns an integer for the part specified 1 for January, 2 for Febuary etc.


You're SQL could be helped with the COALESCE() and ISNULL() functions. This is the same as your first select:

SELECT
    COALESCE(tmpActivity.Year,tmpCreated.Year,tmpContactsCreated.Year) as Year, 
    COALESCE(tmpActivity.Month,tmpCreated.Month,tmpContactsCreated.Month) as Month,
    ISNULL(tmpActivity.ActiveAccounts,0) AS ActiveAccounts, 
    ISNULL(tmpCreated.NewAccounts,0) AS NewAccounts, 
    ISNULL(tmpContactsCreated.NewContacts,0) AS NewContacts

I think there is a bug in your select, I believe your last line has to be this:

) as tmpContactsCreated ON (tmpContactsCreated.Year = tmpCreated.Year AND tmpContactsCreated.Month = tmpCreated.Month) OR
                           (tmpContactsCreated.Year = tmpActivity.Year AND tmpContactsCreated.Month = tmpActivity.Month)     

But I would have to test this to be sure.


Adding in rollups is hard to do -- typically this is done externally to the SQL in the control or whatever displays the results. You could do something like this (contrived example):

SELECT 1 as reportOrder, date, amount, null as total
FROM invoices
UNION ALL
SELECT 2 , null, null, sum(amount)
FROM invoices
ORDER BY reportOrder, date

or you could not have the "extra" total column and put it in the amount column.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文