如何为每行添加具有相同值的列？

发布于 2024-11-26 21:10:35 字数 477 浏览 2 评论 0原文

我被要求对我们的数据库进行搜索并解释 5 个不同年龄段的客户数量。我知道我可以运行相同的查询五次。然而，我希望能够创建一个可以完成整个工作的查询。

据我所知，我需要执行 5 个查询，然后使用“UNION”连接不同的查询。

问题是，对于每个查询，我想添加一个显示“年龄范围”的列，然后输入一些文本来命名该组。

我想出的 SQL 是：

SELECT     COUNT(CLIENT_ID) AS Client_Count, "0 to 19" AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

虽然我希望这能起作用，但它期望 0 到 19 是一列。我如何让它知道我只是想传递一个固定值？

一旦我让它发挥作用，他们就会希望我的值占总数的百分比。任何帮助表示赞赏。

原文

I was asked to do a search against our database and to explain the number of clients in the 5 different age ranges. I know I can run the same query five times. However, i was hoping to be able to create one query that would do the whole job.

To my knowledge, I need to do 5 queries and then use "UNION" to connect the different queries.

The issue is that for each query i want to add a column that says "age range" and then put some text to name the group.

The SQL I came up with was:

SELECT     COUNT(CLIENT_ID) AS Client_Count, "0 to 19" AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

While I hoped this would work, it expects the 0 to 19 to be a column. How would I let it know that I'm just trying to pass it a fixed value?

Once I get this to work, they would then like that I the values as a percentage of the total. Any help is appreciated.

分享到QQ

分享到微博

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

发布评论

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

木槿暧夏七纪年 2024-12-03 21:10:36

更改您的语音标记。例如：

SELECT '0 to 19' AS [Age Group]

完整：

SELECT     COUNT(CLIENT_ID) AS Client_Count, '0 to 19' AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

Change your speach marks. E.g:

SELECT '0 to 19' AS [Age Group]

In Full:

SELECT     COUNT(CLIENT_ID) AS Client_Count, '0 to 19' AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

回复收藏 0 原文

苍暮颜 2024-12-03 21:10:36

不确定我是否正确，但如果您想要列中的结果，您可以执行以下操作：

SELECT
  [0 to 19]  = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN  0 AND 19 THEN 1 END),
  [20 to 39] = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN 20 AND 39 THEN 1 END),
  …
FROM dbo.Clients

Not sure if I got it right, but if you want the results in columns, you can do something like this:

SELECT
  [0 to 19]  = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN  0 AND 19 THEN 1 END),
  [20 to 39] = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN 20 AND 39 THEN 1 END),
  …
FROM dbo.Clients

回复收藏 0 原文

你不是我要的菜∠ 2024-12-03 21:10:35

这应该有效。尝试单引号。

SQL 使用单引号来表示字符串文本。看起来它可以解释双引号，但你已经有了。

回复收藏 0 原文

缺⑴份安定 2024-12-03 21:10:35

我认为以下内容应该为您提供一个良好的起点：

;with AgeRanges(Low, High) as (
    select 0, 19
    union select 20, 39
    union select 40, 59
    union select 60, 79
    union select 80, 1000
)

select
    count(CLIENT_ID) as Client_Count,
    convert(varchar, Low) + ' to ' + convert(varchar, High) as [Age Group]
from
    dbo.CLIENTS
    inner join AgeRanges
        on  DATEDIFF(year, DOB, getdate()) BETWEEN Low and High

The following should give you a good starting point I think:

;with AgeRanges(Low, High) as (
    select 0, 19
    union select 20, 39
    union select 40, 59
    union select 60, 79
    union select 80, 1000
)

select
    count(CLIENT_ID) as Client_Count,
    convert(varchar, Low) + ' to ' + convert(varchar, High) as [Age Group]
from
    dbo.CLIENTS
    inner join AgeRanges
        on  DATEDIFF(year, DOB, getdate()) BETWEEN Low and High

回复收藏 0 原文

~没有更多了~