如何根据条件值创建mysql查询结果？

I want this kind of an out put

我想要这种出局

ID      Status
100     Viewed
103     Not Viewed
105     Viewed

This is my sql:

这是我的sql:

select id, status from status_table where ID in (100, 101,102,103,104,105);

It will show the above result because in status table other ids don't have any entry. ID is foreign key of another table named as table_file table. It contains in another database. So I cannot join the table due to some performance issue. So I am passing file ID as comma separated values. But I want this kind of result How can I create this with out using any loops.

它将显示上述结果,因为在状态表中,其他ID没有任何条目。 ID是另一个名为table_file表的表的外键。它包含在另一个数据库中。由于某些性能问题,我无法加入该表。所以我将文件ID作为逗号分隔值传递。但是我想要这种结果如何在不使用任何循环的情况下创建它。

ID    Status
100   Viewed
101   Not
102   Not
103   Viewed
104   Not
105   Viewed

Is it possible? Please help me.

可能吗?请帮帮我。

2 个解决方案

#1

Do you have a table where those IDs DO exist? So that you can join on it?

你有一张表存在这些ID吗?那么你可以加入吗?

SELECT
  source.ID,
  status.value
FROM
  source
LEFT JOIN
  status
    ON status.id = source.id
WHERE
  source.ID in (100, 101,102,103,104,105);

If not, you need to create a temporary table or inline table (with those values in it). Then you can just join that table to your data.

如果没有,则需要创建临时表或内联表(其中包含这些值)。然后,您可以将该表加入您的数据。

EDIT

Example of an inline table. There are several ways to do this, this is just one.

内联表的示例。有几种方法可以做到这一点,这只是一种方法。

SELECT
  source.ID,
  status.value
FROM
  (
    SELECT 100 AS id UNION ALL
    SELECT 101 AS id UNION ALL
    SELECT 102 AS id UNION ALL
    SELECT 103 AS id UNION ALL
    SELECT 104 AS id UNION ALL
    SELECT 105 AS id
  )
  AS source
LEFT JOIN
  status
    ON status.id = source.id

#2

Assuming 'Not' when the ID is missing, you can do it like this:

当ID丢失时假设'不',你可以这样做:

SELECT
  so.ID,
  CASE WHEN st.value IS NULL THEN 'Not' ELSE st.value END
FROM
  databasename1..source so
LEFT JOIN
  databasename2..status st
    ON st.id = so.id
WHERE
  so.ID in (100, 101,102,103,104,105)

Substitute databasename1 and databasename2 to the real database names.

将databasename1和databasename2替换为实际数据库名称。

#1