时间戳

首页 > 解决方案 > 尝试在 SQL 中合并两个表（相同的列、数据类型和 where 子句）

sql - 尝试在 SQL 中合并两个表（相同的列、数据类型和 where 子句）

问题描述

嗨，我一直在尝试使用简单的联合或使用 CAT 然后联合来组合这两个查询，但它不起作用：

我的查询如下：{

    ;WITH tb1 as

(
                        select 
                                 Venue
                                ,convert(date,event, 103) as EventDate
                                ,RoomName
                                ,SPECIALTY
                                CONSULTANT_NAME
from test.dbo.test_tbl1
WHERE convert(date,event, 103) >= '2019-01-01' and convert(date,event, 103) <= '2019-12-01')
select * from tb1
where
                                 SPECIALTY like 'Business%'
                                or (CONSULTANT_NAME like '%john, Jx%') 
                                or (CONSULTANT_NAME like '%Sam, Sx%')



;WITH tb2 as

(
                        select 
                                 Venue
                                ,convert(date,event, 103) as EventDate
                                ,RoomName
                                ,SPECIALTY
                                CONSULTANT_NAME
from test.dbo.test_tbl2
WHERE convert(date,event, 103) >= '2019-01-01' and convert(date,event, 103) <= '2019-12-01')
select * from tb2
where
                                 SPECIALTY like 'Business%'
                                or (CONSULTANT_NAME like '%john, Jx%') 
                                or (CONSULTANT_NAME like '%Sam, Sx%')
}

at the end of first query if I try to put tab2 as: 

,tb2 as

( select ....)

它的显示错误

任何指示正确方向的帮助表示赞赏。

标签： sqltsql

解决方案

你为什么不使用这样的东西：

SELECT 
     Venue
    ,CONVERT(date,event, 103) AS EventDate
    ,RoomName
    ,SPECIALTY
    ,CONSULTANT_NAME
FROM test.dbo.test_tbl1
WHERE CONVERT(date,event, 103) >= '2019-01-01' AND CONVERT(date,event, 103) <= '2019-12-01') AND (SPECIALTY LIKE 'Business%' OR CONSULTANT_NAME LIKE '%john, Jx%' OR CONSULTANT_NAME LIKE '%Sam, Sx%')

UNION 

SELECT 
     Venue
    ,CONVERT(date,event, 103) AS EventDate
    ,RoomName
    ,SPECIALTY
    ,CONSULTANT_NAME
FROM test.dbo.test_tbl2
WHERE CONVERT(date,event, 103) >= '2019-01-01' AND CONVERT(date,event, 103) <= '2019-12-01') AND (SPECIALTY LIKE 'Business%' OR CONSULTANT_NAME LIKE '%john, Jx%' OR CONSULTANT_NAME LIKE '%Sam, Sx%')

推荐阅读