在 SQL 2008 中向表中添加值
尝试对 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
用途:
这是一个枢轴查询,将行数据转换为列数据。
不是菜鸟/初学者需要处理的问题。 SQL Server 2005+ 添加了(现在为 ANSI)PIVOT/UNPIVOT 语法,但这对于大多数数据库都是可移植的(因为目前很少有数据库支持 PIVOT/UNPIVOT)。
Use:
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).
您需要为结果集中所需的每一列选择一列。在 SQL 中,您仅选择三列。尝试:
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: