SQL中的交叉表查询，用于比较和添加列 [英] Crosstab query in SQL that compares and adds columns

问题描述

我在sql server中有一个表，该表包含三列：日期，中午和下午3点。第一列是不言自明的，但后两列包含根据演讲嘉宾到达时间的不同，他们在场馆的名字。我想编写一个交叉表查询，将发言者姓名写到列标题中，并计算发言者在该日期讲话的次数。

I have a table in sql server that contains three columns: "date", "noon", and "3pm." The first column is self-explanatory, but the latter two contain the names of guest speakers at a venue according to the time they arrived. I want to write a cross-tab query that writes speaker names into the column header and counts the number of times that speaker spoke on that date.

示例

Date   | Noon   |  3pm
092916 | Tom    | <null>
092816 | Dick   |  Tom 
092716 | <null> |  Suzy 

所需的输出

Date   | Dick   |  Tom   |  Suzy
092916 | <null> |   1    | <null> 
092816 |  1     |   1    | <null>
092716 | <null> | <null> |   1

如果我只选择一次并计数，我可以使用交叉表查询轻松完成此操作进入价值类别，但是我很难合并多次，这样我就可以准确计算出谁在哪一天发言。

I can do this pretty easily with a crosstab query if I only select one time and put a count into the value category, but I'm having trouble with merging multiple times so that I can get an accurate count of who spoke on what day.

推荐答案

您可以使用以下查询：

select *
from   (
        select   date, noon as speaker, count(*) as times
        from     events
        group by date, noon
        union all 
        select   date, [3pm], count(*)
        from     events
        group by date, [3pm]
       ) as u
pivot  (
        sum(times)
        for speaker in ([Dick], [Tom], [Suzy])
       ) as piv
order by date desc;

...这将为您提供每个单元格的计数（空，1或2）：

... which gives you a count per cell (null, 1 or 2):

Date   | Dick   |  Tom   |  Suzy
092916 | <null> |   1    | <null> 
092816 |   1    |   1    | <null>
092716 | <null> | <null> |   1

这篇关于SQL中的交叉表查询，用于比较和添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！