如果行存在多天则返回结果的 SPROC T-SQL 语法

发布于 2024-08-19 23:03:25 字数 109 浏览 10 评论 0原文

我需要在表上测试的是,在两个不同的日子里是否有给定用户 ID 和订单 ID 的行(时间戳的 DATETIME 字段)。

我很确定我需要一个拥有条款,这就是我在这里的原因……这让我非常害怕。

what I need to test for on my table is if there are rows for a given user id and order id on two separate days (DATETIME field for a timestamp).

I'm pretty sure I'd need a having clause and that's why I'm here...that frightens me terribly.

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

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

发布评论

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

评论(2

自找没趣 2024-08-26 23:03:25

拥有不应该吓到您,它只是聚合字段上的“位置”:

Select UserID, Count(*) From OrderTbl Group By UserID Having Count(*) > 1

这将为您提供拥有多个订单的所有用户。

Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID Having Count(*) > 1 

如果 @UserID 中的用户 id 有多个记录,将为您提供计数;如果没有,则为 null。

if exists (Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID 
             Having Count(*) > 1) Select 1 else Select 0

如果用户有多个记录,则返回 1,否则返回 0。

更新:没有意识到您每天可以有多个订单。此查询将执行您想要的操作:

With DistinctDates as (Select Distinct UserID, [DATE] From OrderTbl Where (UserID=@UserID))
Select UserID, Count(*) From DistinctDates  
Group By UserID Having Count(*) > 1 

Having shouldn't scare you, it is just a "Where" on an aggregated field:

Select UserID, Count(*) From OrderTbl Group By UserID Having Count(*) > 1

That'll give you all the Users that have multiple orders.

Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID Having Count(*) > 1 

will give you the count if there are multiple records for the user id in @UserID and null if not.

if exists (Select UserID, Count(*) From OrderTbl Where (UserID=@UserID) Group By UserID 
             Having Count(*) > 1) Select 1 else Select 0

will return a 1 if there are multiple records for the User, 0 if not.

Update: Didn't realize that you could have multiple orders per day. This query will do what you want:

With DistinctDates as (Select Distinct UserID, [DATE] From OrderTbl Where (UserID=@UserID))
Select UserID, Count(*) From DistinctDates  
Group By UserID Having Count(*) > 1 
生生漫 2024-08-26 23:03:25

我不确定我是否理解你的问题,但这可能对你有用。 HAVING 是您的朋友,您仍然可以使用 WHERE 子句。这应该让您知道表中多次出现的顺序和用户 ID 组合。

SELECT [UserId], [OrderId]
FROM OrderTable 
WHERE UserId = @UserId
AND OrderId = @OrderId
GROUP BY UserId, OrderId
HAVING Count(*) > 1

I am not sure if I understood your question, but this may work for you. The HAVING is your friend and you can still use the WHERE clause. This should let you know what order and user id combo is occuring more than once in the table.

SELECT [UserId], [OrderId]
FROM OrderTable 
WHERE UserId = @UserId
AND OrderId = @OrderId
GROUP BY UserId, OrderId
HAVING Count(*) > 1
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文