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

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

subtotal sorting tsql

Justin808 · 技术社区 · 15 年前

我有一些T-SQL可以生成一个很好的报告,按月给出一些总结。

我有两个问题,有没有一种方法可以让月份按日历顺序排序,而不是按字母排序?而且,我想做的是为每年添加一个总行,为整个报告添加一个总行?

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

1 回复 | 直到 12 年前

Hogan 15 年前

要按月订购,请使用以下内容:

  ORDER BY DATEPART(Month,Created) ASC

DatePart() 为指定的部分返回一个整数:1表示1月,2表示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

或者您不能将“额外的”汇总列放在“金额”列中。