如何在 sql 查询中仅从对应于某个特定值存在于另一个表的其他列中的对应表中的那些列

Question

我在数据库中有两个表，即main.vacuum_status和main_vacuum_analog。

logtime is a common field

两个表。在main.vacuum_status有列即和st1_vs1_bag1_onoff.。这样就有八列，最多st1_vs1_bag8_onoff。其中一些列分别具有 0 或 1 作为值。在另一个表中，有一些列st1_vs1_bag1_rb直到st1_vs1_bag8_rb，其中包含一些实际类型值。

Condition

现在我想要一个 sql 查询，它检查每当 st1_vs1_bag1_onoff 列中有 1 个时，就会检查另一个表列的实际类型值st1_vs1_bag1_rb......即如果st1_vs1_bag8_onoff为 0，则不应显示st1_vs1_bag8_rb，如果 st1_vs1_bag8_onoff 为 1，则显示 st1_vs1_bag8_rb sholud 的值。

有没有这样的sql查询？？

EDIT 1

通过查看答案，我将我的sql查询形成为：

select 
  case when a.st1_vs1_bag1_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag1_rb ELSE 'Value when 0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag2_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag2_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag3_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag3_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag4_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag4_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag5_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag5_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag6_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag6_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag7_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag7_rb else '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag8_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag8_rb else '0' END as st1_vs1_bag1_rb ,
from main_vacuum_status a INNER JOIN main_vacuum_analog c ON a.LOGTIME = c.LOGTIME

但是错误正在显示Incorrect syntax near form

Answer 1

根据您提供的信息，您可以执行以下操作：

SELECT 
    CASE WHEN st1_vs1_bag1_onoff = 1 THEN st1_vs1_bag1_rb END AS st1_vs1_bag1_rb
    CASE WHEN st1_vs1_bag2_onoff = 1 THEN st1_vs1_bag2_rb END AS st1_vs1_bag2_rb
    CASE WHEN st1_vs1_bag3_onoff = 1 THEN st1_vs1_bag3_rb END AS st1_vs1_bag3_rb
    CASE WHEN st1_vs1_bag4_onoff = 1 THEN st1_vs1_bag4_rb END AS st1_vs1_bag4_rb
    CASE WHEN st1_vs1_bag5_onoff = 1 THEN st1_vs1_bag5_rb END AS st1_vs1_bag5_rb
    CASE WHEN st1_vs1_bag6_onoff = 1 THEN st1_vs1_bag6_rb END AS st1_vs1_bag6_rb
    CASE WHEN st1_vs1_bag7_onoff = 1 THEN st1_vs1_bag7_rb END AS st1_vs1_bag7_rb
    CASE WHEN st1_vs1_bag8_onoff = 1 THEN st1_vs1_bag8_rb END AS st1_vs1_bag8_rb
FROM main_vacuum_status S
INNER JOIN main_vacuum_analog A
    ON  S.logtime = A.logtime

如果 st1_vs1_bag*_onoff 不同于 1，则 st1_vs1_bag*_rb 将为 NULL。如果要改为显示默认值，请将其放在 ELSE 部分中，如下所示：

CASE WHEN st1_vs1_bag1_onoff = 1 THEN st1_vs1_bag1_rb ELSE 'Value when 0' END AS st1_vs1_bag1_rb

Answer 2

您可以执行以下操作：

SELECT LOGTIME
,
,CASE 
    WHEN st1_vs1_bag1_onoff = 1
        THEN st1_vs1_bag1_rb
    ELSE '<No Value>'
    END AS st1_vs1_bag1_rb
,
   .
   .
   .
   .
   .
   .
,CASE 
    WHEN st1_vs1_bag8_onoff = 1
        THEN st1_vs1_bag8_rb
    ELSE '<No Value>'
    END AS st1_vs1_bag8_rb
FROM main.vacuum_status AL1
INNER JOIN main_vacuum_analog AL2 ON AL1.LOGTIME = AL2.LOGTIME

如果这不是您要找的，请告诉我。

Answer 3

我通过这个查询得到了我想要的，上面的两个答案都很有帮助：））

select c.LOGTIME,
  case when a.st1_vs1_bag1_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag1_rb ELSE '0' END as st1_vs1_bag1_rb ,
  CASE when a.st1_vs1_bag2_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag2_rb else '0' END as st1_vs1_bag2_rb ,
  CASE when a.st1_vs1_bag3_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag3_rb else '0' END as st1_vs1_bag3_rb ,
  CASE when a.st1_vs1_bag4_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag4_rb else '0' END as st1_vs1_bag4_rb ,
  CASE when a.st1_vs1_bag5_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag5_rb else '0' END as st1_vs1_bag5_rb ,
  CASE when a.st1_vs1_bag6_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag6_rb else '0' END as st1_vs1_bag6_rb ,
  CASE when a.st1_vs1_bag7_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag7_rb else '0' END as st1_vs1_bag7_rb ,
  CASE when a.st1_vs1_bag8_onoff='0' and a.logtime=c.logtime then c.st1_vs1_bag8_rb else '0' END as st1_vs1_bag8_rb 
from main_vacuum_status a INNER JOIN main_vacuum_analog c ON a.LOGTIME = c.LOGTIME and
c.logtime between '2014-10-10 07:17:00' and '2014-10-10 08:46:00'