1 个表上有 2 个字段的 In 子句

发布于 2024-12-19 23:32:37 字数 321 浏览 2 评论 0原文

我有一个查询：

 SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE logId IN ('1','2','3')

但我希望 IN 子句也像这样测试 computerId ：

logId = '1' and computerId = '998874',

logId = '2' and计算机 ID = '334211', ...

如何做到这一点？

原文

I have a query :

 SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE logId IN ('1','2','3')

But I want the IN clause test the computerId too like this :

logId = '1' and computerId = '998874',

logId = '2' and computerId = '334211',
...

How to do this ?

分享到QQ

分享到微博

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

发布评论

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

深海里的那抹蓝 2024-12-26 23:32:37

您必须将 IN 语句替换为多个 OR 语句。

 SELECT  MAX(date)
        ,  logId
        , computerId
 FROM   logTable
 WHERE  (logId = '1' AND computerid = '998874')
        OR (logId = '2' AND computerid = '334211')

或者，如果您有很多条件，为它们创建一个内存表并连接可能会更具可读性在这些条件下

;WITH Conditions (logID, computerid) AS (
  SELECT '1', '998874'
  UNION ALL SELECT '2', '334211'
)
SELECT  MAX(date)
        ,  logId
        , computerId
FROM   logTable lt
       INNER JOIN Conditions c ON c.logID = lt.LogID 
                                  AND c.computerid = lt.computerID

You would have to replace the INstatement with multiple ORstatements

 SELECT  MAX(date)
        ,  logId
        , computerId
 FROM   logTable
 WHERE  (logId = '1' AND computerid = '998874')
        OR (logId = '2' AND computerid = '334211')

Alternatively, if you have many conditions, it might be more readable to create an in-memory table for them and join on these conditions

;WITH Conditions (logID, computerid) AS (
  SELECT '1', '998874'
  UNION ALL SELECT '2', '334211'
)
SELECT  MAX(date)
        ,  logId
        , computerId
FROM   logTable lt
       INNER JOIN Conditions c ON c.logID = lt.LogID 
                                  AND c.computerid = lt.computerID

回复收藏 0 原文

北风几吹夏 2024-12-26 23:32:37

我认为您不能使用 IN 子句来做到这一点。我认为你需要一个有条件的where。所以：

SELECT MAX(date), logId, computerId
FROM logTable
WHERE (logId = 1 AND computerId = '998874') OR
      (logId = 2 AND computerId = '334211') OR
      ...

I don't think you can do that with an IN clause. I think you need a conditional where. So:

SELECT MAX(date), logId, computerId
FROM logTable
WHERE (logId = 1 AND computerId = '998874') OR
      (logId = 2 AND computerId = '334211') OR
      ...

回复收藏 0 原文

没企图 2024-12-26 23:32:37

SQL Server 中的IN 不支持元组。您可以使用 EXISTS 和表表达式。 2008 语法如下。

SELECT MAX(l.date),
       l.logId,
       l.computerId
FROM   logTable l
WHERE  EXISTS(SELECT *
              FROM   (VALUES ( '1', '998874'),
                              ('2', '334211')) v(logId, computerId)
              WHERE  v.computerId = l.computerId
                     AND v.logId = l.logId)  
GROUP  BY l.logId,
          l.computerId

IN in SQL Server does not support tuples. You can use EXISTS and a table expression. 2008 syntax below.

SELECT MAX(l.date),
       l.logId,
       l.computerId
FROM   logTable l
WHERE  EXISTS(SELECT *
              FROM   (VALUES ( '1', '998874'),
                              ('2', '334211')) v(logId, computerId)
              WHERE  v.computerId = l.computerId
                     AND v.logId = l.logId)  
GROUP  BY l.logId,
          l.computerId

回复收藏 0 原文

懷念過去 2024-12-26 23:32:37

像这样：

SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE (logId = '1' AND computerId = '998874')
    OR (logId = '2' AND computerId = '334211')
    OR (logId = '3' AND computerId = '123456')
 GROUP BY logId, computerId

Like this:

SELECT 
   MAX(date), 
   logId,
   computerId
 FROM 
   logTable
 WHERE (logId = '1' AND computerId = '998874')
    OR (logId = '2' AND computerId = '334211')
    OR (logId = '3' AND computerId = '123456')
 GROUP BY logId, computerId

回复收藏 0 原文

~没有更多了~