如何从带有计算字段的 SQL 查询中获取总计

Question

我有一张表格，列出了诊所的就诊情况。我想要一个“直方图”，显示患者访问诊所的频率以及总数。下面是一些示例代码（在 MS SQL Server 2005 下测试）来展示我正在讨论的内容：

CREATE TABLE #test (
  visit_id int IDENTITY(1,1),
  patient_id int
);

DECLARE @num_patients int;
SELECT @num_patients = 1000 + ABS(CHECKSUM(NEWID())) % 250;

INSERT INTO #test (patient_id)
SELECT TOP 15 PERCENT ABS(CHECKSUM(NEWID())) % @num_patients
FROM sysobjects a, sysobjects b;

-- SELECT COUNT(*) AS total_visits FROM #test;

-- SELECT COUNT(DISTINCT patient_id) AS distinct_patients FROM #test;

SELECT CASE GROUPING(num_pat_visits) WHEN 1 THEN 'Total'
            ELSE CAST(num_pat_visits AS varchar(5)) END AS num_pat_visits,
  COUNT(*) AS num_patients, num_pat_visits * COUNT(*) AS tot_pat_visit
FROM
  (SELECT patient_id, COUNT(*) AS num_pat_visits FROM #test GROUP BY patient_id) a
GROUP BY num_pat_visits WITH ROLLUP
ORDER BY CAST(num_pat_visits AS int) DESC;

这几乎让我到达了我想要的位置：

num_pat_visits num_patients tot_pat_visit 
-------------- ------------ ------------- 
60                        1            60 
54                        2           108 
52                        2           104 
51                        4           204 
50                        3           150 
49                        3           147 
48                        7           336 
47                        7           329 
46                       15           690 
45                       15           675 
44                       29          1276 
43                       36          1548 
42                       45          1890 
41                       45          1845 
40                       59          2360 
39                       71          2769 
38                       51          1938 
37                       72          2664 
36                       77          2772 
35                       74          2590 
34                       72          2448 
33                       82          2706 
32                       90          2880 
31                       74          2294 
30                       69          2070 
29                       47          1363 
28                       30           840 
27                       27           729 
26                       26           676 
25                       21           525 
24                       13           312 
23                        4            92 
22                        5           110 
21                        4            84 
20                        2            40 
18                        2            36 
Total                  1186          NULL

但是，我似乎无法让 SQL Server 显示它所在位置的访问总数总行上显示 NULL。

有什么想法吗？

Answer 1

我认为你可以这样做：

sum(num_pat_visits) as tot_pat_visit

SELECT CASE GROUPING(num_pat_visits) WHEN 1 THEN 'Total'
            ELSE CAST(num_pat_visits AS varchar(5)) END AS num_pat_visits,
  COUNT(*) AS num_patients, 
  --num_pat_visits * COUNT(*) AS tot_pat_visit
  sum(num_pat_visits) as tot_pat_visit
FROM
  (SELECT patient_id, COUNT(*) AS num_pat_visits FROM #test GROUP BY patient_id) a
GROUP BY num_pat_visits WITH ROLLUP
ORDER BY CAST(num_pat_visits AS int) DESC;