database - 每月从多个表中获取记录

Question

我有这样的场景:我有很多表，每个表都有日期字段。

Table: FA

Id  | Created_Date  |
------------------------
1   | 1/12/2012 |
2   | 2/15/2012 |
3   | 2/25/2012 |

Table: TPA

Id  | Created_Date  |
------------------------
1   | 3/10/2012 |
2   | 4/25/2012 |
3   | 5/20/2012 |
4   | 5/21/2012 |

Table: Gift

Id  | Created_Date  |
------------------------
1   | 6/10/2012 |
2   | 7/25/2012 |
3   | 8/10/2012 |
3   | 7/11/2012 |

我想要输出，就像我想要每个表中的记录总数一样，并使用 Created_Date 字段日期分别显示它。

预期输出如下:

Answer 1

试试这个解决方案，

SELECT  MonthName, 
        COALESCE(FA, 0) FA, 
        COALESCE(TPA, 0) TPA, 
        COALESCE(GIFT, 0) GIFT
FROM
        (
            SELECT  monthList.ordby,
                    monthList.MonthName,
                    org.TableName,
                    org.TotalCount
            FROM    
                    (
                        SELECT 1 ordby, 'January' MonthName UNION SELECT 2, 'February' UNION
                        SELECT 3, 'March' UNION SELECT 4, 'April' UNION
                        SELECT 5,'May' UNION SELECT 6,'June' UNION
                        SELECT 7,'July' UNION SELECT 8,'August' UNION 
                        SELECT 9,'September' UNION SELECT 10,'October' UNION
                        SELECT 11,'November' UNION SELECT 12,'December'
                    ) monthList
                    LEFT JOIN
                    (
                        SELECT  'FA' TableName, 
                                DATENAME(mm,Created_Date) MonthName, 
                                COUNT(*) TotalCount
                        FROM    FA
                        GROUP   BY DATENAME(mm,Created_Date)
                        UNION
                        SELECT  'TPA' TableName, 
                                DATENAME(mm,Created_Date) MonthName, 
                                COUNT(*) TotalCount
                        FROM    TPA
                        GROUP   BY DATENAME(mm,Created_Date)
                        UNION
                        SELECT  'Gift' TableName, 
                                DATENAME(mm,Created_Date) MonthName, 
                                COUNT(*) TotalCount
                        FROM    Gift
                        GROUP   BY DATENAME(mm,Created_Date)
                    ) org ON monthList.MonthName = org.MonthName
        ) data
        PIVOT
        (
            MAX(TotalCount)
            FOR TableName IN ([FA], [TPA],[GIFT])
        ) head
ORDER   BY ordby

http://www.sqlfiddle.com/#!3/dc613/14