SQL查询，以获取列值中整数的外观数(或在代码中获取整数外观的提示)

Question

标题有点混乱，但我找不到更好的。这是我想做的。我有一张表格：

姓名-性别-答案

Tom =男性. 1,2,3

Kate女性1,4

John x男性2,4

Maggy女士1,3

我有两个问题：

1)如果有一个查询来获取出现在“答案”列中的整数数？

在我们的例子中：

答案计数

1/3

2-2-2

3/2

4/2

2)如果有可能，是否有办法按性别分列：

在我们的例子中：

性别-答案-统计

男-1-1

男-2-2

男-3-1

女性.1.2

女-3-2

我希望我说的很清楚。

我使用C#，并使用OleDB连接从excel工作表中读取数据。

如果无法通过SQL查询执行此操作，则如何使用C#代码进行操作。(我使用Datatable来填充所读取的数据)

非常感谢您的帮助

Answer 1

不应将多个值存储在字段中，应使用单独的表对表进行规范化处理：

Person:
Id  Name   Gender
1   Tom    Male
2   Kate   Female
3   John   Male
4   Maggy  Female

Answer:
Id Answer
1  1
1  2
1  3
2  1
2  4
3  2
3  4
4  1
4  3

现在，您可以使用count轻松地获得答案的数量。

selecct p.Name, count(*) as Cnt
from Person p
inner join Answer a on a.Id = p.Id
group by p.Name

你也可以很容易地按性别分组，并回答：

selecct p.Gender, a.Answer, count(*)
from Person p
inner join Answer a on a.Id = p.Id
group by p.Gender, a.Answer

Answer 2

(我将尝试在MSSql中回答这个问题)

您可以使用sql创建一个拆分函数，如下面所示的示例：http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

注意，您可以修改第一个示例(这是GroupOn代码，它将是您的“男性”或“女性”元素)：

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5),
          @GroupOn nvarchar(100)
)  

RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100),
          GroupOn nvarchar(100)
) 
AS  
BEGIN 

Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data, @GroupOn)
Select Data = ltrim(rtrim(@RowData))

Return
END

然后你就可以相应地数数和分组。

编辑修复了上面的拼写错误。

Answer 3

这个查询是针对问题2的，可以对其进行小修改，以匹配问题1的结果。

    declare @t table
    (
        name varchar(50),
        gender varchar(50),
        answers varchar(50)
    )

    insert into @t
    select 'Tom', 'Male', '1,2,3'
    union
    select 'Kate', 'Female', '1,4'
    union
    select 'John', 'Male', '2,4'
    union
    select 'Maggy', 'Female', '1,3'

    select 
        gender, answer, count = count(*)
    from
    (
        select
            gender, 
            -- here t2.c is an xml column, which holds values of this sort <a>1</a>
            -- 'data(.)' gets the value from the <a> tag
            answer = cast(t2.c.query('data(.)') as varchar)
        from
        (
            select 
                name, gender, 
                -- represent answers in the xml, proper for xquery node function
                -- for example <root><a>1</a><a>2</a></root>
                answers = cast('<root><a>' + replace(answers, ',', '</a><a>') + '</a></root>' as xml)
            from @t
        ) t1
        -- xquery nodes function reads the xml and
        -- in this case for each tag <a> it returns a separate row
        cross apply answers.nodes('/root/a') t2(c)
    ) t
    group by gender, answer
    order by gender desc, answer

注意:如果我正确理解您的问题，结果将与您在第2部分中提供的结果不匹配，因为您的结果与示例数据不相对应。