T-SQL:选择具有子节点的前20个根节点

Question

我的情况：我在SQL Server 2012数据库中有一个表

id | created             | sum
------------------------------
1  | 2013-12-10 12:00:00 | 200 
2  | 2013-12-10 13:00:00 | 300 
3  | 2013-12-10 14:00:00 | 400 
4  | 2013-12-09 08:00:00 | 100 
5  | 2013-12-09 15:00:00 | 600 
6  | 2013-12-10 12:00:00 | 50 
...
50 | 2013-11-23 14:00:00 | 400 
51 | 2013-11-22 08:00:00 | 100 
52 | 2013-11-22 15:00:00 | 600 
53 | 2013-11-20 12:00:00 | 50 

如何为20个不同日期选择行而不考虑时间？

选择操作的预期结果：

1 | 2013-12-10
1  | 2013-12-10 12:00:00 | 200 
2  | 2013-12-10 13:00:00 | 300 
3  | 2013-12-10 14:00:00 | 400 
2 | 2013-12-09
4  | 2013-12-09 08:00:00 | 100 
5  | 2013-12-09 15:00:00 | 600 
...
20| 2013-11-22
51 | 2013-11-22 08:00:00 | 100 
52 | 2013-11-22 15:00:00 | 600 

Answer 1

你可以试试这样的东西：

• 使用CTE（Common Table Expression）从表中提取20个仅限日期的值
• 将基表与CTE输出连接起来，以从基表中获取仅针对那些选定日期的所有行

试试这样的东西：

-- replace this with your own, base table - this is just for demo purposes
DECLARE @table TABLE (ID INT, Created DATETIME2(0), ValueSum INT)
INSERT INTO @table VALUES(1, '2013-12-10 12:00:00', 200), 
(2, '2013-12-10 13:00:00', 300 ), 
(3, '2013-12-10 14:00:00', 400),  
(4, '2013-12-09 08:00:00', 100 ), 
(5, '2013-12-09 15:00:00', 600),  
(6, '2013-12-10 12:00:00', 50),  
(50, '2013-11-23 14:00:00', 400 ), 
(51, '2013-11-22 08:00:00', 100 ), 
(52, '2013-11-22 15:00:00', 600 ), 
(53, '2013-11-20 12:00:00', 50 )
-- define a CTE thta selects TOP (n) distinct date-only values from your base table    
;WITH RandomDates AS
(
    SELECT DISTINCT TOP (3)
        DateOnly = CAST(Created AS DATE)
    FROM @table
)
SELECT * FROM RandomDates

这将列出您选择的仅日期值

如果你将这些值与你的基表连接起来，你可能会得到你想要的输出。。。

;WITH RandomDates AS
(
    SELECT DISTINCT TOP (20)
        DateOnly = CAST(Created AS DATE)
    FROM dbo.YourBaseTable
)
SELECT t.* 
FROM RandomDates rd
INNER JOIN dbo.YourBaseTable t ON CAST(t.Created AS DATE) = rd.DateOnly

Answer 2

试试这个

速度较慢

select top 20 distinct CONVERT(varchar(25), Created, 110)
from    tbl
where   CONVERT(varchar(25), Created, 110) between [Datfrom] and [DateTo]

更快的

select top 10 distinct CONVERT(varchar(25), Created, 110)
from    tbl
where   Created between '1/1/2013' + ' 00:00:00' and '1/31/2013' + ' 23:59:59'

Answer 3

试试这个：

SELECT rowNo, createdDate, sumCol
FROM ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY CONVERT(DATE, a.created)) rowNo, CONVERT(DATE, a.created) createdDate, '' sumCol
       FROM tableA a
       GROUP BY CONVERT(DATE, a.created)
       UNION 
       SELECT B.id AS rowNo, b.created AS createdDate, b.sum AS sumCol
       FROM (SELECT TOP 20 CONVERT(DATE, a.created) createdDate FROM tableA a ORDER BY CONVERT(DATE, a.created)) A
       INNER JOIN tableA B ON A.createdDate = CONVERT(DATE, b.created)
      ) AS A
ORDER BY createdDate

检查SQL FIDDLE演示

输出

| ROWNO |         CREATEDDATE | SUMCOL |
|-------|---------------------|--------|
|     1 | 2013-11-20 00:00:00 |      0 |
|    53 | 2013-11-20 12:00:00 |     50 |
|     2 | 2013-11-22 00:00:00 |      0 |
|    51 | 2013-11-22 08:00:00 |    100 |
|    52 | 2013-11-22 15:00:00 |    600 |
|     3 | 2013-11-23 00:00:00 |      0 |
|    50 | 2013-11-23 14:00:00 |    400 |
|     4 | 2013-12-09 00:00:00 |      0 |
|     4 | 2013-12-09 08:00:00 |    100 |
|     5 | 2013-12-09 15:00:00 |    600 |
|     5 | 2013-12-10 00:00:00 |      0 |
|     1 | 2013-12-10 12:00:00 |    200 |
|     6 | 2013-12-10 12:00:00 |     50 |
|     2 | 2013-12-10 13:00:00 |    300 |
|     3 | 2013-12-10 14:00:00 |    400 |