将查询从一个表格插入到另一个表格 [英] Insert Query From one tabel to another

问题描述

我有一个数据库，其列的值包含0到5的示例，如下所示:

I have a Database with a Column with value containing 0 to 5 example as shown below :

IndeX     Name     Status
  1       Test       0
  1       Test       3
  1       Test       1
  1       Test       0
  1       Test       2
  1       Test       1
  1       Test       5
  1       Test       0

我想计算状态中每个数字的数量，然后将我想要的输出插入到另一个表中，如下所示

I Want to count the number of each number inside Status and insert into another table the output i want is shown below

Index   Zero   One    Two    Three    Four    Five
 1        3     2      1      1        0       1

什么是SQL语句来实现这一目标?

[edit]已添加代码块，忽略HTML ..."选项已禁用，较小的拼写和标点符号-OriginalGriff [/edit]

What is the SQL statement to achieve this ?

[edit]Code block added, "Ignore HTML..." option disabled, minor spelling and punctuation - OriginalGriff[/edit]

推荐答案

您可以使用数据透视运算符来这个.请查看以下教程:

http://msdn.microsoft.com/en-us/library/ms177410.aspx [ ^ ]

You can use pivot operator to this. Please check the following tutorial:

http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]

您可以通过以下子查询获得所需的输出

You can get required output with some sub-query like below

select
SC.MyIndex AS Index1,
(SELECT count(sc1.status) as z FROM statuscount SC1 WHERE sc1.status = 0 group by sc1.myindex,sc1.status ) AS ZERO,
(SELECT count(sc1.status) as o FROM statuscount SC1 WHERE sc1.status = 1 group by sc1.myindex,sc1.status ) AS One,
(SELECT count(sc1.status) as t FROM statuscount SC1 WHERE sc1.status = 2 group by sc1.myindex,sc1.status ) AS TWO,
(SELECT count(sc1.status) as th FROM statuscount SC1 WHERE sc1.status = 3 group by sc1.myindex,sc1.status ) AS Three,
(SELECT count(sc1.status) as fo FROM statuscount SC1 WHERE sc1.status = 4 group by sc1.myindex,sc1.status ) AS Four,
(SELECT count(sc1.status) as fi FROM statuscount SC1 WHERE sc1.status = 5 group by sc1.myindex,sc1.status ) AS Five
FROM  statuscount SC group by sc.myindex

另一种形式不同的解决方案是

Another solution in different form is

select
sc.MyIndex,sc.status,count(sc.status) AS TotalCount from statuscount SC group by sc.myindex,sc.status

希望对您有所帮助.

Hope it helps.

这篇关于将查询从一个表格插入到另一个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！