SQL连接结合条件
问题描述:
请问如何从这些数据中获得如下结果?SQL连接结合条件
(我想合并的条件,但仍然有一些不必要的行加入。)
输入是在只有一个表:
ID IN OUT
-----------------
1 6:00 null
2 7:11 null
2 null 16:30
3 null 19:00
输出(视图)应结合相同ID,这样的结果是这样的:
ID IN OUT
------------------
1 6:00 null
2 7:11 16:30
3 null 19:00
答
你可以尝试用一个CTE来实现这一目标如下:
CREATE TABLE #data
(
Id int not null,
inTime varchar(10) null,
outTime varchar(10) null
)
INSERT INTO #data VALUES
(1, '6:00', null),
(2 , '7:11', null),
(2 , null, '16:30'),
(3 , null, '19:00')
WITH combinedTable AS (
SELECT
Id,
MAX(inTime) AS A,
MAX(outTime) AS B
FROM #data
GROUP BY Id
HAVING MAX(2) = MIN(2)
AND MAX(3) = MIN(3)
)
SELECT *
FROM combinedTable
UNION ALL
SELECT *
FROM #data
WHERE ID NOT IN (SELECT ID FROM combinedTable)
答
以下可达到目的: 1.有一个表ID和在IN不等于空值:
(select ID, IN from table where In ne NULL()) as tableA
O/P将是:
ID IN
1 6:00
2 7:11
2 。具有ID和OUT的表,其中OUT不等于空:
(select ID, OUT from table where OUT ne NULL()) as tableB
O/P将是:
ID OUT
2 16:30
3 19:00
3.Outer加入两个表:
select tableA.ID,tableA.IN, tableB.OUT
from table as tableA
outer full join
select ID,OUT from table as tableB
On tableA.ID=tableB.ID and tableA.IN ne Null() and tableB.OUT ne NULL()
输出将所需的输出:
ID IN OUT
1 6:00 null
2 7:11 16:30
3 null 19:00
可以有超过2相同的ID? – gh9
谢谢,解决方案由两个步骤组成: 1.标准化数据 - 将它们分成三个不同的视图(仅IN,仅限OUT和组合IN和OUT) 2.联合所有这3个视图分为4h一个 –