需要SQL查询以解决以下问题 [英] Need SQL Query for this following problem

问题描述

我有两张桌子

日id天
  1 星期一
  2 星期二
  3 星期三
  4 星期四
  5 星期五
  6 星期六
  7 星期日

  slot   id   slot  
  1   9:00    -    12:00  
  2   10:00    -    13:00  
  3   11:00    -    14:00  
  4   12:00    -    15:00  
  5   13:00    -    16:00  
  6   14:00    -    17:00  

i需要编写查询以获得以下结果

 星期一 < span class =code-leadattribute> 9:00    -    12:00  
 星期一  10:00    -    13:00  
 星期一  11:00    -    14:00  
 星期一  12:00    -    15:00  
 星期一  13:00    -    16:00  
  Monday   14:00    -    17:00  
 星期二  9:00    -    12:00  
 星期二  10:00    -    13:00  
  Tuesday   11:00    -    14:00  
 星期二  12:00    -    15:00  
 星期二  13:00    -    16:00  
  Tuesday   14:00    -    17:00  
 星期三  9:00    -   < span class =code-leadattribute> 12:00  
  Wednesday   10:00    -    13:00  
  Wednesday   11:00    -    14:00  
  Wednesday   12:00    -    15:00  
  Wednesday   13 ：00    -    16:00  
  Wednesday   14:00    -    17:00  
 星期四  9:00    -    12:00  
  Thursday   10:00    -    13:00  
  Thursday   11:00    -    14:00  
 星期四  12:00    -   15:00  
 星期四  13:00    -    16:00  
  Thursday   14:00    -    17:00  

看起来虽然简单，但我无法理解工作

解决方案

尝试以下查询

  SELECT  day，slot 从 tableDay，tableSlot 订单 按天

jibesh的解决方案很好，但我更喜欢使用 JOINs [ ^ ]。 通过使用联接，您可以根据逻辑从两个或多个表中检索数据表之间的关系。联接指示Microsoft SQL Server应如何使用一个表中的数据来选择另一个表中的行。 [ ^ ]

  SELECT  d.day，s.slot 
  FROM  tableDay  AS  d INNR  JOIN  tableSlot  AS  s  ON  d。[day id] = s。[slot id] 
  ORDER   BY  d。[day id] 

为什么要使用联接？ [ ^ ]

早期版本的Micr osoft®SQLServer™，使用* =和= *运算符在WHERE子句中指定了左外连接条件和右外连接条件。在某些情况下，此语法会导致可能以多种方式解释的模糊查询。符合SQL-92的外连接在FROM子句中指定，不会导致这种歧义。由于SQL-92语法更精确，因此本版本不包含有关在WHERE子句中使用旧的Transact-SQL外连接语法的详细信息。 SQL Server的未来版本可能不支持该语法。使用Transact-SQL外连接的任何语句都应更改为使用SQL-92语法。

I have two tables

day id  day
1   Monday
2   Tuesday
3   Wednesday
4   Thursday
5   Friday
6   Saturday
7   Sunday

slot id slot
1   9:00 - 12:00
2   10:00 - 13:00
3   11:00 - 14:00
4   12:00 - 15:00
5   13:00 - 16:00
6   14:00 - 17:00

i need to write a query to get the following result

Monday  9:00 - 12:00
Monday      10:00 - 13:00
Monday      11:00 - 14:00
Monday      12:00 - 15:00
Monday      13:00 - 16:00
Monday      14:00 - 17:00
Tuesday      9:00 - 12:00
Tuesday     10:00 - 13:00
Tuesday     11:00 - 14:00
Tuesday     12:00 - 15:00
Tuesday     13:00 - 16:00
Tuesday     14:00 - 17:00
Wednesday   9:00 - 12:00
Wednesday   10:00 - 13:00
Wednesday   11:00 - 14:00
Wednesday   12:00 - 15:00
Wednesday   13:00 - 16:00
Wednesday   14:00 - 17:00
Thursday    9:00 - 12:00
Thursday    10:00 - 13:00
Thursday    11:00 - 14:00
Thursday    12:00 - 15:00
Thursday    13:00 - 16:00
Thursday    14:00 - 17:00

Seems simple though, but i just cant get my mind working

解决方案

try the following query

SELECT day, slot from tableDay,tableSlot order by day

Solutioin of jibesh is good, but i prefer to use JOINs[^]. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table.[^]

SELECT d.day, s.slot
FROM tableDay AS d INNR JOIN tableSlot AS s ON d.[day id] = s.[slot id]
ORDER BY d.[day id]

Why to use joins?[^]

In earlier versions of Microsoft® SQL Server™ , left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

这篇关于需要SQL查询以解决以下问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！