在语句时使用增量列的mySQL

发布于 2025-02-09 18:22:39 字数 2163 浏览 3 评论 0原文

我正在尝试在从表中选择数据到auto_increment the Row Count时创建列，而我发现的最佳解决方案是将row_number（）跨越（按日期desc）作为行_，因为它是与订单条款一致，并已证明在我的情况下有效。

但是，当在语句时在情况下创建stats时，我无法使用新列行_。

例如，我有以下语句：

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_
      ,CASE WHEN row_ = 1 then 8 else 0 end as stat1
      ,CASE WHEN row_ = 2 then 2 else 0 end as stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

但是，我会发现一个错误，说：field List list中的未知列'row_'。

我真的在寻找可以有一个表格的东西（行_是由row_number（）...部分计算的）：

row_	col1	col2
1	blah blah	blah blah
2	blah	blah blah
3	等等	，

情况

row_	可以	在	下	使用

原文

I am trying to create a column when selecting data from a table to auto_increment the row count and the best solution I have found is to use ROW_NUMBER() OVER ( order by Date DESC) AS row_ because it is consistent with the order clause and has proven to work in my situation.

However I can't use the new column row_ when creating stats in a case when statement.

For example, I have the following statement:

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_
      ,CASE WHEN row_ = 1 then 8 else 0 end as stat1
      ,CASE WHEN row_ = 2 then 2 else 0 end as stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

However I get an error saying: Unknown column 'row_' in field list.

Im really looking for something where I can have a table (row_ being calculated by the ROW_NUMBER()... section):

row_	col1	col2
1	blah	blah
2	blah	blah
3	blah	blah

and be able to use row_ in a case when situation so create another column to look like:

row_	col1	col2	stat1	stat2
1	blah	blah	8	0
2	blah	blah	0	2
3	blah	blah	0	0

分享到QQ

分享到微博

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

发布评论

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

感受沵的脚步 2025-02-16 18:22:39

您可以直接使用row_number（）函数case子句如下：

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 1 THEN 8 ELSE 0 END AS stat1,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 2 THEN 2 ELSE 0 END AS stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

demo

You can directly use the ROW_NUMBER() function within you CASE clause Like below:

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 1 THEN 8 ELSE 0 END AS stat1,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 2 THEN 2 ELSE 0 END AS stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

DEMO

回复收藏 0 原文

~没有更多了~