代码之家 › 专栏 › 技术社区 › Ahmad

列上带有where子句的SELECT语句

inner-join datatables tsql sql-server sql

Ahmad · 技术社区 · 5 年前

在我进行内部联接的表中,有一个名为“Data”的列和另一个名为“Type”的列,这取决于在将此表与另一个表进行内部联接时需要将它们分开的数据类型。

下面是我要运行的脚本:

SELECT 
C.StoreName, 'Time' = (SELECT CST.Data from tbStoreScheduleData CST
INNER JOIN tbPrograms P2 on CST.StoreScheduleDataID = P2.StoreScheduleDayID
 where CST.Type = 'T'  ) from tbPrograms P 

INNER JOIN tbStore C on P.StoreID= C.StoreID
INNER JOIN tbStoreScheduleData CS on P.StoreScheduleDayID = CS.StoreScheduleDataID

但当我运行这个程序时,我得到了所有的时间。如何在语句中的列上放置where子句?只获取时间值的“T”类型?

在表Store Schedule Data中,每个存储都有输入值,一个是时间,另一个是日期,根据类型将其分开并拉取。如果你想看看一周中的几天,你可以选择D型,如果你想时间,你可以选择T型。但是,在一个内部连接中拉这个是我一直坚持的。

编辑:这是StoreScheduleDataID中的数据

+---------------------+------+--------------------+-----------+----------+
| StoreScheduleDataID | Type |        Data        | SortOrder | WeekDays |
+---------------------+------+--------------------+-----------+----------+
|                   1 | D    | Monday to Thursday |         1 | 2345     |
|                   2 | D    | Monday to Friday   |         2 | 23456    |
|                   3 | D    | Tuesday to Friday  |         3 | 3456     |
|                   4 | D    | Tuesdays           |        11 | 3        |
|                   5 | T    | 8:00AM to 2:15PM   |        90 | NULL     |
|                   6 | T    | 8:00AM to 1:00PM   |        83 | NULL     |
|                   7 | T    | 8:30AM to 1:30PM   |       108 | NULL     |
+---------------------+------+--------------------+-----------+----------+

期望输出:

+------------------------------------+------------------+
| StoreName           Date           |       TIME       |
+------------------------------------+------------------+
| Store1          Monday to Friday   | 8:00AM to 2:15PM |
| Store2          Monday to Thursday | 8:00AM to 2:15PM |
| Store3          Monday to Friday   | 8:00AM to 2:15PM |
+------------------------------------+------------------+

我得到的第一个答案是:

+------------------------------------+------+
| StoreName           Date           | TIME |
+------------------------------------+------+
| Store1          Monday to Friday   | NULL |
| Store2          Monday to Thursday | NULL |
| Store3          Monday to Friday   | NULL |
+------------------------------------+------+

1 回复 | 直到 5 年前

gotqn user3521065 5 年前

可能是这样:

SELECT C.StoreName
      ,MAX(CASE WHEN CST.Type = 'T' THEN  CST.Data  END) AS [Time]
      ,MAX(CASE WHEN CST.Type = 'D' THEN  CST.Data  END) AS [Date]
from tbPrograms P 
INNER JOIN tbStore C 
    on P.StoreID= C.StoreID
INNER JOIN tbStoreScheduleData CS 
    on P.StoreScheduleDayID = CS.StoreScheduleDataID
GROUP BY C.StoreName