在 SQL 2008 中向表中添加值

发布于 2024-09-29 10:04:52 字数 838 浏览 3 评论 0原文

尝试对 SQL Server 2008 中的 T-SQL 有一个基本的了解。假设我有一个名为“Issues”的表,其列如下:

Priority User
1        Foo
1        Foo
2        Foo
5        Foo
4        Bar
5        Bar
1        Bar
1        Fuz

并且我希望显示每个用户的优先级计数,以及每个用户的详细信息优先级,使得结果表可能被命名为“Breakdown”,可能看起来

User Total 1 2 3 4 5
Foo  4     2 1 0 0 1
Bar  3     1 0 0 1 1
Fuz  1     1 0 0 0 0

我在想我可以声明变量并编写我的查询,

DECLARE @P1 INT
DECLARE @P2 INT
DECLARE @P3 INT
DECLARE @P4 INT
DECLARE @P5 INT

SELECT COUNT(id) AS Total,UserName, 
CASE Priority
 WHEN 1 Then @P1 = @P1 + 1
 WHEN 2 Then @P2 = @P2 + 1
 WHEN 3 Then @P3 = @P3 + 1
 WHEN 4 Then @P4 = @P4 + 1
 WHEN 5 Then @P5 = @P5 + 1
END,
FROM Breakdown
GROUP BY UserName

但我很确定我走错了路。有人有什么建议吗?

谢谢,并对这个新手问题表示歉意;但我不确定到底要在这里谷歌搜索什么...-

R。

Trying to get a basic understanding of T-SQL here in SQL Server 2008. Suppose I have a table named "Issues" with columns such as:

Priority User
1        Foo
1        Foo
2        Foo
5        Foo
4        Bar
5        Bar
1        Bar
1        Fuz

and I wish to display a count of the Priority for each User, along with a breakdown of each Priority, such that the resulting table might be named "Breakdown" might look like

User Total 1 2 3 4 5
Foo  4     2 1 0 0 1
Bar  3     1 0 0 1 1
Fuz  1     1 0 0 0 0

I was thinking I might declare variables and write my query something like

DECLARE @P1 INT
DECLARE @P2 INT
DECLARE @P3 INT
DECLARE @P4 INT
DECLARE @P5 INT

SELECT COUNT(id) AS Total,UserName, 
CASE Priority
 WHEN 1 Then @P1 = @P1 + 1
 WHEN 2 Then @P2 = @P2 + 1
 WHEN 3 Then @P3 = @P3 + 1
 WHEN 4 Then @P4 = @P4 + 1
 WHEN 5 Then @P5 = @P5 + 1
END,
FROM Breakdown
GROUP BY UserName

but I'm pretty sure I'm on the wrong track. Does anyone have any suggestions?

Thanks, and sorry for the noobish question; but I'm not sure exactly what to google for here...

-R.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

无人接听 2024-10-06 10:04:52

用途:

  SELECT i.user, 
         COUNT(i.priority) AS total,
         SUM(CASE WHEN i.priority = 1 THEN 1 ELSE 0 END) AS 1,
         SUM(CASE WHEN i.priority = 2 THEN 1 ELSE 0 END) AS 2,
         SUM(CASE WHEN i.priority = 3 THEN 1 ELSE 0 END) AS 3,
         SUM(CASE WHEN i.priority = 4 THEN 1 ELSE 0 END) AS 4,
         SUM(CASE WHEN i.priority = 5 THEN 1 ELSE 0 END) AS 5
    FROM ISSUES i
GROUP BY i.user

这是一个枢轴查询,将行数据转换为列数据。
不是菜鸟/初学者需要处理的问题。 SQL Server 2005+ 添加了(现在为 ANSI)PIVOT/UNPIVOT 语法,但这对于大多数数据库都是可移植的(因为目前很少有数据库支持 PIVOT/UNPIVOT)。

Use:

  SELECT i.user, 
         COUNT(i.priority) AS total,
         SUM(CASE WHEN i.priority = 1 THEN 1 ELSE 0 END) AS 1,
         SUM(CASE WHEN i.priority = 2 THEN 1 ELSE 0 END) AS 2,
         SUM(CASE WHEN i.priority = 3 THEN 1 ELSE 0 END) AS 3,
         SUM(CASE WHEN i.priority = 4 THEN 1 ELSE 0 END) AS 4,
         SUM(CASE WHEN i.priority = 5 THEN 1 ELSE 0 END) AS 5
    FROM ISSUES i
GROUP BY i.user

It's a pivot query, converting row data into columnar data.
Not a noob/beginner issue to deal with. SQL Server 2005+ added the (now ANSI) PIVOT/UNPIVOT syntax, but this is portable to most databases (because few currently support PIVOT/UNPIVOT).

我喜欢麦丽素 2024-10-06 10:04:52

您需要为结果集中所需的每一列选择一列。在 SQL 中,您仅选择三列。尝试:

SELECT UserName,
       Count(*) AS Total, 
       SUM(CASE Priority WHEN 1 THEN 1 ELSE 0 END) AS P1_Total,
       SUM(CASE Priority WHEN 2 THEN 1 ELSE 0 END) AS P2_Total,
       SUM(CASE Priority WHEN 3 THEN 1 ELSE 0 END) AS P3_Total,
       SUM(CASE Priority WHEN 4 THEN 1 ELSE 0 END) AS P4_Total,
      SUM(CASE Priority WHEN 5 THEN 1 ELSE 0 END) AS P5_Total
FROM Issues 
GROUP BY UserName

You need to SELECT one column for each column you want in your result set. In your SQL, you're only selecting three columns. Try:

SELECT UserName,
       Count(*) AS Total, 
       SUM(CASE Priority WHEN 1 THEN 1 ELSE 0 END) AS P1_Total,
       SUM(CASE Priority WHEN 2 THEN 1 ELSE 0 END) AS P2_Total,
       SUM(CASE Priority WHEN 3 THEN 1 ELSE 0 END) AS P3_Total,
       SUM(CASE Priority WHEN 4 THEN 1 ELSE 0 END) AS P4_Total,
      SUM(CASE Priority WHEN 5 THEN 1 ELSE 0 END) AS P5_Total
FROM Issues 
GROUP BY UserName
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文